Entity Framework Designer First get navigation property as tasks

In my opinion, lazy loading (which I think will take place when enumerating a navigation property will result in a database access) is a bad access pattern, as it simply means that the database access will happen in surprising places, which is can make it difficult to predict applications.

All solutions below use import from System.Data.Entity

.

Solution 1 : use download withInclude

var course = await db.Courses.Include(c => c.Students).FirstOrDefaultAsync(c => c.ID == CourseID);
var student = course.Students.First(p => p.ID == StudentID);

      

        
        
        
      

    

Benefits:

• One database access required to load all objects, and this solution scales very well if you want to get more than one object Course
  
  at a time;
• The nav property is Students
  
  loaded and can be used freely;

Disadvantages:

• There is always at least one access to the database;
• The entire set of related objects is Student
  
  loaded, even if you only need one:

Solution 2 : use a method LoadAsync

that exists in a specific collection class;

This solution relies on the fact that the lazy-loaded collections are taken from the class EntityCollection<TEntity>

.

First I would define an extension method:

public static async Task LoadAsync<T>(ICollection<T> collection)
    where T : class
{
    if (collection == null) throw new ArgumentNullException("collection");

    var entityCollection = collection as System.Data.Entity.Core.Objects.DataClasses.EntityCollection<T>;

    if (entityCollection == null || entityCollection.IsLoaded) return;
    await entityCollection.LoadAsync(CancellationToken.None).ConfigureAwait(false);
}

      

        
        
        
      

    

Then you could write something like:

var course = await db.Courses.FindAsync(CourseID);
await course.Students.LoadAsync();
var student = course.Students.First(p => p.ID = StudentID);

      

        
        
        
      

    

Advantage:

• In fact, you may not be able to access the database if the objects are already loaded in the context;
• Loading the navigation property is guaranteed Students
  
  ;

Disadvantages:

• Susceptibility to "N + 1 requests" problem;
• Both Course
  
  , the set of related objects Student
  
  can grow black, which can cause concurrency problems along the way; (note that concurrency issues affecting relationships are more difficult to solve than concurrency issues affecting a single record).

Solution 3 : Use a CreateSourceQuery

class-specific method to load only the objects Student

you want.

OK, this doesn't work, and it's actually a pretty bad idea.

However, a solution with the same advantages / disadvantages can be written, but in a different way:

var course = await db.Courses.FindAsync(CourseID);
var studentsQuery = from c in db.Courses
                    where c.ID == CourseID
                    from s in c.Students
                    select s;
var student = await studentsQuery.FirstAsync(p => p.ID = StudentID);

      

        
        
        
      

    

Advantage:

• You only load one Student
  
  object that you intend to use;

Disadvantages:

• The navigation property is Students
  
  not loaded, which means it cannot be used without potentially triggering database access;
• The second line will always trigger database access (susceptible to "N + 1 queries" issue or even start method time);

Solution 4 : Loaded Loading, More Selective: Load both the course and the student you are interested in in the original LINQ query.

I'm not 100% sure that this solution will work as written.

var query = from c in db.Courses
            where c.ID == CourseID
            select new { course = c, student = c.Students.First(p => p.ID == StudentID) };

var result = await query.FirstOrDefaultAsync();
var course = result.course;
var student = result.student;

      

        
        
        
      

    

Benefits:

• Only one database access is required to retrieve both objects;
• You are only fetching the objects you will be working on,

Disadvantages:

• The navigation property is Students
  
  not loaded, which means it cannot be used without potentially triggering database access;

** When to use which solution? **

• If you need to populate the navigation property (either because you know you are using most of its elements, or because you want to pass the parent object to another component that is allowed to use that property it wants), then use solution 1 or 2;
• If you don't need to populate the navigation property, use solution 4. Only use solution 3 if you have an explicitly loaded object Course
  
  ;