Entity Framework: multiple queries vs inner joins

Question

I'm currently playing around with Entity Framework, and have a performance question regarding inner joins.

Which of the following 2 cases is the best based on performance in general?

For simplicity reasons of this example, let's say the there is a 1-to-1 relationship for brothers and sisters.

(1) Inner joins:

EFContext context = new EFContext();

int myId = GetMyOwnId();

Person me = context.People
                   .Include(p => p.Father)
                   .Include(p => p.Mother)
                   .Include(p => p.Brother)
                   .Include(p => p.Sister)
                   .Where(p => p.ID == myId);

return me;

(2) Optional multiple queries:

EFContext context = new EFContext();

int myId = GetMyOwnId();

Person me = context.People
                   .Where(p => p.ID == myId);

if (me.FatherId.HasValue) 
    me.Father = context.People.Find(me.FatherId);

if (me.MotherId.HasValue) 
    me.Mother = context.People.Find(me.MotherId);

if (me.BrotherId.HasValue) 
    me.Brother = context.People.Find(me.BrotherId);

if (me.SisterId.HasValue) 
    me.Sister = context.People.Find(me.SisterId);

return me;

So which case is the best for every situation?

Or is one better than the other for people without any family, or people with a father and a mother and a brother and a sister?

Vivien Sonntag · Accepted Answer · 2019-06-03 14:10:39Z

5

Option (1) should always be better, as the JOIN gets performed on the database level. As a rule of thumb you can say native database checks are faster than code checks and multiple roundtrips between database and code are always slower than a single one. As the database will implicitly do the check for an empty ID on a join anyways there is no need to do that in code and add possible additional roundtrips for that. Also the database will be able to automatically optimise the execution plan if it knows what is the intention of the query, this can provide another performance boost - especially if the constraints are set correctly and/or the query is executed frequently.

Maybe someone can add a benchmark for this to be 100% sure, but from a pure logical point this should be correct if nothing strange happens.

edited Jun 3, 2019 at 14:10

answered Jun 3, 2019 at 13:52

Vivien Sonntag

4,6591 gold badge29 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mason Over a year ago

That's what I was thinking, but wasn't that sure. Thanks for the explanation!

Collectives™ on Stack Overflow

Entity Framework: multiple queries vs inner joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related