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

[Simon Slavin]

On 30 Mar 2016, at 6:35pm, Tim Stowell  wrote:

> SELECT TOP (20) [t0].[field1], [t0].[ field1], [t0].[field2] FROM [tablename] 
> AS [t0]
> 
> Instead of the "TOP" keyword it should be "LIMIT"

Hmm.  I'd just like to point out that the syntax diagram for SQLite says that 
the LIMIT clause must appear at the end of the command (apart from the optional 
OFFSET clause which may appear after it).  So the fix is not just to replace 
TOP with LIMIT, but something more than that.  I suspect that the 'Linq' 
editors need to take a close look at their source code

The confusion comes from the fact that SQL92 didn't include any way to limit 
the number of results so different engines implemented it in different ways.  I 
suspect SQLite got 'LIMIT' from PostgreSQL.  Other engines use 'TOP' or 'FIRST' 
or 'ROWS' or 'ROWCOUNT' in different places in the command.  It's a mess.

Simon.

[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

[Joe Mistachkin]

Tim Stowell wrote:
>
> var dbConnection = new SQLiteConnection(
> "Data Source=db;provider=System.Data.SQLite.Linq;");
> var context = new DataContext(dbConnection);
> 

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.

--
Joe Mistachkin

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

[Joe Mistachkin]

Tim Stowell wrote:
>
> 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.
> 

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

--
Joe Mistachkin

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

[Joe Mistachkin]

Tim Stowell wrote:
>
> SELECT TOP (20) [t0].[field1], [t0].[ field1], [t0].[field2] FROM
> [tablename] AS [t0]
> 
> Instead of the "TOP" keyword it should be "LIMIT"
> 

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?

--
Joe Mistachkin

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

[Joe Mistachkin]

Tim Stowell wrote:
>
> var queryable = table.Skip(20);  // Incorrect SQL
generated
>

Are you using the latest System.Data.SQLite?

Do you have an example of the generated SQL?

--
Joe Mistachkin

[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.