The following code has the same result, but whichever is faster in bringing the result

Question

The first code was used "join"

But in the second code is not used "join"

Note that the result is the same.

So I have several questions

Which is better?
Which is faster?

Code01:

    (from Member in dContext.TB_FamilyCardMembers
    select new
    {
        Member.FamilyCard_ID,
        Member.TB_FamilyCard.NoFamilyCard,
        CardType = Member.TB_FamilyCard.Is_Card == true ? "دفتر عائلة" : "بيان عائلي",
        FirsN = Member.TB_Person.FirstName,
        FatherN = Member.TB_Person.FatherName == null ? SelectPersonByID(int.Parse(Member.TB_Person.Father_ID.ToString())).FirstName : Member.TB_Person.FatherName,
        LastN = Member.TB_Person.LastName == null ? SelectPersonByID(int.Parse(Member.TB_Person.Father_ID.ToString())).LastName : Member.TB_Person.LastName,
        MotherN = Member.TB_Person.MotherName == null ? SelectPersonByID(int.Parse(Member.TB_Person.Mother_ID.ToString())).FirstName : Member.TB_Person.MotherName,
        MotherLN = Member.TB_Person.MotherLastName == null ? SelectPersonByID(int.Parse(Member.TB_Person.Mother_ID.ToString())).LastName : Member.TB_Person.MotherLastName
    }).ToList();

______________________________________________

Code02:

(from Member in dContext.TB_FamilyCardMembers
join Card in dContext.TB_FamilyCards on Member.FamilyCard_ID equals Card.ID
join Person in dContext.TB_Persons on Member.Person_ID equals Person.ID
select new
{
    Member.FamilyCard_ID,
    Card.NoFamilyCard,
    CardType = Card.Is_Card == true ? "دفتر عائلة" : "بيان عائلي",
    FirsN = Person.FirstName,
    FatherN = Person.FatherName == null ? SelectPersonByID(int.Parse(Person.Father_ID.ToString())).FirstName : Person.FatherName,
    LastN = Person.LastName == null ? SelectPersonByID(int.Parse(Person.Father_ID.ToString())).LastName : Person.LastName,
    MotherN = Person.MotherName == null ? SelectPersonByID(int.Parse(Person.Mother_ID.ToString())).FirstName : Person.MotherName,
    MotherLN = Person.MotherLastName == null ? SelectPersonByID(int.Parse(Person.Mother_ID.ToString())).LastName : Person.MotherLastName
}).ToList();

This is really a question for http://codereview.stackexchange.com/ — Sudsy, Jun 10 '16 at 22:39
The join takes a lot of resources and is therefore less efficient. — jdweng, Jun 11 '16 at 01:54

score 0 · Answer 1 · edited May 23 '17 at 11:58

All roads lead to Rome

Just because there is no join in the Linq code, does not mean there is no join in the final query.

Once you use

Member.TB_Person.FirstName

Linq-2-sql will post probably add a join to that generated SQL.

A lot of coders explicitly add the join because they are coding LINQ-2-SQL in a real SQL kind of way. Most of the times it is not needed (assuming the proper foreign keys are in place) because L2S will figure the join out for you.

To really answer your question you need to profile the actual SQL generated. Only then you will see the difference in the query that is actually sent to the database. Chances are it is identical. If not, select the most efficient of the two.

How to view the SQL: How to view LINQ Generated SQL statements?

The following code has the same result, but whichever is faster in bringing the result

1 Answers1