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.

Answer 1

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.