# Wednesday, 20 May 2009

This came up in class yesterday, so I did a little digging.  Everyone may already know this, but it came as news to me. :)

If I’ve got a collection in memory, as well as a LINQ to SQL DataContext

List<FavoriteFood> foods = new List<FavoriteFood>(){
	new FavoriteFood{CustomerID = "ALFKI", Favorite="Chips"},
	new FavoriteFood{CustomerID = "ANATR", Favorite = "Fish"}};

NorthwindDataContext context = new NorthwindDataContext();

and I want to do an INNER JOIN between the foods list and the Customer table in Northwind, it would seem like this should do it

var bad = (from cust in context.Customers
         join f in foods on cust.CustomerID equals f.CustomerID
         select new
         {
             cust.ContactName,
             f.Favorite
         }).ToList();

but sadly, no.

image

if you stop to think about it, it totally makes sense that it wouldn’t work like that, since there’s no way to translate that into SQL in any rational way to send to SQL server. 

OK, so the next step would be to first get the customers in memory, then do the join

//this executes the whole query, thus retrieving the entire customer table
var customers = (from c in context.Customers
             select c).ToList();

//an inner join between the customers from SQL and the in-memory list
var inner = from cust in customers
     join f in foods on cust.CustomerID equals f.CustomerID
     select new
     {
         cust.ContactName,
         f.Favorite
     };

That works, but I’ve had to pull the entire Customer table into memory just to join two rows.  If I wanted to do a LEFT OUTER JOIN, I’d need that anyway, like so

//here's the left outer join between the customer list from SQL 
//and the in-memory favorites
var leftouter = from cust in customers
             join f in foods on cust.CustomerID equals f.CustomerID into custFoods
             from custFood in custFoods.DefaultIfEmpty()
             select new
             {
                 cust.ContactName,
                 Favorite = custFood == null ? null : custFood.Favorite
             };

but I want an inner join without pulling down all the customers, so I need to only fetch those rows that will join from Customer

//this is how you manage the IN clause
var x = from c1 in context.Customers
     where (from cf in foods
           select cf.CustomerID).Contains(c1.CustomerID)
     select c1;

//Note that to join the result in x back to the foods collection you would have to 
//execute the query just like with customers above...
var littleInnerJoin = from filteredCustomer in x.ToList()
                   join f in foods on filteredCustomer.CustomerID equals f.CustomerID
                   select new
                       {
                           filteredCustomer.ContactName,
                           f.Favorite
                       };

It’s two steps, but now I’ve just loaded the rows that will join into memory, and then done the join with LINQ to Objects.  It bears a little thinking about, but doesn’t seem like too much overhead, IMHO.

Work | LINQ
Wednesday, 20 May 2009 13:38:43 (Pacific Daylight Time, UTC-07:00)  #    Disclaimer  |  Comments [0]  |