none
Using EF to perform mulitple table selection RRS feed

  • Question

  • Given an SQL statment like this:

     
    SELECT 
    Table2.area as Area1, 
    Table4.area as Area2, 
    FieldA, 
    sum(FieldB) as SUMA, 
    sum(FieldC) as SUMB
    
    FROM Table1,Table2,Table3,Table4
    
    WHERE Table3.ID = MyIDParameter
    AND Table3.ID = Table1.ID
    AND Table3.ID = Table4.ID

    Would the LINQ equivalent be:

    var stuff = from T1 in db.Table1 join T3 in db.Table3 on T1.ID equals T1.ID join T4 in db.Table4 on T3.ID equals T4.ID

    join T2 in db.Table2 on NOTHING? where

    T3.ID = MyIDParameter select T2.Area as Area1, T4.Area as Area2, FieldA, Sum(FieldB) as SUMA Sum(FieldC as SUMB



    JP Cowboy Coders Unite!


    Thursday, September 4, 2014 5:46 PM

Answers

  • Hello Javaman,

    It seems that you want to know how to perform cross join in LINQ, for this, we do not need to use the join key word, it is similar with the sql statement, in you LINQ, you could just remove the join and use from:

    var stuff = from T1 in db.Table1 join T3 in db.Table3 on T1.ID equals T1.ID join T4 in db.Table4 on T3.ID equals T4.ID from T2 in db.Table2 where T3.ID = MyIDParameter select T2.Area as Area1, T4.Area as Area2,

    If I misunderstand, please let me know.

    Best Regards,

    Fred.


    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.


    Friday, September 5, 2014 1:14 AM
    Moderator
  • Hello,

    >> What's the difference in using the Joins to emulate the SQL query from just using where clauses?

    I am not quite sure what you mean, Joins in LINQ would be translated to be INNER JOIN in your provided LINQ query. So it seems that you are asking the difference between JOIN and just use where clause in database, as far as I know, if we just use where clause, it would perform the cross join, I am not sure if it has a performance issue since it does not combine the rows and produces results that consist of every combination of rows from two or more tables, for database feature, i would suggest that you could post it to the SQL Sever forum.

    In my opinion, i would select the JOIN sytanx, because it's a lot clearer and maybe have a better performance(I am not sure about this since i do not test it). 

    If I misunderstand, please let me know.

    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.

    • Marked as answer by Mr. Javaman II Friday, September 5, 2014 12:53 PM
    Friday, September 5, 2014 6:13 AM
    Moderator

All replies

  • Hello Javaman,

    It seems that you want to know how to perform cross join in LINQ, for this, we do not need to use the join key word, it is similar with the sql statement, in you LINQ, you could just remove the join and use from:

    var stuff = from T1 in db.Table1 join T3 in db.Table3 on T1.ID equals T1.ID join T4 in db.Table4 on T3.ID equals T4.ID from T2 in db.Table2 where T3.ID = MyIDParameter select T2.Area as Area1, T4.Area as Area2,

    If I misunderstand, please let me know.

    Best Regards,

    Fred.


    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.


    Friday, September 5, 2014 1:14 AM
    Moderator
  • What's the difference in using the Joins to emulate the SQL query from just using where clauses?

    JP Cowboy Coders Unite!

    Friday, September 5, 2014 3:41 AM
  • Hello,

    >> What's the difference in using the Joins to emulate the SQL query from just using where clauses?

    I am not quite sure what you mean, Joins in LINQ would be translated to be INNER JOIN in your provided LINQ query. So it seems that you are asking the difference between JOIN and just use where clause in database, as far as I know, if we just use where clause, it would perform the cross join, I am not sure if it has a performance issue since it does not combine the rows and produces results that consist of every combination of rows from two or more tables, for database feature, i would suggest that you could post it to the SQL Sever forum.

    In my opinion, i would select the JOIN sytanx, because it's a lot clearer and maybe have a better performance(I am not sure about this since i do not test it). 

    If I misunderstand, please let me know.

    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.

    • Marked as answer by Mr. Javaman II Friday, September 5, 2014 12:53 PM
    Friday, September 5, 2014 6:13 AM
    Moderator
  • Thanks Fred, 

       I never really considered the differences when using pure SQL until I tried to convert to LINQ.  At first I didn't realize that LINQ supports multiple from statements, so I was stuck accidentally with Joins, if you look at the SQL above there is no explicit Join, so that got me thinking "just what does SQL do when there's no explicit join but multiple tables?"   In the SQL above there's obviously some type of join happening based on the where clause logic.

    I asked a DB person about this yesterday and he told me "I always do joins because they are more efficient".  I took that as good advice.  So in the future when I'm converting SQL to LINQ I will employ joins instead of where clauses.

    I will say this however, when attempting to join on multiple fields, the syntax is a bit challenging.  The same is true for GroupBy.  


    JP Cowboy Coders Unite!

    Friday, September 5, 2014 1:05 PM