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