CODEWITHSUNDEEP
c#linq-to-sql
NibblyPig
NibblyPig

Reputation: 52952

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. }

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

Answers (4)

The Smallest
The Smallest

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

buru
buru

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

Sarin
Sarin

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

Joel Etherton
Joel Etherton

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

Related Questions