none
multiple join query in linq RRS feed

  • Question

  • I have a few tables that need to be joined as in the tsql below:

    SELECT     dbo.MealType.mealTypeName, dbo.menuCategories.menuCategoryName, dbo.MenuItems.MenuItemName, dbo.MenuItems.TodayMenu,
                          dbo.MenuItems.MenuItemDescription
    FROM         dbo.MenuItems INNER JOIN
                          dbo.menuCategories ON dbo.MenuItems.MenuCategoryID = dbo.menuCategories.MenuCategoryID INNER JOIN
                          dbo.MealType ON dbo.MenuItems.MealTypeID = dbo.MealType.mealTypeID AND dbo.menuCategories.MealTypeID = dbo.MealType.mealTypeID
    WHERE     (dbo.MealType.mealTypeName = 'lunch') AND (dbo.MenuItems.TodayMenu = 1)

    I can figure out most of the LINQ, but can not see how to express the AND condition. 

    What I 9incorectly)  have is: 

    var

     

     

    myMenu = (from m in db.MenuItems

     

     

    join c in db.menuCategories on m.MenuCategoryID equals c.MenuCategoryID

     

     

    join t in db.MealTypes on

     

    c.MealTypeID

     

    equals t.mealTypeID

     

     

     

    where

    ((t.mealTypeID == MealTypeID)

    && (m.TodayMenu ==

     

    true))

     

     

    select new

    {

    m.MenuItemName,

    m.MenuItemDescription,

    m.MenuItemPrice,

    c.menuCategoryName

    }

    )

     

    Can a LINQ guru please help and explain?

     

    Thank you,

     

    Paolo

    Wednesday, July 21, 2010 7:02 PM

Answers

  • Hi Paolo,

     

    Based on the T-SQL you showed, I think you need to change your where clause in the LINQ query a little because “MealTypeID” in the where will not be recognized in the context. You can try something like this:

     

     

    var myMenu = (from m in db.MenuItems
        join c in db.MenuCategories on m.MenuCategoryID equals c.MenuCategoryID
        join t in db.MealTypes on m.MealTypeID equals t.MealTypeID
        where c.MealTypeID == t.MealTypeID && m.TodayMenu == true && t.MealTypeName == "lunch"
        select new
        {
         t.MealTypeName,
         c.MenuCategoryName,
         m.MenuItemName,
         m.TodayMenu, 
         m.MenuItemDescription    
        });
    
    

     


    If you have any other questions, please feel free to let me know.

     


    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 22, 2010 6:41 AM
    Moderator

All replies

  • I don't see why your's doesn't work. I am guessing you don't really need the joins since you could have embedded froms like the following. Addtionally I would limit before the join like this, you can move a where before you join or from.

     

    var anonymous = (from item in db.MenuItems
      where item.IsTodaysMenu
      from category in item.Categories
      from meal in category.MealTypes
      where meal.Id == mealId
      select new 
       {
        item.Name,
        item.Description,
        item.Price
        category.Name
       }).ToList();
    

     

    Or a double where after.

    var anonymous = (from item in db.MenuItems
      from category in item.Categories
      from meal in category.MealTypes
      where item.IsTodaysMenu
      where meal.Id == mealId
      select new 
       {
        item.Name,
        item.Description,
        item.Price
        category.Name
       }).ToList();
    
    Wednesday, July 21, 2010 7:39 PM
  • Hi Paolo,

     

    Based on the T-SQL you showed, I think you need to change your where clause in the LINQ query a little because “MealTypeID” in the where will not be recognized in the context. You can try something like this:

     

     

    var myMenu = (from m in db.MenuItems
        join c in db.MenuCategories on m.MenuCategoryID equals c.MenuCategoryID
        join t in db.MealTypes on m.MealTypeID equals t.MealTypeID
        where c.MealTypeID == t.MealTypeID && m.TodayMenu == true && t.MealTypeName == "lunch"
        select new
        {
         t.MealTypeName,
         c.MenuCategoryName,
         m.MenuItemName,
         m.TodayMenu, 
         m.MenuItemDescription    
        });
    
    

     


    If you have any other questions, please feel free to let me know.

     


    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 22, 2010 6:41 AM
    Moderator
  • Hi Paolo,

    I'm writing to follow up the post. Does the above suggestion work?

    Please feel free to let me know if you need any help.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, July 28, 2010 6:27 AM
    Moderator
  • Hi Paolo,

    I'm marking my reply as answer.

    Please feel free to let me know if you need any help.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, July 31, 2010 6:16 AM
    Moderator