none
how to left outer join two tables in Linq? RRS feed

  • Question

  • Hi,

    I have two tables: Item, ItemFee.

    Item Table has ItemID, ItemName, ItemDescrip

    ItemFee has ItemID,  feeId, ItemFee

    Item is parent table. they Join bItemIDs.

    The T-sql like select i.*, f.* from item i left outer join itemfee on i.ItemId = f.itemId and feeId = 2

    where i.itemid = 3

    

    How can I use a linq to achieve same result?

    Thanks.

    Monday, October 29, 2012 5:28 AM

Answers

  • Sorry Peter my error. At that point where you get the error you have a collection of itemfee and not a single instance. Modify the query as follows.

    var results = (from i in ctx.item.Include("itemfee")
                   where i.itemid == 3 && i.itemfees.Any( f => f.feeId == 2)
                   select i).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by peter 9 Wednesday, October 31, 2012 10:31 PM
    Tuesday, October 30, 2012 10:55 PM

All replies

  • hi,

    this might help you.

                var joinresult = from item in items
                        join fee in itemfees on item.ItemID equals fee.ItemID
                        select new { item, fee };

    but i would recommend you to read : 101 LINQ Samples

    Thanks,

    Vinil;

    Monday, October 29, 2012 6:40 AM
  • Hi Peter;

    If you have created an association between the two tables then you can do something like this code snippet. Please note that the term itemfees is the navigation property name in the model and may have to be changes in my code snippet to match your code. The results i which is an instance of item will have attached an itemfee and itemfee will be null if none exist. Also the name ctx in the code snippet needs to be changed to the instance of the context you are using.

    var results = (from i in ctx.item.Include("itemfee")
                   where i.itemid == 3 && i.itemfees.feeId == 2
                   select i).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".


    Monday, October 29, 2012 1:52 PM
  • Hi Fernando,

    feeId does not exist in i.itemfees.

    Thank you.

    Peter

    Monday, October 29, 2012 10:22 PM
  • Hi Vinil,

    where should I write the feeid == 2?

    Thank you.

    peter

    Monday, October 29, 2012 10:23 PM
  • Hi Peter;

    OK there needs to be an association in the database between the two tables. If this is NOT code first project then the edmx file designer will have navigation property at the bottom of the item table that will connect to the itemfee table. This property is a collection of itemfee and itemfee does have a feeid column as per your post.

    Does the database have a association configured between the two tables?

    Is this a Code First or Database First Project? If Database First then please post a screen shot of the two tables from the edmx designer. If Code First please post the classes of the two tables and all the configuration code of the two classes.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, October 29, 2012 11:12 PM
  • Hi Fernando,

    I am not using code first. In the edmx, both tables have the navigation properties. ie. item has itemfees and itemfee has item as the navigation property.

    Thanks.

    Peter

    Tuesday, October 30, 2012 10:20 PM
  • Sorry Peter my error. At that point where you get the error you have a collection of itemfee and not a single instance. Modify the query as follows.

    var results = (from i in ctx.item.Include("itemfee")
                   where i.itemid == 3 && i.itemfees.Any( f => f.feeId == 2)
                   select i).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by peter 9 Wednesday, October 31, 2012 10:31 PM
    Tuesday, October 30, 2012 10:55 PM
  • Hi Fernando,

    It works. Thank u very much.

    Peter

    
    Wednesday, October 31, 2012 10:33 PM
  •  

    Not a problem Peter, glad I was able to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, November 1, 2012 3:16 AM