Lazy loading or linq query?

Question

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.

Lazy loading will be slower in your example because you're making two queries. — Steve Ruble
– Steve Ruble, Commented Aug 23, 2014 at 16:19

Steve Ruble · Accepted Answer · 2014-08-23 20:24:31Z

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.

Yuliam Chandra · Accepted Answer · 2014-08-23 16:32:32Z

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.

Collectives™ on Stack Overflow

Lazy loading or linq query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related