C# Entity-Framework: How can I combine a .Find and .Include on a Model Object?

Question

I'm doing the mvcmusicstore practice tutorial. I noticed something when creating the scaffold for the album manager (add delete edit).

I want to write code elegantly, so i'm looking for the clean way to write this.

FYI i'm making the store more generic:

Albums = Items

Genres = Categories

Artist = Brand

Here is how the index is retrieved (generated by MVC):

var items = db.Items.Include(i => i.Category).Include(i => i.Brand);

Here is how the item for delete is retrieved:

Item item = db.Items.Find(id);

The first one brings back all the items and populates the category and brand models inside the item model. The second one, doesn't populate the category and brand.

How can i write the second one to do the find AND populate whats inside (preferably in 1 line)... theoretically - something like:

Item item = db.Items.Find(id).Include(i => i.Category).Include(i => i.Brand);

Answer 1

You can use Include() first, then retrieve a single object from the resulting query:

Item item = db.Items
              .Include(i => i.Category)
              .Include(i => i.Brand)
              .FirstOrDefault(x => x.ItemId == id);

Answer 2

Dennis' answer is using Include and SingleOrDefault. The latter goes round-tripping to database.

An alternative, is to use Find, in combination with Load, for explicit loading of related entities...

Below an MSDN example:

using (var context = new BloggingContext()) 
{ 
  var post = context.Posts.Find(2); 

  // Load the blog related to a given post 
  context.Entry(post).Reference(p => p.Blog).Load(); 

  // Load the blog related to a given post using a string  
  context.Entry(post).Reference("Blog").Load(); 

  var blog = context.Blogs.Find(1); 

  // Load the posts related to a given blog 
  context.Entry(blog).Collection(p => p.Posts).Load(); 

  // Load the posts related to a given blog  
  // using a string to specify the relationship 
  context.Entry(blog).Collection("Posts").Load(); 
}

Of course, Find returns immediately without making a request to the store, if that entity is already loaded by the context.

Answer 3

There's no real easy way to filter with a find. But I've come up with a close way to replicate the functionality but please take note of a few things for my solution.

This Solutions allows you to filter generically without knowning the primary key in .net-core

1. Find is fundamentally different because it obtains the the entity if it's present in the tracking before Querying the database.

2. Additionally It can filter by an Object so the user does not have to know the primary key.

3. This solution is for EntityFramework Core.

4. This requires access to the context

Here are some extension methods to add which will help you filter by primary key so

    public static IReadOnlyList<IProperty> GetPrimaryKeyProperties<T>(this DbContext dbContext)
    {
        return dbContext.Model.FindEntityType(typeof(T)).FindPrimaryKey().Properties;
    }

    //TODO Precompile expression so this doesn't happen everytime
    public static Expression<Func<T, bool>> FilterByPrimaryKeyPredicate<T>(this DbContext dbContext, object[] id)
    {
        var keyProperties = dbContext.GetPrimaryKeyProperties<T>();
        var parameter = Expression.Parameter(typeof(T), "e");
        var body = keyProperties
            // e => e.PK[i] == id[i]
            .Select((p, i) => Expression.Equal(
                Expression.Property(parameter, p.Name),
                Expression.Convert(
                    Expression.PropertyOrField(Expression.Constant(new { id = id[i] }), "id"),
                    p.ClrType)))
            .Aggregate(Expression.AndAlso);
        return Expression.Lambda<Func<T, bool>>(body, parameter);
    }

    public static Expression<Func<T, object[]>> GetPrimaryKeyExpression<T>(this DbContext context)
    {
        var keyProperties = context.GetPrimaryKeyProperties<T>();
        var parameter = Expression.Parameter(typeof(T), "e");
        var keyPropertyAccessExpression = keyProperties.Select((p, i) => Expression.Convert(Expression.Property(parameter, p.Name), typeof(object))).ToArray();
        var selectPrimaryKeyExpressionBody = Expression.NewArrayInit(typeof(object), keyPropertyAccessExpression);

        return Expression.Lambda<Func<T, object[]>>(selectPrimaryKeyExpressionBody, parameter);
    }

    public static IQueryable<TEntity> FilterByPrimaryKey<TEntity>(this DbSet<TEntity> dbSet, DbContext context, object[] id)
        where TEntity : class
    {
        return FilterByPrimaryKey(dbSet.AsQueryable(), context, id);
    }

    public static IQueryable<TEntity> FilterByPrimaryKey<TEntity>(this IQueryable<TEntity> queryable, DbContext context, object[] id)
        where TEntity : class
    {
        return queryable.Where(context.FilterByPrimaryKeyPredicate<TEntity>(id));
    }

Once you have these extension methods you can filter like so:

query.FilterByPrimaryKey(this._context, id);

Answer 4

Didnt work for me. But I solved it by doing like this.

var item = db.Items
             .Include(i => i.Category)
             .Include(i => i.Brand)
             .Where(x => x.ItemId == id)
             .First();

Dont know if thats a ok solution. But the other one Dennis gave gave me a bool error in .SingleOrDefault(x => x.ItemId = id);

Answer 5

In this scenario you must use DbSet<T>.Local.

You cannot combine DbSet<T>.Find(object[] params) to do what you want because it will query the database if the entity is not currently attached and tracked by the context.

Implementations of DbSet<T>.SingleOrDefault<T>, DbSet<T>.FirstOrDefault<T> and related methods will also query the database immediately upon invocation.

Assuming you have type MyEntity with property Id returning int you could create a method like the following, or adapt it to meet your specific need.

public MyEntity FindLocalOrRemote(int id)
{
    MyEntity entity = 

        context.MyEntities
               .Local
               .SingleOrDefault(p => p.Id == id) 

        ?? 

        context.MyEntities
               .Include(p => p.PackItems)
               .SingleOrDefault(p => p.PackId == id);
        
    return entity;
}

A drawback of this approach, and quite possibly why there is no built-in method for this, might be due to the challenge of designing an API around key values or because using DbSet<T>.Local there is no guarantee that the attached and tracked entity has the related navigation property populated from the database.

This question is really old, but not a single person gave either a simple or correct answer to the question.

This would work with Entity Framework 6 or Entity Framework Core.

Answer 6

You have to cast IQueryable to DbSet

var dbSet = (DbSet<Item>) db.Set<Item>().Include("");

return dbSet.Find(id);