DefaultIfEmpty-LINQ to SQL vs In Memory[英] DefaultIfEmpty - LINQ to SQL vs In Memory

本文是小编为大家收集整理的关于DefaultIfEmpty-LINQ to SQL vs In Memory的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

对于单元测试,我们使用内存集合来验证 LINQ 查询的逻辑.但是,在下面的场景中,我看到 LINQ to SQL 与内存中的结果之间存在差异.

对于这个例子,我们有三个表 Customer、Order、Item.我想要客户订购的所有商品的计数.我也想向没有订购任何商品的客户展示.在 SQL 中,这将是一个外连接.在 LINQ to SQL 中,我写了这个...

var itemCounts = 
   from c in Customer
   from o in Order.Where(oo=>o.CustomerId==c.CustomerId).DefaultIfEmpty()
   from i in Item.Where(ii=>i.OrderId==o.OrderId).DefaultIfEmpty()
   group i by new { i.ItemId, c.CustomerId } into ig
   select new ItemCountResult {
     CustomerId = ig.Key.CustomerId,
     Count = ig.Count()
   };

当我们针对数据库时,这可以正常工作.我们得到有订单和没有订单的客户以及数量.当我们用内存集合代替单元测试时,我们会看到对象引用未设置异常.我已将其缩小到"i.OrderId==o.OrderId"这一行,特别是 o 为空.

根据"DefaultIfEmpty"的工作原理,这实际上是我所期望的行为.DefaultIfEmpty 返回一个可枚举的 null 元素.

那么如何修复此代码以在这两种情况下工作?

更新:当我简化问题时,我丢失了一些重要的信息.所以让我重申一下这个问题.

一个客户有 0-n 个订单.一个订单有 1-n 个项目.一个项目有 1-n 顺序.

我需要商品列表以及订购该商品的客户数量.如果有 0 位客户订购了该商品,我希望它仍然被退回,但计数为 0.

问题是 Order 和 Item 之间的多对多,这使我无法使用 join-into 语法.

我目前有这样的东西(希望这次没有打错):

var counts =
  from i in Items
  from oi in OrderItems.Where(z=>z.ItemId==i.ItemId).DefaultIfEmpty()
  from o in Orders.Where(z=>z.OrderId==oi.OrderId).DefaultIfEmpty()
  from c in Customers.Where(z=>z.CustomerId==o.CustomerId).DefaultIfEmpty()
  group c by new { i.ItemId, c.CustomerId } into cg
  select new CountResult {
    CustomerId = cg.Key.CustomerId,
    Count = cg.Count()
  };

推荐答案

您的查询一开始就被骗了.这个:

from ...
from o in Order.Where(oo=>o.CustomerId==c.CustomerId).DefaultIfEmpty()
from i in Item.Where(ii=>i.OrderId==o.OrderId).DefaultIfEmpty()

... 试图在 o 真正进入范围之前使用它.我很惊讶这完全有效.看起来像你想要的:

from ...
from o in Order.Where(oo => oo.CustomerId == c.CustomerId).DefaultIfEmpty()
from i in Item.Where(ii => ii.OrderId == o.OrderId).DefaultIfEmpty()

但是,这仍然存在同样的问题 - 如果 c.CustomerId 没有客户,则 o 将为空.SQL 翻译可能不会表现出相同的行为,但坦率地说,从 IMO 开始有点奇怪.

试试这个,假设你建立了正确的关系:

from c in Customer
join i in Items on c.CustomerId equals i.Order.OrderId into items
select new { CustomerId = c.CustomerId, Count = items.Count() };

这是另一种选择,回到使用显式连接:

from c in Customer
join oi in (from o in Orders
            join i in Items on o.OrderId equals i.OrderId
            select new { o, i })
on c.CustomerId equals oi.o.CustomerId into ordersForCustomer
select new { CustomerId = c.CustomerId, Count = ordersForCustomer.Count() };

本文地址:https://www.itbaoku.cn/post/1556976.html

问题描述

For unit testing we use in memory collections to verify the logic of our LINQ queries. But, in the below scenario I'm seeing differences between the results from LINQ to SQL vs In Memory.

For this example we have three tables Customer, Order, Item. I would like the count of all items ordered by a customer. I would like to show customers who have not ordered any items as well. In SQL this would be an outer join. In LINQ to SQL I wrote this...

var itemCounts = 
   from c in Customer
   from o in Order.Where(oo=>o.CustomerId==c.CustomerId).DefaultIfEmpty()
   from i in Item.Where(ii=>i.OrderId==o.OrderId).DefaultIfEmpty()
   group i by new { i.ItemId, c.CustomerId } into ig
   select new ItemCountResult {
     CustomerId = ig.Key.CustomerId,
     Count = ig.Count()
   };

This works fine when we are going against the database. We get customers with and without orders along with counts. When we substitute in memory collections for unit testing we see an object reference not set exception. I've narrowed it down to the line "i.OrderId==o.OrderId" specifically o is null.

Based on how "DefaultIfEmpty" works, this is actually the behavior I would expect. DefaultIfEmpty returns a single element enumerable of null.

So how do I fix this code to work in both scenarios?

UPDATE: While I was simplifying the problem I lost some important pieces of information. So let me restate the problem.

A Customer has 0-n Orders. A Order has 1-n Items. A Item has 1-n Order.

I need the list of Items along with the number of customers that ordered that item. If 0 customers ordered the item I want it to still be returned but with a count of 0.

The problem is the many-to-many between Order and Item which prevents me from using the join-into syntax.

I currently have something like this (hopefully without mistypes this time):

var counts =
  from i in Items
  from oi in OrderItems.Where(z=>z.ItemId==i.ItemId).DefaultIfEmpty()
  from o in Orders.Where(z=>z.OrderId==oi.OrderId).DefaultIfEmpty()
  from c in Customers.Where(z=>z.CustomerId==o.CustomerId).DefaultIfEmpty()
  group c by new { i.ItemId, c.CustomerId } into cg
  select new CountResult {
    CustomerId = cg.Key.CustomerId,
    Count = cg.Count()
  };

推荐答案

Your query is pooched to start with. This:

from ...
from o in Order.Where(oo=>o.CustomerId==c.CustomerId).DefaultIfEmpty()
from i in Item.Where(ii=>i.OrderId==o.OrderId).DefaultIfEmpty()

... is trying to use o before it's really in scope. I'm surprised that works at all. It looks like you want:

from ...
from o in Order.Where(oo => oo.CustomerId == c.CustomerId).DefaultIfEmpty()
from i in Item.Where(ii => ii.OrderId == o.OrderId).DefaultIfEmpty()

However, that still has the same problem - o will be null if there are no customers for c.CustomerId. The SQL translation may well not exhibit the same behaviour, but it's frankly a little odd to start with IMO.

Try this instead, assuming you have the right relationship set up:

from c in Customer
join i in Items on c.CustomerId equals i.Order.OrderId into items
select new { CustomerId = c.CustomerId, Count = items.Count() };

Here's another alternative, back to using explicit joins:

from c in Customer
join oi in (from o in Orders
            join i in Items on o.OrderId equals i.OrderId
            select new { o, i })
on c.CustomerId equals oi.o.CustomerId into ordersForCustomer
select new { CustomerId = c.CustomerId, Count = ordersForCustomer.Count() };