Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity framework performance join vs navigation property

I am trying to understand why is join in my case faster than statement which use navigation property. I have two queries.

First with navigation property :

          var result = (from users in context.MetricBloodPreasure
                orderby users.User.LastName, users.User.FirstName
                select new
                {
                    UserName = users.User.LastName + ", " + users.User.FirstName,
                    Date = users.DateOfValue,
                }).ToList();

Generatet sql :

SELECT 
    [Project1].[C1] AS [C1], 
    [Project1].[C2] AS [C2], 
    [Project1].[DateOfValue] AS [DateOfValue]
    FROM ( SELECT 
        [Extent1].[DateOfValue] AS [DateOfValue], 
        [Extent2].[FirstName] AS [FirstName], 
        [Extent2].[LastName] AS [LastName], 
        1 AS [C1], 
        CASE WHEN ([Extent2].[LastName] IS NULL) THEN N'' ELSE [Extent2].[LastName] END + N', ' + CASE WHEN ([Extent2].[FirstName] IS NULL) THEN N'' ELSE [Extent2].[FirstName] END AS [C2]
        FROM  [dbo].[MetricBloodPreasure] AS [Extent1]
        INNER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[Id]
    )  AS [Project1]
    ORDER BY [Project1].[LastName] ASC, [Project1].[FirstName] ASC

Second with join:

var result1 = (from u in context.User
                orderby u.LastName, u.FirstName
                join us in context.MetricBloodPreasure
                    on u.Id equals us.UserId into users
                from s in users
                select new
                {
                    UserName = s.User.LastName + ", " + s.User.FirstName,
                    Date = s.DateOfValue,
                }).ToList();

Generated sql:

SELECT 
    1 AS [C1], 
    CASE WHEN ([Extent1].[LastName] IS NULL) THEN N'' ELSE [Extent1].[LastName] END + N', ' + CASE WHEN ([Extent1].[FirstName] IS NULL) THEN N'' ELSE [Extent1].[FirstName] END AS [C2], 
    [Extent2].[DateOfValue] AS [DateOfValue]
    FROM  [dbo].[User] AS [Extent1]
    INNER JOIN [dbo].[MetricBloodPreasure] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[UserId]) AND ([Extent2].[UserId] = [Extent1].[Id])

Before running first query, call var user = context.User.FirstOrDefault(); because I think open connection to database take some time.

Results : Navigation property query : 00:00:00.6719646 Join query : 00:00:00.4941169

Looking at results it seems that Linq queries that use joins instead of navigation properties are faster. Is that true or I'm doing something wrong ?

like image 547
puko Avatar asked Apr 24 '15 05:04

puko


People also ask

What is the use of navigation property in Entity Framework?

A navigation property is an optional property on an entity type that allows for navigation from one end of an association to the other end. Unlike other properties, navigation properties do not carry data.

How does Entity Framework affect the connection with the database?

Because an open connection to the database consumes a valuable resource, the Entity Framework opens and closes the database connection only as needed. You can also explicitly open the connection. For more information, see Managing Connections and Transactions. Once in each application domain.

Does Entity Framework cache data?

Entity Framework has the following forms of caching built-in: Object caching – the ObjectStateManager built into an ObjectContext instance keeps track in memory of the objects that have been retrieved using that instance. This is also known as first-level cache.

What is principal key?

Principal key: The properties that uniquely identify the principal entity. This may be the primary key or an alternate key. Foreign key: The properties in the dependent entity that are used to store the principal key values for the related entity.


1 Answers

To get a better insight into what it is doing you should get the raw SQL and you can check out the execution plan yourself.

To do this, you can either use SQL Profiler to see what query is being run, or you can log the SQL query itself by doing something like this before you run your query:

context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

Also doing a simple benchmark like you did by running each once isn't going to necessarily be reliable. You'll want to run it multiple times and average things out. You should also run them in the opposite order to see if that changes things as well.

like image 95
Jeff Treuting Avatar answered Sep 30 '22 15:09

Jeff Treuting