Strange issue since updating to 7.3

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
damon.cognito
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Strange issue since updating to 7.3

Post by damon.cognito » Tue 17 Feb 2015 16:39

Since updating to 7.3.342 (from 6.2.122) a query have been causing a weird exception: "syntax error at or near \"SELECT\". I've narrowed it down to one part of the select which I have extracted below:

Code: Select all

var query = from card in entity.Time
   where Time.Head.OIDHead == oidHead
   select new
   {
     Ref = (Time.OIDBill > 0) ? entity.Bill.FirstOrDefault(o => o.OIDBill == Time.OIDBill).Reference : ""
   };
[/size]

Stack trace
]" at Devart.Data.PostgreSql.ae.f(ac A_0)\r\n at Devart.Data.PostgreSql.ac.n()\r\n at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)\r\n at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)\r\n at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n at Devart.Data.PostgreSql.Entity.t.a(CommandBehavior A_0)\r\n at Devart.Common.Entity.ab.b(CommandBehavior A_0)\r\n at Devart.Data.PostgreSql.Entity.t.b(CommandBehavior A_0)\r\n at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)\r\n at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)\r\n at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)\r\n at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"[/code]

It's a bit of a strange construct granted, but it used to work. We are using PostgreSQL 9.3.5 and VS 2013.

Regards, Damon.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: Strange issue since updating to 7.3

Post by Shalex » Wed 18 Feb 2015 14:16

Please give us the following information:
1. Send us a small test project with the corresponding DDL/DML script for reproducing.
2. Enable the dbMonitor tool (download link, documentation) and specify the exact SQL statement which fails to execute.
3. Which EntityFramework version (x.x.) are you using?

damon.cognito
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Re: Strange issue since updating to 7.3

Post by damon.cognito » Wed 18 Feb 2015 17:20

Entity Framework is 6.1.2

I will try to get a sample project together when I have the time. I've highlighted the section in red below if errors on with

syntax error at or near "SELECT"

SELECT
1 AS "C1",
CASE WHEN "Project1"."OIDBill" > 0 THEN "Limit2"."Reference" ELSE '' END AS "C2"
FROM (SELECT
"Filter1"."OIDBill"
FROM (SELECT
"Extent1"."OIDTime"
"Extent1"."OIDBill"
"Extent1"."Description"
FROM public."Time" AS "Extent1"
WHERE "Extent1"."OIDHead" = 1 ) AS "Filter1"
LEFT JOIN LATERAL (SELECT
"Extent2"."OIDBill",
"Extent2"."Reference"
FROM public."Bill" AS "Extent2"
WHERE "Extent2"."OIDBill" = "Filter1"."OIDBill"
LIMIT 1 ) AS "Limit1" ON true ) AS "Project1"
LEFT JOIN LATERAL (SELECT
"Extent3"."OIDBill",
"Extent3"."Reference"
FROM public."Bill" AS "Extent3"
WHERE "Extent3"."OIDBill" = "Project1"."OIDBill"
LIMIT 1 ) AS "Limit2" ON true

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: Strange issue since updating to 7.3

Post by Shalex » Thu 19 Feb 2015 11:21

Thank you for the additional information. We are looking forward to your test project.

damon.cognito
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Re: Strange issue since updating to 7.3

Post by damon.cognito » Thu 12 Mar 2015 21:03

All sent; pretty sure it is producing incorrect SQL statements from the LINQ.

Regards, Damon.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: Strange issue since updating to 7.3

Post by Shalex » Fri 13 Mar 2015 13:36

Please upgrade to EntityFramework v6.1.3:
Install-Package EntityFramework -Version 6.1.3

This should solve the problem. We have verified the fix your test project. The corresponding discussion at our forum: http://forums.devart.com/viewtopic.php?f=1&t=31133.

damon.cognito
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Re: Strange issue since updating to 7.3

Post by damon.cognito » Fri 13 Mar 2015 17:37

Thanks. It has solved some problems but not other. Still getting the issue as reported in my first post with 'syntax error at or near "SELECT"' on other queries. DM monitor shows the errored query but when you copy it out of DB Monitor it runs fine!

I will try to get you another sample project but it is a huge project and it is very time consuming, so I want to make sure you don't already know about it (like the 6.1.2./6.1.3 one).

Reagrds, Damon.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: Strange issue since updating to 7.3

Post by Shalex » Mon 16 Mar 2015 16:34

We cannot identify the reason of the problem at the moment. Please send us a small test project with the corresponding DDL/DML script for reproducing.

Post Reply