locked
Entity Framework: How to perform left join with EF and LINQ among multiple tables RRS feed

  • Question

  • basically i have 3 tables and those are user,colors and usercolor

    tables info

    User Tables has fields like -> UserID, UserName
    
    Color Tables has fields like -> ColorID, ColorName
    
    UserColor Tables has fields like -> UserID, ColorID

    i have corresponding dbset classes

    in my code.

    now see the below query where left join is performed among 3 tables in sql and tell me how to write the same equivalent query with EF and LINQ.

    select c.ColorID
        , c.ColorName
        , IsSelected = case when uc.ColorID is null then 0 else 1 end
    from dbo.Colors c
    left join dbo.UserColor uc on uc.ColorID = c.ColorID and uc.UserID = 1 --leave this in the join or it becomes an inner join
    left join dbo.Users u on u.UserID = uc.UserID

    thanks

    Monday, September 26, 2016 2:55 PM

Answers

  • The equivalent would roughly be something like this (of course untested and uncompiled due to the lack of your entity classes and appropriate sample data):

    var query = from c in context.Color
                join uc in context.UserColor on new { c.ColorId, c.ColorId} equals {uc.ColorId, 1} into cuc
                from x in cuc.DefaultIfEmpty()
                join u in context.User on u.UserId equals cuc.UserId into ucuc
                from y in ucuc.DefaultIfEmpty()
                select new { x.ColorId, x.ColorName, x.ColorId == 0 ? false : true };


    Please refer to the following link for more samples of how to do multiple LEFT joins in LINQ: http://stackoverflow.com/questions/267488/linq-to-sql-multiple-left-outer-joins

    Having that said, it is generally not a good idea to simply try to convert fairly complex T-SQL queries to LINQ due to the fact the a raw T-SQL query is always faster than a query generated by the plan compiler and the plan compiler is not that good on generating complex queries that contain for example sub queries. You might as well execute raw T-SQL queries against the database and then map the results to the object types in your model. Please refer to the following link for more information this: https://msdn.microsoft.com/en-us/data/jj592907.aspx.


    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Monday, September 26, 2016 6:18 PM

All replies

  • To this using LINQ, everything is the much the same, except your IsSelected use the keyword equals instead of the = sign.

    Use,

     DefaultIsEmpty

    to obtain the left side of the data, which should obtain any data that contains the matching UserColors that is empty for Colors.

    Example here : https://msdn.microsoft.com/en-us/library/bb360179.aspx

    Monday, September 26, 2016 3:24 PM
  • The equivalent would roughly be something like this (of course untested and uncompiled due to the lack of your entity classes and appropriate sample data):

    var query = from c in context.Color
                join uc in context.UserColor on new { c.ColorId, c.ColorId} equals {uc.ColorId, 1} into cuc
                from x in cuc.DefaultIfEmpty()
                join u in context.User on u.UserId equals cuc.UserId into ucuc
                from y in ucuc.DefaultIfEmpty()
                select new { x.ColorId, x.ColorName, x.ColorId == 0 ? false : true };


    Please refer to the following link for more samples of how to do multiple LEFT joins in LINQ: http://stackoverflow.com/questions/267488/linq-to-sql-multiple-left-outer-joins

    Having that said, it is generally not a good idea to simply try to convert fairly complex T-SQL queries to LINQ due to the fact the a raw T-SQL query is always faster than a query generated by the plan compiler and the plan compiler is not that good on generating complex queries that contain for example sub queries. You might as well execute raw T-SQL queries against the database and then map the results to the object types in your model. Please refer to the following link for more information this: https://msdn.microsoft.com/en-us/data/jj592907.aspx.


    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Monday, September 26, 2016 6:18 PM
  • what is this cuc.DefaultIfEmpty() ?

    just do not understand this line too join uc in context.UserColor on new { c.ColorId, c.ColorId} equals {uc.ColorId, 1} into cuc

    no left keyword has been used to indicate the query is left outer join.

    how people understand just seeing EF & LINQ query that the query is related to left outer join or right outer join ?


    Monday, September 26, 2016 6:35 PM
  • Hi Mou_inn,

    >>what is this cuc.DefaultIfEmpty() ?

    DefaultIfEmpty returns the elements of the specified sequence or the type parameter's default value in a singleton collection if the sequence is empty.

    For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/bb360179.aspx

    >>just do not understand this line too join uc in context.UserColor on new { c.ColorId, c.ColorId} equals {uc.ColorId, 1} into cuc

    it is a boolean comparisons with anonymous type.

    >>how people understand just seeing EF & LINQ query that the query is related to left outer join or right outer join ?

    Please check another thread you posting

    https://social.msdn.microsoft.com/Forums/en-US/60737b4d-dc13-4330-af92-402c9b65688f/how-people-understand-just-seeing-ef-linq-query-that-the-query-is-related-to-left-outer-join-or?forum=adodotnetentityframework

    Best regards,

    Cole Wu


    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.

    Tuesday, September 27, 2016 8:07 AM

  • what is DefaultIsEmpty keyword for ?
    Tuesday, September 27, 2016 9:06 AM
  • Sorry still DefaultIfEmpty is not clerar.

    just do not understand this line too join uc in context.UserColor on new { c.ColorId, c.ColorId} equals {uc.ColorId, 1} into cuc

    why same color id has been refer twice in code like on new { c.ColorId, c.ColorId} ?

    Tuesday, September 27, 2016 9:09 AM
  • >>Sorry still DefaultIfEmpty is not clerar.

    It returns an empty collection with a collection of one default value. Here is yet another example: http://stackoverflow.com/questions/19293844/linq-join-iquery-how-to-use-defaultifempty

    >>just do not understand this line too join uc in context.UserColor on new { c.ColorId, c.ColorId} equals {uc.ColorId, 1} into cuc

    >>why same color id has been refer twice in code like on new { c.ColorId, c.ColorId} ?

    It is the way you join on more than one field in LINQ (JOIN dbo.UserColor uc on uc.ColorID = c.ColorID and uc.UserID = 1). Please refer to the following thread for more information: http://stackoverflow.com/questions/373541/how-to-do-joins-in-linq-on-multiple-fields-in-single-join


    Hope that helps.

    Please remember to close your threads by marking all helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Tuesday, September 27, 2016 7:09 PM