none
Joins across multiple tables in linq RRS feed

  • Question

  • Hi All,

    I am trying to create a linq query using 4 different table from a datasource.

    I want to return all items from StockItems table regardless of whether or not they have a supplier code attached to them. StockItemSuppliers is the foreign key to this table using ItemID....

    I realise that I need an outerjoin(I think!!).....but cannot seem to get this to work...

    Below is my current code...

    Thanks a million in advance for any ideas..

    Cheers

    Aidan

    var q = from st in cxt.StockItems
    join sup in cxt.StockItemSuppliers on st.ItemID equals sup.ItemID .DefaultIfEmpty()
    join x in cxt.StockItemXs on st.ItemID equals x.StockItemXID
    join pg in cxt.ProductGroups on st.ProductGroupID equals pg.ProductGroupID
    where sup.Preferred==true
    select new
    {
    	st.Code,
    st.Name,
    
    sup.SupplierStockCode,
    sup.SupplierAccountName,
    st.StandardCost,
    x.Margin,
    x.SellPrice,
    x.StonehouseRebate,
    x.StonehouseSellPrice,
    pg.Description
    
    };
    
    q = q.Distinct();
    
    return q;
    


    Tuesday, April 22, 2014 11:01 AM

Answers

  • Hello,

    >>I realise that I need an outerjoin(I think!!).....

    If you want to perform a left out join, you can use the syntax as below:

    from person in people
    
    join pet in pets on person equals pet.Owner into gj
    
    from subpet in gj.DefaultIfEmpty()
    

    For details, please check this link:
    http://msdn.microsoft.com/en-us/library/bb397895.aspx

    >>but cannot seem to get this to work...

    How does your current code not work for? Does it throw an error message? If it has, please share it with us.

    If you want to know more about LINQ, you can refer to below links:

    http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    http://msdn.microsoft.com/en-us/library/bb397927.aspx

    Hope this to be helpful.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 23, 2014 5:38 AM
    Moderator