[sqlite] calling Skip() on IQueryable with LINQ is generating incorrect sql

[Tim Stowell]

> The SQLiteConnection class has no "provider" connection string property.
> Also, the DataContext class appears to be part of the LINQ-to-MSSQL
> feature.
>
> You probably want to use the ObjectContext class.

Thanks for the help. The ObjectContext class appears to be part of Entity 
Framework, I was hoping there was a way to just use the SQLite linq 
functionality without needing Entity Framework. If not I guess I can work with 
it
Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of NAVEX Global? Inc. and the intended recipients and may contain 
confidential and privileged information. Any unauthorized review, use, 
disclosure, or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all copies of 
the original message.

[sqlite] calling Skip() on IQueryable with LINQ is generating incorrect sql

[Tim Stowell]

> Maybe it's somehow using LINQ-to-MSSQL instead?  What does the C# code
> look like that is actually performing the LINQ query?

That would make sense. I'm actually using the dynamic linq library. First I get 
a context then I perform select and take operations on it



var dbConnection = new SQLiteConnection("Data 
Source=db;provider=System.Data.SQLite.Linq;");
var context = new DataContext(dbConnection);

var table = context.GetTable();// table is now an instance of 
IQueryable
table.Select("new (it.columnName)").Take(20);


Maybe I'm getting the context incorrectly?





Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of NAVEX Global? Inc. and the intended recipients and may contain 
confidential and privileged information. Any unauthorized review, use, 
disclosure, or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all copies of 
the original message.

[sqlite] calling Skip() on IQueryable with LINQ is generating incorrect sql

[Tim Stowell]

> As far as I can tell, this cannot currently be generated by the
> System.Data.SQLite.Linq (or EF6) assembly.  In the past, I believe there was
> an issue where it would not emit the LIMIT clause; however, that was fixed
> long ago.
>
> Perhaps the project is picking up an outdated version of the managed
> assemblies for System.Data.SQLite somewhere?

Thanks for the response, the strange thing is the System.Data.SQLite.Linq dll 
doesn't seem to be getting used at all. If I remove the dll file from my bin 
output folder there are no errors when I refresh the site. If I remove 
System.Data.SQLite.dll then I get an error.

Is there any way to force that dll to be used?
Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of NAVEX Global? Inc. and the intended recipients and may contain 
confidential and privileged information. Any unauthorized review, use, 
disclosure, or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all copies of 
the original message.

[sqlite] calling Skip() on IQueryable with LINQ is generating incorrect sql

[Tim Stowell]

 > Are you using the latest System.Data.SQLite?
Yes it's the newest package from Nuget, version 1.0.99

> Do you have an example of the generated SQL?
It basically looks like this:

SELECT TOP (20) [t0].[field1], [t0].[ field1], [t0].[field2] FROM [tablename] 
AS [t0]

Instead of the "TOP" keyword it should be "LIMIT"

Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of NAVEX Global? Inc. and the intended recipients and may contain 
confidential and privileged information. Any unauthorized review, use, 
disclosure, or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all copies of 
the original message.

[sqlite] calling Skip() on IQueryable with LINQ is generating incorrect sql

[Tim Stowell]

I?m using SQLite with .NET and LINQ. When I call Skip on an IQueryable, the 
generated SQL uses ?TOP? instead of ?Limit? which is causing an error due to 
SQLite not supporting the Top keyword. I have referenced both 
System.Data.SQLite, System.Data.SQLite.Linq, and System.Data.Linq in the Visual 
Studio Project. Below is some sample code:


var cachedFilePath = String.Concat(PathInfo.getDataPath() + "reports\\", 
reportGUID, ".", "sqlite");
var dbConnection = new SQLiteConnection("Data Source=somePath; ");
dbConnection.Open();

var context = new DataContext(dbConnection);
var table = context.GetTable();

var queryable = table.Skip(20);  // Incorrect SQL generated


Does anyone have any ideas how to get LINQ to generate the correct ?Limit? 
clause?
Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of NAVEX Global? Inc. and the intended recipients and may contain 
confidential and privileged information. Any unauthorized review, use, 
disclosure, or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all copies of 
the original message.