SQLite compiled query

[mepolsen]

I'm trying to use the CompiledQuery class to improve query performance on a SQLIte db but running into errors. My compiled query throws a cast error of:

Unable to cast object of type 'Devart.Data.Linq.Provider.ProviderType' to type 'Devart.Data.SQLite.Linq.Provider.SQLiteProviderType'.

Here is the syntax of my query:

public static Func>
EnabledTiles = CompiledQuery.Compile((CalibrationDataContext db, bool enabled) =>
from tile in db.Tiles where tile.Enabled == enabled select tile);

public static IEnumerable GetEnabledTiles(bool enabled)
{
CalibrationDataContext db = Program.DataContext;
return CompiledQueries.EnabledTiles(db, enabled);
}

with the following stack trace provided:
at Devart.Data.SQLite.Linq.Provider.Query.a.b(ProviderType A_0, ProviderType A_1)
at Devart.Data.Linq.Provider.Query.DbMethodCallConverter.TranslateToServerMethod(List`1 serverMethods, List`1 largeProviderTypes, List`1 arguments, Type returnType)
at Devart.Data.Linq.Provider.Query.DbMethodCallConverter.VisitBinaryOperator(ab bo)
at Devart.Data.Linq.Provider.Query.SqlVisitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.Query.SqlVisitor.VisitExpression(SqlExpression exp)
at Devart.Data.Linq.Provider.Query.SqlVisitor.VisitSelectCore(SqlSelect select)
at Devart.Data.Linq.Provider.Query.DbMethodCallConverter.VisitSelect(SqlSelect select)
at Devart.Data.Linq.Provider.Query.SqlVisitor.VisitAlias(d a)
at Devart.Data.Linq.Provider.Query.DbMethodCallConverter.VisitAlias(d a)
at Devart.Data.Linq.Provider.Query.SqlVisitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.DataProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, IList`1 externalParameterAccessors)
at Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression query)
at Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Compile(Expression query)
at Devart.Data.Linq.CompiledQuery.a(IProvider A_0)
at Devart.Data.Linq.CompiledQuery.a(DataContext A_0, Object[] A_1)
at Devart.Data.Linq.CompiledQuery.Invoke[a,b,c](a A_0, b A_1)

I read through the LINQ documentation and did not find any mention of having to provide a cast. Am I missing a ProfiverAttribute declaration somewhere in my query class?

[StanislavK]

I will send you a test project in a letter, please check that it was not blocked by your mail filter. Please specify what should be changed in the project to reproduce the problem, or send us your sample so that we are able to investigate the issue in details.

Also, please specify the exact version of LinqConnect (or dotConnect for SQLite) you are using. You can check it in the Tools -> LinqConnect -> 'About LinqConnect' (or Tools -> SQLite -> 'About dotConnect for SQLite') item of the Visual Studio main menu.

[mepolsen]

Stanislav,

Your test project compiles without error. The only difference I can see is in the lqml file where the Provider is defined. Here is mine:



Compared to the one you sent:



Could this be causing the problem? I'm using 2.90.180.0

[mepolsen]

It seems the problem is in the query syntax. If I change the bool type to an int and then convert the int to bool using Convert, it works.

public static Func>
EnabledTiles = CompiledQuery.Compile((CalibrationDataContext db, bool enabled) =>
from tile in db.Tiles where tile.Enabled == Convert.ToBoolean(enabled) select tile);

Any way around the extra conversions?

[StanislavK]

We have reproduced the problem; we will investigate it and inform you about the results.

[StanislavK]

We have released LinqConnect 2 (included into the dotConnect for SQLite 3 release) which contains the fix for this issue. The new version of LinqConnect can be downloaded from
http://www.devart.com/linqconnect/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in LinqConnect 2, please refer to
http://www.devart.com/forums/viewtopic.php?t=19628