UseDatabaseNullSemantics仍会生成NULL检查 [英] UseDatabaseNullSemantics still generating NULL checks
问题描述
我的上下文对象(构造函数)上有这个
I have this on my context object (constructor):
this.Configuration.UseDatabaseNullSemantics = true;
但是即使设置了这个,该查询:
But even with this set, this query:
var query = (from i in _repo.Invoices
join o in _repo.Orders on i.orderid equals o.orderid
join o2 in _repo.Orders on o.linkedorderid equals o2.linkedorderid into leftOrders
from lo in leftOrders.DefaultIfEmpty()
where invoiceIds.Contains(i.invoiceid)
select new
{
i, lo
}).ToList();
返回此SQL:
SELECT
[Extent1].[invoiceid] AS [invoiceid],
[Extent1].[custid] AS [custid],
[Extent1].[orderid] AS [orderid],
[Extent1].[orderamount] AS [orderamount],
[Extent1].[invoiceamount] AS [invoiceamount],
[Extent1].[paidamount] AS [paidamount],
[Extent1].[paidstatus] AS [paidstatus],
[Extent1].[printdate] AS [printdate],
[Extent1].[updateddate] AS [updateddate]
FROM [dbo].[invoices] AS [Extent1]
INNER JOIN [dbo].[orders] AS [Extent2] ON [Extent1].[orderid] = [Extent2].[orderid]
LEFT JOIN [dbo].[orders] AS [Extent3] ON ([Extent2].[linkedorderid] = [Extent3].[linkedorderid]) OR (([Extent2].[linkedorderid] IS NULL) AND ([Extent3].[linkedorderid] IS NULL))
WHERE [Extent1].[invoiceid] IN (3098489, 3123185, 3156838)
不难发现我遇到了System.OutOfException错误,因为我有成千上万的具有linkedorderid等于NULL的订单.
It's not hard to see that I'm getting a System.OutOfException error since I have tons of orders with linkedorderid equals NULL.
如果我删除了OR (([Extent2].[linkedorderid] IS NULL) AND ([Extent3].[linkedorderid] IS NULL))
,查询运行正常,并返回所有具有子工作订单的发票...
If I remove OR (([Extent2].[linkedorderid] IS NULL) AND ([Extent3].[linkedorderid] IS NULL))
query runs fine and returns all invoices that have or not children work orders...
任何想法我该如何解决?
Any idea how can I solve this ?
推荐答案
这项工作有效吗?如果删除(或(null并且为null))子句时发布的查询正确,那么我认为这将执行相同的查询. (这假定联接中没有逻辑错误,如其他帖子中所建议的那样.)
Does this work? If the posted query is correct when you remove the (or (is null and is null)) clause, then I think this will execute the same query. (This assumes there isn't a logic error in the joins, as suggested in other posts.)
var query = (from i in _repo.Invoices
from o in _repo.Orders.Where(c => c.orderid == i.orderid)
from o2 in _repo.Orders.Where(c => c.linkedorderid == o.linkedorderid).DefaultIfEmpty()
where invoiceIds.Contains(i.invoiceid)
select new
{
i, o2
}).ToList();
这篇关于UseDatabaseNullSemantics仍会生成NULL检查的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!