Entity Framework Generates different queries on different workstations

Question

We have problem on single Developer Machine and a few clients. Single Linq Query Generates two different SQL queries. The problem is in fact that second query has “OUTER APPLY” statement which firebird do not supports. We think that it isn’t Code issue, but environmental issue, but i will paste 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")

Query generated for MSSQL Engine (we supports two db engines 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

Are you developing against SQL Server?

If you are using .edmx then the ProviderManifestToken attribute (usually in the first 10 lines of the XML) will indicate the supported set of SQL features. This is determined from the database used when you created or updated the model from the database. E.g. a common issue is working against a SQL 2008 local database then pushing to a 2005 database and finding out that the application crashes because it doesn't support datetime2 in SQL 2005. The fix in this instance is to change the auto-created value from 2008 to 2005

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