Difference using joins or navigation properties in EF

Question

I am learning EF and I am trying to understand when to use which type of query.

For example this query:

From fe In Processes.Where(Function(process) CBool(process.Type = 21 And 
                                            process.State = 0 And
                                            process.VDat > #2017/01/01# And
                                            process.ProcessAdresses.Any(Function(adr) adr.AdrNr = "0201") And
                                            process.ProcessPorisitions.Any(Function(pp) pp.ArtID = 200)))

Will return all processes of Type=21, State=0, Vdat>01.01.2017, which have a processAdress with AdrNr=0201 and a position with an ArtId=200. Here the navigation Properties are used and lazy loading has to be enabled. (I think, since the related data needs to get loaded, and it will when accessing the nav props)

Now I can express the same query using joins:

From fe In Processes.Where(
            Function(process) CBool(
                process.Art = 215 And
                process.State = 0 And
                process.VDat > #2017/01/01#))
     Join adr In ProcessAdresses.Where(
             Function(adr) adr.AdrNr = "020107")
         On fe.VID Equals vadr.VID
     Join pp In ProcessPorisitions.Where(
             Function(pp) pp.ArtID = 20004993)
         On fe.VID Equals vpos.VID
     Group  By fe Into Group
     Select fe

Here no nav props are used.

I was trying to measure/analyse performance using LinqPad but I only could see the difference in the created SQL Query, without effort...

So my questions are:

• What are the main differences of querying one way to querying the other way?
• Is there any rule to follow for when using one or the other?
• How can I mess performance in such cases or for SQL queries in general?

Answer 1

What are the main differences of querying one way to querying the other way?

One look at your code tells enough. Using navigation properties makes code easier to understand and better maintainable. Also, explicit joins are error prone. It's easy to join wrong ID properties (I've seen it happen). Finally, it causes repetitive code if you have more similar queries.

Is there any rule to follow for when using one or the other?

There are guidelines. DRY is one of them. See above.

How can I mess performance in such cases or for SQL queries in general?

This may be the only exception to the rule (guideline). Navigation properties to non-mandatory entities will be translated as outer joins. Outer joins may perform worse than inner joins. So when performance is critical and when null references should be filtered out of the end result you may resort to an explicit join statement, overriding the navigation property.

See also: Don't use Linq's Join. Navigate!.