none
How do I get LINQ to join on many-to-many relations? RRS feed

  • Question

  • Hi everyone!

    I have noticed that Linq behaves strangely with the way I define many-to-many relations in my database. I have tried to search this forum and I have read a number of blog posts about many-to-many relations, but I can't figure out what I am doing wrong, so I thought I would ask the question here. Perhaps some of you guys can give some pointers on how I can improve the performance, either by defining the relations differently or by writing better Linq queries.

    Okay, consider the following minimal test database:

    Table: User
    - (PK) ID, int
    - Name, varchar(50) not null

    Table: Order
    - (PK) ID, int
    - Product, int not null
    - Amount, varchar(50) not null

    Table: UserOrder
    - UserID, int references User.ID
    - OrderID, int references Order.ID
    PK for this table is (UserID, OrderID)

    Given that database I add a .dbml file to my project and drag the three classes into the designer, and then I run the following two Linq-queries:
    var model = new
     DataClasses1DataContext();
    var user = model.Users.Single(x => x.Name == "TestUser"
    );
    var orders = user.UserOrders.Select(x => x.Order).ToList();<br/>
    
    
    If I examine the output of the above with the datacontext Log property set to the debug console, I can see that Linq generates this SQL:

    SELECT
     [t0].[ID], [t0].[Name]
    FROM
     [dbo].[User] AS
     [t0]
    WHERE
     [t0].[Name] = @p0
    -- @p0: Input VarChar (Size = 8; Prec = 0; Scale = 0) [TestUser]
    
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1
    
    
    SELECT
     [t0].[UserID], [t0].[OrderID]
    FROM
     [dbo].[UserOrder] AS
     [t0]
    WHERE
     [t0].[UserID] = @p0
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1
    
    
    SELECT
     [t0].[ID], [t0].[Product], [t0].[Amount]
    FROM
     [dbo].[Order] AS
     [t0]
    WHERE
     [t0].[ID] = @p0
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1
    
    
    SELECT
     [t0].[ID], [t0].[Product], [t0].[Amount]
    FROM
     [dbo].[Order] AS
     [t0]
    WHERE
     [t0].[ID] = @p0
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
    
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1
    
    
    SELECT
     [t0].[ID], [t0].[Product], [t0].[Amount]
    FROM
     [dbo].[Order] AS
     [t0]
    WHERE
     [t0].[ID] = @p0
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
    
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1
    
    

    That is, one for the user (as expected), one for the relationtable and then one for each resulting row in the Order table!

    I would expect the above two Linq statements to result in two SQL statements, something like this:
    select
     * from
     [User] where
     [Name] = 'TestUser'
    ;
    
    select
     [Order].*
    from
     [Order] inner
     join
     [UserOrder]
    on
     [Order].[ID] = [UserOrder].[OrderID]
    where
     [UserOrder].[UserID] = 1;
    

    I would appreciate any help from anyone who can explain what I am doing wrong, and how I can improve the generated SQL.

    Sincerely, Anders Madsen
    Monday, May 25, 2009 7:09 PM

Answers

  • No the problem is in your Linq:

    var orders = user.UserOrders.Select(x => x.Order).ToList();

    Here Select() is a projection operator and

    user.UserOrders.Select ( x =>

    getting each order of user as x. Then  from that x getting the order of that x ( sort of a cycle, user -< orders -< order ). You could do it like this:

    var

     

     

    customer = db.Customers.Where (c => (c.CustomerID == "ALFKI"));
    var salesMan = customer
                   .SelectMany ( c => c.Orders, (c, o) => o.Employee);


    With your data:

    var

     

     

    user = model.Users.Where(x => x.Name == "TestUser");
    var orders = user.SelectMany(x => x.UserOrders, (x,o) => o.Order).ToList();

     
    BTW how you are editing and testing your Linq queries (and doing a lot more usefull things interactively)? I would strongly suggest using Joe Albahari's cool LinqPad utility from www.LinqPad.Net Though it is for free I would strongly suggest to pay a few buck and get the autocompletion feature. How usefull it is beyond my English, check it. youreself.

    Monday, May 25, 2009 9:06 PM

All replies

  • The old good Northwind sample database represent many-to-many between Customer and Employee over Orders:

    Customers: CustomerID, CompanyName ...

    Orders: CustomerID, EmployeeID, ...

    Employees: EmployeeID, ....

    var

    customer = db.Customers.Single( c => c.CustomerID == "ALFKI" );

    var salesMan = db.Orders
                   .Where( o => o.Customer == customer )
                   .Select( o => o.Employee )
                   .Distinct();

    would select all salesman who served ALFKI.

    Monday, May 25, 2009 7:49 PM
  • Thank you, that works great! It produces exactly the SQL I was expecting!

    But, if it is possible, I would really like it if you could explain a little about what is wrong with using the Orders property on a User? I mean, doesn't the dbml designer tool generate that kind of properties because it wants you to use them?

    I still fail to understand why these two aproaches doesn't produce the same SQL - but I can gather that the prefered way of making queries is to always start from the datacontext?

    It seems so, because this also produces the expected SQL:
    var orders = model.Users
        .Where(x => x.Name == "TestUser")
        .SelectMany(x => x.UserOrders.Select(y => y.Order));
    Sincerly, Anders Madsen
    Monday, May 25, 2009 8:26 PM
  • No the problem is in your Linq:

    var orders = user.UserOrders.Select(x => x.Order).ToList();

    Here Select() is a projection operator and

    user.UserOrders.Select ( x =>

    getting each order of user as x. Then  from that x getting the order of that x ( sort of a cycle, user -< orders -< order ). You could do it like this:

    var

     

     

    customer = db.Customers.Where (c => (c.CustomerID == "ALFKI"));
    var salesMan = customer
                   .SelectMany ( c => c.Orders, (c, o) => o.Employee);


    With your data:

    var

     

     

    user = model.Users.Where(x => x.Name == "TestUser");
    var orders = user.SelectMany(x => x.UserOrders, (x,o) => o.Order).ToList();

     
    BTW how you are editing and testing your Linq queries (and doing a lot more usefull things interactively)? I would strongly suggest using Joe Albahari's cool LinqPad utility from www.LinqPad.Net Though it is for free I would strongly suggest to pay a few buck and get the autocompletion feature. How usefull it is beyond my English, check it. youreself.

    Monday, May 25, 2009 9:06 PM
  • Thank you! That was an excellent explanation, as I was reading it I could just feel the light come on in my head. I'm actually almost embarrassed that I didn't realize that myself - but anyway I do realize it now :-)

    Oh and I already bought Linqpad - it is really terribly useful :-)

    Sincerely, Anders Madsen
    Monday, May 25, 2009 9:29 PM