Entity Framework Creates various requests on different workstations

Question

We have a problem on one developer's machine and several clients. Single Linq Query Creates two different SQL queries. The problem is that the second request has an “OUTER APPLY” statement that firebird does not support. We think that this is not a code problem, but a problem with the environment, but I will embed the code.

linq:

AIds = (from x in context.RISK_T_ASSESS_HIST where (x.ID_RISKOBJECT.HasValue && x.F_CREATEDON >= Freq.StartDate && x.F_CREATEDON <= Freq.EndDate) group x by x.ID_RISKOBJECT into gr let lastCreated = gr.Max(p => p.F_CREATEDON) select new { ObjId = gr.Key , LastStatus = gr.Where(p => p.F_CREATEDON == lastCreated && p.ID_RISKOBJECT == gr.Key).Select(p => p.F_STATUS).FirstOrDefault() }).Where(x => x.LastStatus == 0 || x.LastStatus == 1).Select(x => x.ObjId.Value).ToArray(); 

SQL with OUTER APPLY

 SELECT "G"."ID_RISKOBJECT" AS "ID_RISKOBJECT" FROM (SELECT "C"."A1" AS "C1", "C"."K1" AS "ID_RISKOBJECT" FROM ( SELECT "E"."ID_RISKOBJECT" AS "K1", MAX("E"."F_CREATEDON") AS "A1" FROM "RISK_T_ASSESS_HIST" AS "E" WHERE (("E"."ID_RISKOBJECT" IS NOT NULL) AND ("E"."F_CREATEDON" >= @p__linq__0)) AND ("E"."F_CREATEDON" <= @p__linq__1) GROUP BY "E"."ID_RISKOBJECT" ) AS "C" ) AS "G" OUTER APPLY (SELECT FIRST (1) "I"."F_STATUS" AS "F_STATUS" FROM "RISK_T_ASSESS_HIST" AS "I" WHERE (((("I"."ID_RISKOBJECT" IS NOT NULL) AND ("I"."F_CREATEDON" >= @p__linq__0)) AND ("I"."F_CREATEDON" <= @p__linq__1)) AND (("G"."ID_RISKOBJECT" = "I"."ID_RISKOBJECT") OR (("G"."ID_RISKOBJECT" IS NULL) AND ("I"."ID_RISKOBJECT" IS NULL)))) AND (("I"."F_CREATEDON" = "G"."C1") AND ("I"."ID_RISKOBJECT" = "G"."ID_RISKOBJECT")) ) AS "J" WHERE (0 = "J"."F_STATUS") OR (1 = "J"."F_STATUS") 

Working SQL

 SELECT "B"."ID_RISKOBJECT" AS "ID_RISKOBJECT" FROM ( SELECT "C"."ID_RISKOBJECT" AS "ID_RISKOBJECT", (SELECT FIRST (1) "I"."F_STATUS" AS "F_STATUS" FROM "RISK_T_ASSESS_HIST" AS "I" WHERE (((("I"."ID_RISKOBJECT" IS NOT NULL) AND ("I"."F_CREATEDON" >= @p__linq__0)) AND ("I"."F_CREATEDON" <= @p__linq__1)) AND (("C"."ID_RISKOBJECT" = "I"."ID_RISKOBJECT") OR (("C"."ID_RISKOBJECT" IS NULL) AND ("I"."ID_RISKOBJECT" IS NULL)))) AND (("I"."F_CREATEDON" = "C"."C1") AND ("I"."ID_RISKOBJECT" = "C"."ID_RISKOBJECT"))) AS "C1" FROM ( SELECT "D"."A1" AS "C1", "D"."K1" AS "ID_RISKOBJECT" FROM ( SELECT "F"."ID_RISKOBJECT" AS "K1", MAX("F"."F_CREATEDON") AS "A1" FROM "RISK_T_ASSESS_HIST" AS "F" WHERE (("F"."ID_RISKOBJECT" IS NOT NULL) AND ("F"."F_CREATEDON" >= @p__linq__0)) AND ("F"."F_CREATEDON" <= @p__linq__1) GROUP BY "F"."ID_RISKOBJECT" ) AS "D" ) AS "C" ) AS "B" WHERE (0 = "B"."C1") OR (1 = "B"."C1") 

The query generated for the MSSQL Engine (we support two db modules in our APP)

 SELECT [Project1].[ID_RISKOBJECT] AS [ID_RISKOBJECT] FROM (SELECT [GroupBy1].[A1] AS [C1], [GroupBy1].[K1] AS [ID_RISKOBJECT] FROM ( SELECT [Extent1].[ID_RISKOBJECT] AS [K1], MAX([Extent1].[F_CREATEDON]) AS [A1] FROM [dbo].[RISK_T_ASSESS_HIST] AS [Extent1] WHERE ([Extent1].[ID_RISKOBJECT] IS NOT NULL) AND ([Extent1].[F_CREATEDON] >= @p__linq__0) AND ([Extent1].[F_CREATEDON] <= @p__linq__1) GROUP BY [Extent1].[ID_RISKOBJECT] ) AS [GroupBy1] ) AS [Project1] OUTER APPLY (SELECT TOP (1) [Extent2].[F_STATUS] AS [F_STATUS] FROM [dbo].[RISK_T_ASSESS_HIST] AS [Extent2] WHERE ([Extent2].[ID_RISKOBJECT] IS NOT NULL) AND ([Extent2].[F_CREATEDON] >= @p__linq__0) AND ([Extent2].[F_CREATEDON] <= @p__linq__1) AND (([Project1].[ID_RISKOBJECT] = [Extent2].[ID_RISKOBJECT]) OR (([Project1].[ID_RISKOBJECT] IS NULL) AND ([Extent2]. [ID_RISKOBJECT] IS NULL))) AND ([Extent2].[F_CREATEDON] = [Project1].[C1]) AND ([Extent2].[ID_RISKOBJECT] = [Project1].[ID_RISKOBJECT]) ) AS [Limit1] WHERE [Limit1].[F_STATUS] IN (0,1) 

Answer 1

Do you work with SQL Server?

If you use .edmx, then the ProviderManifestToken attribute (usually in the first ten lines of XML) will indicate the supported set of SQL functions. This is determined from the database used when creating or updating the model from the database. For instance. a common problem works with the local SQL 2008 database, and then goes to the 2005 database and it turns out that the application crashes because it does not support datetime2 in SQL 2005. The fix in this case is to change the automatically generated value from 2008 to 2005

I am not familiar with working with firebird, but I suggest looking in this area.

Answer 2

The problem is that between Net Framework 4.5 and firebirdsql.data.firebirdclient.dll - When sb installed this version of the framework - EntityFramework generates external application applications for firebird.

Probably Jiri Cincura (cincura.net) should research it :)

Problem Solved by downgrading the .net framework to version 4.0 full