c# - Entity Framework Designer First get navigation property as Tasks

Question

The Task pattern says that in order to be consistent everything has to be completely async or completely not async.

By using entity framework designer first I can achieve this quite easily

var course = await db.Courses.FindAsync(CourseID);

Courses is a DbSet generated by entity framework and therefore has all the Async methods. The problem is that if I add a navigation property to that class, the latter is not a DbSet and it does not contain any async method definition.

As an example, if I add a navigation property to a Students table, it will be created as a virtual ICollection Students which means I cannot use the async methods. But what I do want is having entity framework to automatically generate a Task<> in order to be able to await even the navigation properties.

Is It possible? my goal is to achieve something like this:

var course = await db.Courses.FindAsync(CourseID);
var student = await course.Students.FindAsync(StudentID);

while at the moment my options are mixing async/notAsync code:

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

or not using the navigation property at all:

var course = await db.Courses.FindAsync(CourseID);
var student = await db.Students.Where(p => p.CourseID == course.ID && p.ID == StudentsID).FirstAsync();

Can you suggest a solution that do not require code first?

EDIT according to https://entityframework.codeplex.com/wikipage?title=Task-based%20Asynchronous%20Pattern%20support%20in%20EF.#AsyncLazyLoading what im searching for is called "Async Lazy Loading" and is not a feature available yet (and maybe it will never be). It seems that you can either use Lazy Loading OR the async features, maybe I should just wrap the property in a Task by awaiting Task.Run(course.Students.First(p => p.ID = StudentID)) but I'm not sure it is a good idea.

Answer 1

In my opinion, lazy loading (which would in my opinion be the case where enumerating the navigation property would trigger a database access) is a bad access pattern, as it simply means that database access will happen at surprising places, which can make application performance difficult to predict.

All the solutions below use an import from System.Data.Entity.

Solution 1: Use eager loading with an Include

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

Advantages:

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

Drawbacks:

• There is always at least one database access;
• The whole set of related Student objects is loaded even if you needed only one;

Solution 2: Use the LoadAsync method that exists on the concrete collection class;

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

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:

• There may be no database access at all if the objects are already loaded in the context;
• The navigation property Students is guaranteed to be loaded;

Drawbacks:

• Susceptible to the "N+1 queries" issue;
• Both the Course and the set of related Student objects can grow stale, which may trigger concurrency issues down the road; (note that concurrency issues that affect a relationship are harder to resolve than concurrency issues that affect a single record)

Solution 3: Use the CreateSourceQuery method on the concrete class to load only the Student object that you want.

OK, doing that does not work, and actually is a pretty bad idea.

However, a solution with the same advantages/drawbacks can be written, but in another 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 the one Student object that you are going to use;

Drawbacks:

• The Students navigation property is not loaded, meaning that it cannot be used without potentially triggering a database access;
• The second line will always trigger a database access (susceptible to the "N+1 queries" issue, or even running the method times);

Solution 4: Eager loading, more selective: load both the course and the student that interest you in the initial LINQ query.

I am not 100% sure that that 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;

Advantages:

• Only one database access is required to retrieve both objects;
• You only retrieve the objects that you are going to work on;

Drawbacks:

• The Students navigation property is not loaded, meaning that it cannot be used without potentially triggering a database access;

** When to use which solution? **

• If you need the navigation property to be filled (either because you know that you will make use of most of its elements, or because you want to pass the parent entity to another component that is allowed to make use of that property however it wants), then use solution 1 or 2;
• If you do not need the navigation property to be filled, then use solution 4. Only use solution 3 if you categorically have the Course object already loaded;