为什么当我执行ToList()时，此LINQ to SQL查询会中断? [英] Why would this LINQ to SQL query break when I do ToList()?

问题描述

最初我是用以下代码合并两组结果:

Originally I was merging two sets of results with code like this:

var list1 = from a in IDataSourceObject
            blahblah
            select a;

var list2 = from a in IDataSourceObject2
            blahblah
            select a;    

var joinedlist = from a in list1
                 join b in list2 on a.id = b.id
                 into fishcakes
                 from b in fishcakes.DefaultIfEmpty()
                 orderby b.ranking
                 select new { blah=cakes, etc. }

这曾经可以正常工作，但是后来我想对列表1进行更多过滤，所以我这样做了:

This used to work fine, but then I wanted to filter list 1 a little more, so I did this:

var list1 = from a in IDataSourceObject
            blahblah
            select a;

// ToList required because im calling a method in my code
var updatedList1 = from a in list1.ToList()
                   where myMethod(somestuff) == true
                   select a;   

var list2 = from a in IDataSourceObject2
            blahblah
            select a;    

var joinedlist = from a in updatedList1
                 join b in list2 on a.id = b.id
                 into fishcakes
                 from b in fishcakes.DefaultIfEmpty()
                 orderby b.ranking
                 select new { blah=cakes, etc. }

但是我得到一个错误，本质上说OrderBy b.ranking为空.执行ToList之后不再合并结果.我已经检查了updatedList1，并使myMethod始终返回true，所以从本质上讲，问题出在使用ToList().

However I get an error essentially saying that OrderBy b.ranking is null. It's not merging the results anymore after doing the ToList. I have checked updatedList1 and I made myMethod always return true, so essentially the problem comes from using ToList().

我知道这可能与延迟执行有关，但我不知道如何执行.应该完全一样.

I understand it may be something to do with deferred execution but I don't have the foggiest idea how. It should be exactly the same.

有人有什么建议吗?

推荐答案

调用fishcakes.DefaultIfEmpty()可以返回其中包含null的集合.

Calling fishcakes.DefaultIfEmpty() can return collection with null in it.

如果调用.ToList()，则所有当前结果都将复制到本地(.Net)对象，并且.ToList()之后的所有命令都将在程序中执行.

If you call .ToList(), all current results are copied to local (.Net) objects, and all commands after .ToList() would be executed in your program.

如果对.Net集合执行查询，则尝试调用null.ranking-引发NullReferenceException.同时，在SQL Server上执行不会引发异常，因为在SQL中可以要求null的子属性(它会简单地返回null).

If you execute your query against .Net collections, then trying to call null.ranking - which throws NullReferenceException. Meanwhile execution on SQL Server doesn't throws exceptions, because in SQL it's OK to ask for subproperty of null (it would simply return null).

为防止示例出现异常，您可以过滤ranking等于null的项目，也可以替换

To prevent exceptions in your example: you can either filter items, that have ranking equal to null, or replace your

orderby b.ranking 

类似这样(我假设ranking是int)

to something like this (I assume ranking is int)

orderby b != null ? b.ranking : -1

同样的情况是实现价值.例如(假设Item可能有Category，也可能没有):

Same situation is with materializing values. For example (assume, that Item may have Category, or may not have):

// this will work, because it's executed on SQL-side
db.Items
      .Select(x=>new { CatId = (int?)x.Category.Id, x.Id})
      .ToList();

// this will throw NullRefException, because it's executed against collection in .Net environment, not on SQL Server.
db.Items
      .ToList()
      .Select(x=>new { CatId = (int?)x.Category.Id, x.Id}); 

PS:如果使用Resharper，它将在第一个示例中抱怨说，不需要强制转换为int?.不要相信！ )

PS: If you use Resharper, it would complain in first example, that casting to int? is not needed. Don't believe it! )

这篇关于为什么当我执行ToList()时，此LINQ to SQL查询会中断?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！