Strange issue since updating to 7.3

[damon.cognito]

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]

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]

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]

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

[damon.cognito]

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

Regards, Damon.

[Shalex]

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]

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]

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.