Performance using joins vs navigation properties RRS feed

  • Question

  • In most cases I would use navigational properties to slect/get data from related tables.

    For example:  (from o in db.Order where d.Customer.Id == 123 && d.Customer.Category == 1  select d)

    However I have noticed that sometimes you get a very inefficient sql. Which I just got with a structure that looks something liek this:

    A -> B - > C <- D

    I would run my query based on A and doing some selections, getting data from the other tables. I would get more than 1 row from D but qualifying it with a selection in where statement so only 1 row returned.

    When I do queries that I suspect might be inefficient I check with SQL profiler and noticed it did do something like 13000 reads (casuing some full table scans) when only returning 4 rows. So what I tried next was to use join (like I would do it in sql) in my linq statement instead and it was only doing 360 reads and performing much faster and much cleaner sql.

    Is there any good rule of thumb to follow when it is better to use joins than navigation properties? In most cases it doesn't matter and even if the query is not optimal it is good enough.

    Thursday, April 25, 2013 6:59 PM


  • Hi magnusb999;

    It is always a good idea to look at the SQL query that was created by Linq to EF or even Linq to SQL for that matter because the query that it comes up with is NOT guaranteed to be the most efficient query. Sometimes self brewed queries will perform much better and sometimes not. So it is a good habit to see what it came up with and see if you can do better.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Proposed as answer by Chester Hong Tuesday, April 30, 2013 9:00 AM
    • Marked as answer by Chester Hong Tuesday, May 7, 2013 9:11 AM
    Thursday, April 25, 2013 8:18 PM