DefaultIfEmpty-LINQ to SQL与内存

本文介绍了DefaultIfEmpty-LINQ to SQL与内存的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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.

在此示例中，我们有三个表Customer，Order，Item.我想要一个客户订购的所有物品的数量.我想向那些还没有订购任何商品的客户展示.在SQL中，这将是一个外部联接.我在LINQ to SQL中写了这个...

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()
   };

当我们要使用数据库时，这可以很好地工作.我们获得带订单和不带订单的客户.当我们用内存集合代替单元测试时，我们看到对象引用未设置异常.我将其范围缩小到"i.OrderId == o.OrderId"行，特别是o为空.

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.

基于"DefaultIfEmpty"的工作原理，这实际上是我所期望的行为. DefaultIfEmpty返回一个可枚举为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.

一个客户有0-n个订单.订单有1-n个项目.一个商品有1-n个订单.

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

我需要商品列表以及订购该商品的客户数量.如果0位顾客订购了该商品，我希望它仍能退还，但计数为0.

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.

问题是订单和商品之间的多对多关系，这使我无法使用join-into语法.

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

...试图在实际范围内使用o.令我惊讶的是，它真的奏效了.看起来像您想要的:

... 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()

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

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() };

这篇关于DefaultIfEmpty-LINQ to SQL与内存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

sql