C# Entity-Framework: How can I combine a .Find and .Include on a Model Object?
C#asp.net MvcEntity FrameworkC# Problem Overview
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);
C# Solutions
Solution 1 - C#
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);
Solution 2 - C#
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.
Solution 3 - C#
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
-
Find is fundamentally different because it obtains the the entity if it's present in the tracking before Querying the database.
-
Additionally It can filter by an Object so the user does not have to know the primary key.
-
This solution is for EntityFramework Core.
-
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);
Solution 4 - C#
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);
Solution 5 - C#
You have to cast IQueryable to DbSet
var dbSet = (DbSet<Item>) db.Set<Item>().Include("");
return dbSet.Find(id);