Join in linq

Question

Hi, is it possible to make a join in linq and only return data from one dataset where the other key was present, a little like:

 var q = from c in customers
            join o in orders on c.Key equals o.Key
            select new {c.Name, o.OrderNumber};

and then instead of returning just the two records then returning customers like:

  var q = from c in customers
                join o in orders on c.Key equals o.Key
                select c;

When I try to do (something similar) I get this error: The specified LINQ expression contains references to queries that are associated with different contexts.

Answer 1

I going to assume that you've skipped a Where clause which involved the orders table (or otherwise the join would be pointless)

In which case, you can just have Linq infer the join.

var q = from c in customers
       where c.Orders.Any(o=> o.ProductCode == productCode)
       select c;

Linq2Sql will automatically create the Orders property if you have a foreign key defined; I believe with the Entity Framework, you have to manually specify it.

Answer 2

The error indicates an other problem:
You have to use the same DataContext on every object in the query if you're using Linq to SQL.

Your code should look somehow like that:

using (MyDataContext dc = new MyDataContext())
{
    var q = from c in dc.customers
            join o in dc.orders on c.Key equals o.Key
            select c;

    // process q within the DataContext
    // the easies would be to call q.ToList()
}

Answer 3

Will it be in EF 4.0 to create join from multiple context?

For example:

TestModelEntities e1 = new TestModelEntities();
        TestModelEntities e2 = new TestModelEntities();

        var d = from firme1 in e1.firme
                join g in e2.grad on firme1.grad.grad_id equals g.grad_id
                select firme1;

Answer 4

No, as far as VS 2010 Beta 2. I'm getting the same error message using two different context objects.