OUTER适用于.NET 4.5和实体框架 [英] OUTER APPLY in .net 4.5 and Entity Framework
问题描述
在安装后的的.Net 4.5
,还有由EF生成的查询问题。以 .NET 4.0中同样的查询在LINQ
正常工作。我使用Firebird数据库,它不支持命令 OUTER APPLY
。请问有什么办法可以修改下面的LINQ查询,以便它的行为在的.Net 4.5
?
目前LINQ查询:
INT []天南星科植物=(从x在context.RISK_T_ASSESS_HIST
其中(x.ID_RISKOBJECT.HasValue&安培;&安培; x.F_CREATEDON> = Freq.StartDate
&功放;&安培; x.F_CREATEDON< = Freq.EndDate)
团X由x.ID_RISKOBJECT到GR
让lastCreated = gr.Max(P => p.F_CREATEDON)
选择新
{
OBJID = gr.Key
,LastStatus = gr.Where(P => p.F_CREATEDON == lastCreated
&功放;&安培; p.ID_RISKOBJECT == gr.Key)
。选择(P => p.F_STATUS).FirstOrDefault()
}),其中(X => x.LastStatus == 0 || x.LastStatus == 1)
。选择(X => x.ObjId.Value).ToArray();
SQL生成。净4.5:
SELECT
PROJECT1。ID_RISKOBJECTASID_RISKOBJECT
FROM(SELECT
GroupBy1,A1ASC1,
GroupBy1。K1ASID_RISKOBJECT
FROM(SELECT
Extent1。ID_RISKOBJECTASK1,
MAX(Extent1。F_CREATEDON)作为A1
从RISK_T_ASSESS_HISTASExtent1
WHERE((Extent1。ID_RISKOBJECTIS NOT NULL)
AND(Extent1F_CREATEDON> = 2013年4月17日))
AND(Extent1F_CREATEDON< = 2013年4月17日)
GROUP BYExtent1。ID_RISKOBJECT
)ASGroupBy1)作为PROJECT1
OUTER APPLY(首先选择(1)
Extent2。F_STATUSASF_STATUS
从RISK_T_ASSESS_HISTASExtent2
WHERE((((Extent2。ID_RISKOBJECTIS NOT NULL)
AND(Extent2F_CREATEDON> = 2013年4月17日))
AND(Extent2F_CREATEDON< = @ 2013年4月17号))
与((PROJECT1。ID_RISKOBJECT=Extent2。ID_RISKOBJECT)OR((PROJECT1。ID_RISKOBJECTIS NULL)
AND(Extent2。ID_RISKOBJECTIS NULL))))和((Extent2。F_CREATEDON=PROJECT1。C 1)
AND(Extent2。ID_RISKOBJECT=PROJECT1。ID_RISKOBJECT)))ASLIMIT1
WHERE(0 =LIMIT1。F_STATUS)OR(1 =LIMIT1。F_STATUS)
确定。唯一的解决办法,这是我发现在阅读中的初始表到列表中。不幸的是,不完全是我的意思,但它的作品。
艾滋病=(从X中
(
context.RISK_T_ASSESS_HIST.Where(X => x.ID_RISKOBJECT.HasValue和放大器;&安培; x.F_CREATEDON> = Freq.StartDate和放大器;&安培; x.F_CREATEDON< = Freq.EndDate)
。选择(X =>新建{x.ID_RISKOBJECT,x.F_CREATEDON,x.F_STATUS})了ToList()
)
团X由x.ID_RISKOBJECT到GR
让lastCreated = gr.Max(P => p.F_CREATEDON)
选择新
{
OBJID = gr.Key
,
LastStatus = gr.Where(P => p.F_CREATEDON == lastCreated和放大器;&安培; p.ID_RISKOBJECT == gr.Key)。选择(P => p.F_STATUS).FirstOrDefault()
}),其中(X => x.LastStatus == 0 || x.LastStatus == 1)。选择(X => x.ObjId.Value).ToArray();
此问题,实体框架,将被作为对他们的论坛一个错误。
After installing the .Net 4.5
, there were problems in queries generated by EF. The same query in linq to .Net 4.0
works correctly. I'm using Firebird database and it does not support command OUTER APPLY
. Is there any way to modify the following query linq, so that it behaves in a .Net 4.5
?
Current linq query:
int[] AROIds = (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 generated for. Net 4.5:
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 "RISK_T_ASSESS_HIST" AS "Extent1"
WHERE (("Extent1"."ID_RISKOBJECT" IS NOT NULL)
AND ("Extent1"."F_CREATEDON" >= 2013-04-17))
AND ("Extent1"."F_CREATEDON" <= 2013-04-17)
GROUP BY "Extent1"."ID_RISKOBJECT"
) AS "GroupBy1" ) AS "Project1"
OUTER APPLY (SELECT FIRST (1)
"Extent2"."F_STATUS" AS "F_STATUS"
FROM "RISK_T_ASSESS_HIST" AS "Extent2"
WHERE (((("Extent2"."ID_RISKOBJECT" IS NOT NULL)
AND ("Extent2"."F_CREATEDON" >= 2013-04-17))
AND ("Extent2"."F_CREATEDON" <= @2013-04-17))
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 (0 = "Limit1"."F_STATUS") OR (1 = "Limit1"."F_STATUS")
Ok. The only solution, which is what I found while reading in the initial table to the list. Unfortunately, not quite what I meant, but it works.
AIds = (from x in
(
context.RISK_T_ASSESS_HIST.Where(x => x.ID_RISKOBJECT.HasValue && x.F_CREATEDON >= Freq.StartDate && x.F_CREATEDON <= Freq.EndDate)
.Select(x => new { x.ID_RISKOBJECT, x.F_CREATEDON, x.F_STATUS }).ToList()
)
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();
This issue Entity Framework and will be passed as an error on their forum.
这篇关于OUTER适用于.NET 4.5和实体框架的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!