ERROR: 42601: syntax error at or near "LEFT"

[source....@gmail.com]

Please,

we have trouble after .NET v4 security update from Windows Update. All our LINQ queries start to generate these type of exceptions.

We have not change nothing in our LOGIC but suddenly all stop working. We have found that problem is when we have these type of Queries in our LINQ methods

Dim result = (From i In dbContext.groups _
Where i.id = ID _
Select New With { _
i.id, _
i.name, _
.Associated = (From x In dbContext.group_users _
Where x.groupid = i.id _
Select x.id, x.name)}).SingleOrDefault

Once again everything runs smoothly before windows update (We must have Automatic Update enabled)

Our current setup is
Windows 2003 Web Edition
.NET Runtime version v4.0.30319
NpgSql 2.1.2

[emil.l...@gmail.com]

Hi. Can you print the generated SQL?
If you use DbContext and EF6, you can use
dbContext.Database.Log = Console.Out.WriteLine
to get logging.

If you use an older version of EF, you can (with C# syntax) use
((ObjectQuery)query).ToTraceString()

[Josh Cooley]

Can you post the generated SQL that is producing the error?

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/d6878df5-7c0e-4da5-8e82-ad0e4ad2508f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[source....@gmail.com]

Sorry for late response.

Generated SQL Query is

SELECT "Project1"."C1" AS "C1","Project1"."id" AS "id","Project1"."name" AS "name","Project1"."C2" AS "C2","Project1"."groupid" AS "groupid","Project1"."hostid" AS "hostid","Project1"."addedby" AS "addedby","Project1"."drc" AS "drc","Project1"."active" AS "active" FROM (SELECT "Extent1"."id" AS "id","Extent1"."name" AS "name",1 AS "C1","Extent2"."groupid" AS "groupid","Extent2"."hostid" AS "hostid","Extent2"."addedby" AS "addedby","Extent2"."drc" AS "drc","Extent2"."active" AS "active", CASE WHEN ("Extent2"."groupid" IS NULL ) THEN (CAST (NULL AS int4)) ELSE (1) END AS "C2" FROM "public"."groups" AS "Extent1" WHERE cast(1 as int8)="Extent1"."id" LIMIT 2 LEFT OUTER JOIN "public"."group_hosts" AS "Extent2" ON "Extent2"."groupid"="Extent1"."id") AS "Project1" ORDER BY "Project1"."id" ASC ,"Project1"."C2" ASC

for this LINQ call

Dim result = (From i In dbcontext.groups _
Where i.id = 1 _

Select New With { _
i.id, _
i.name, _

.Associated = (From x In dbcontext.group_hosts _

Where x.groupid = i.id _

Select x)}).SingleOrDefault

On other machines this call runs fine.

[emil.l...@gmail.com]

Hi. Try the pull request at https://github.com/npgsql/Npgsql/pull/256
The issue is that the Limit expression doesn't become a subquery. This pull request should fix that.

[source....@gmail.com]

Sorry,

but no luck. We have downloaded latest version of Npgsql and compile it but we are getting same error again.

[emil.l...@gmail.com]

Hi. The pull request at https://github.com/npgsql/Npgsql/pull/256 has not been merged yet into the official Npgsql. You have to download the version at https://github.com/Emill/Npgsql.

[source....@gmail.com]

NO luck again. I have compiled npgsql version from the link you provide me but still getting this error

"ERROR: 42601: syntax error at or near "LEFT"

Npgsql version now : 2.2.20
.NET 4.0
Postgresql version 9.3.4

[emil.l...@gmail.com]

Hi. Make sure you use the newly compiled version of Npgsql.EntityFramework.dll.
Add it to the GAC using "gacutil /i Npgsql.EntityFramework.dll" and also copy it into your project and set "Copy Local" to True and make sure it is included in your bin folder. If you use the debugger in Visual Studio, you can press Ctrl+D followed by Ctrl+M to show a list of loaded assemblies when you debug your program. Make sure the path to Npgsql.EntityFramework.dll is correct and you don't use an older version of the dll. You might need to adjust your app.config/web.config and machine.config and set the correct version of Npgsql.EntityFramework.

If it still doesn't work, post the generated SQL here.

[source....@gmail.com]

Yes, new DLL are loaded.

SQL query from inner exception is

SELECT "Project1"."C1" AS "C1","Project1"."id" AS "id","Project1"."name" AS "name","Project1"."C2" AS "C2","Project1"."userid" AS "userid" FROM (SELECT "Extent1"."id" AS "id","Extent1"."name" AS "name",1 AS "C1","Extent2"."userid" AS "userid", CASE WHEN ("Extent2"."groupid" IS NULL ) THEN (CAST (NULL AS int4)) ELSE (1) END AS "C2" FROM "public"."gps_groups" AS "Extent1" WHERE cast(1 as int8)="Extent1"."id" LIMIT 2 LEFT OUTER JOIN "public"."gps_group_users" AS "Extent2" ON "Extent2"."groupid"="Extent1"."id") AS "Project1" ORDER BY "Project1"."id" ASC ,"Project1"."C2" ASC

[emil.l...@gmail.com]

Hi. That query is clearly not generated by the new code generator (at https://github.com/Emill/Npgsql). The new code generator separates columns with a comma followed by a space. The old code generator separates columns with only a comma, as seen in your SQL query. So for some reason the correct Npgsql.EntityFramework.dll is not loaded. Try to follow the steps carefully again...

[emil.l...@gmail.com]

This is the query I get when I run your query (although to another table design with different column names) with the new code generator, which is correct:

SELECT "Project1"."ID" AS "ID", "Project1"."Item" AS "Item", "Project1"."C1" AS "C1", "Project1"."ID1" AS "ID1" FROM (SELECT "Extent1"."ID" AS "ID", "Extent1"."Item" AS "Item", "Extent2"."ID" AS "ID1", CASE WHEN ("Extent2"."ID" IS NULL ) THEN (CAST (NULL AS int4)) ELSE (1) END AS "C1" FROM (SELECT "Extent1"."ID" AS "ID", "Extent1"."Item" AS "Item" FROM "dbo"."left" AS "Extent1" WHERE 1="Extent1"."ID" LIMIT 2) AS "Extent1" LEFT OUTER JOIN "dbo"."right" AS "Extent2" ON "Extent2"."middleId"="Extent1"."ID") AS "Project1" ORDER BY "Project1"."ID" ASC ,"Project1"."C1" ASC

The Limit expression is correctly put into a new subquery.

[source....@gmail.com]

Yes, you were right. I didn't set correct version in web.config for bindingRedirect.

Now it works.

Thank you for your support.