实体框架在不同的工作站上生成不同的查询 [英] Entity Framework Generates different queries on different workstations

问题描述

我们在单个Developer Machine和几个客户端上有问题。
单个Linq查询生成两个不同的SQL查询。事实上，第二个查询具有firebird不支持的OUTER APPLY语句。
我们认为它不是代码问题，但环境问题，但我会粘贴代码。

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

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")

工作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")

为MSSQL Engine生成的查询（我们在我们的APP中支持两个db引擎）

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)    

推荐答案

Net Framework 4.5和firebirdsql.data.firebirdclient.dll之间的问题 - 当sb安装了这个版本的框架时 - EntityFramework为firebird生成外部应用语句。

The problem is between Net Framework 4.5 and firebirdsql.data.firebirdclient.dll - When sb have installed this version of framework - EntityFramework generates outer apply statements for firebird.

可能Jiri Cincura（cincura.net）应该调查它：）

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

问题解决通过将.net框架降级为4.0完整

Problem Solved by downgrading .net framework to 4.0 full

这篇关于实体框架在不同的工作站上生成不同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！