CODEWITHSUNDEEP
c#asp.net-mvclinqentity-framework
krypru
krypru

Reputation: 1742

Lazy loading or linq query?

As the topic says I wonder what is faster and better approach.

Linq and new method in repository like GetCourts(int clubId)

var courts =_context.Courts.Where(c=>c.ClubId==clubId)

Or lazy loading using EF

var courts= _clubRepository.GetClub(clubId).Courts;

My Club entity:

public class Club :EntityBase
    {
        public string Name { get; set; }
        public virtual IList<Court> Courts { get; set; }
    }

My Court entity:

public class Court:EntityBase
    {
       public bool IsIndoor { get; set; }

       public int ClubId { get; set; }
       public virtual Club Club { get; set; }

       public int CourtTypeId { get; set; }
       public virtual CourtType CourtType { get; set; }
    }

I don't knwo what approach to use in my project.

Upvotes: 6

Views: 8244

Answers (2)

Steve Ruble
Steve Ruble

Reputation: 3895

In the use case you describe in your question, using a LINQ query will be much faster than using lazy loading.

This statement:

var courts= _clubRepository.GetClub(clubId).Courts;

is equivalent to these statements:

var club = _clubRepository.GetClub(clubId);
var courts = club.Courts;

The first line causes a SQL query to be issued to get a single club with ClubId == clubId. The second line causes another SQL query to be issued to get all courts with ClubId == clubId. The fact that you are making two queries will overwhelm any potential benefits from the fact that lazy loading uses sp_executesql.

On the other hand,

var courts =_context.Courts.Where(c=>c.ClubId==clubId)

will result in only one SQL query being issued, which will be much more efficient.

Of course, if you already have an instance of Club in memory, you should use lazy loading to get its related entities, both for efficiency and to keep your object graph consistent.

Upvotes: 6

Yuliam Chandra
Yuliam Chandra

Reputation: 14640

Lazy loading will wrap the query in sp_executesql, while using normal linq query it will send the t-sql statement directly.

And msdn states that sp_executesql will reuse existing execution plan, that means if application sends subsequent query, it helps reducing the overhead.

Using sp_executesql can help reduce this overhead and still let SQL Server reuse execution plans. sp_executesql can be used instead of stored procedures when executing a Transact-SQL statement several times, when the only variation is in the parameter values supplied to the Transact-SQL statement. Because the Transact-SQL statements themselves remain constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution. - MSDN

Lazy loading is a feature which you can do and load the navigation when it's needed, if you use normal query, you end up having additional code to re-connect all objects.

Upvotes: 3

Related Questions