Reputation: 52952
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. }
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. }
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.
Does anyone have any suggestions?
Upvotes: 1
Views: 648
Reputation: 5783
Calling fishcakes.DefaultIfEmpty()
can return collection with null
in it.
If you call .ToList()
, all current results are copied to local (.Net) objects, and all commands after .ToList()
would be executed in your program.
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
).
To prevent exceptions in your example: you can either filter items, that have ranking
equal to null
, or replace your
orderby b.ranking
to something like this (I assume ranking
is int)
orderby b != null ? b.ranking : -1
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: If you use Resharper, it would complain in first example, that casting to int?
is not needed. Don't believe it! )
Upvotes: 7
Reputation: 3210
Because ToList() returns IEnumerable, which isn't IQueryable
Clarification:
Using Linq to SQL you're implicitly using IQueryable, so those selects, joins and ordering are translated into SQL and executed on DB server. However, converting updatedList1 to List prevents linq2sql from translating the whole statement into SQL query, and its statements are being executed one by one, just as in normal Linq. Not only it may introduce some errors (like NullReferenceException mentioned in the answer above), but also it's much less efficient than "pure" linq2sql expression.
Upvotes: 0
Reputation: 1285
join into
is analogous to a left inner join in SQL. So fishcakes could be empty for some a
and therefore fishcakes.DefaultIfEmtpy()
could return null for some a
Try
var joinedlist = from a in updatedList1
join b in list2 on a.id = b.id
into fishcakes
from b in fishcakes
where b != null
orderby b.ranking
select new { blah=cakes, etc. }
Upvotes: 2
Reputation: 37543
Because you are attempting to join 2 different (incompatible) types. If you take list2 and perform a similar .ToList()
operation on it, this should alleviate the symptom.
Upvotes: 0