Entity Framework Core Find all by primary key (C#)
Introduction
Entity Framework Core has a method DbSet<TEntity>.Find(Object[]) used to find a entity by primary key but does not have a method to find multiple keys. This article presents a language extension method which provides this missing functionality. The code sample provides is done in a Windows form project but can be used in any project type from desktop to web solutions.
Implementation
Add the following class to the project with a DbContext and models. Change the namespace to match the namespace of the current project this class is being added too.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
namespace SampleLibrary.Extensions
{
public static class DbContextExtensions
{
private static readonly MethodInfo ContainsMethod = typeof(Enumerable).GetMethods()
.FirstOrDefault(mi => mi.Name == "Contains" && mi.GetParameters().Length == 2)
.MakeGenericMethod(typeof(object));
/// <summary>
/// Find by primary key
/// </summary>
/// <typeparam name="T">Model to find against</typeparam>
/// <param name="dbContext">Valid DbContext for model</param>
/// <param name="keyValues">primary key values</param>
/// <returns>Array of T for matching records from keyValues</returns>
public static Task<T[]> FindAllAsync<T>(this DbContext dbContext, params object[] keyValues) where T : class
{
var entityType = dbContext.Model.FindEntityType(typeof(T));
var primaryKey = entityType.FindPrimaryKey();
if (primaryKey.Properties.Count != 1)
throw new NotSupportedException("Only a single primary key is supported");
var pkProperty = primaryKey.Properties[0];
var pkPropertyType = pkProperty.ClrType;
// validate passed key values
foreach (var keyValue in keyValues)
{
if (!pkPropertyType.IsInstanceOfType(keyValue))
throw new ArgumentException($"Key value '{keyValue}' is not of the right type");
}
// retrieve member info for primary key
var pkMemberInfo = typeof(T).GetProperty(pkProperty.Name);
if (pkMemberInfo == null)
throw new ArgumentException("Type does not contain the primary key as an accessible property");
// build lambda expression
var parameter = Expression.Parameter(typeof(T), "e");
var body = Expression.Call(null, ContainsMethod,
Expression.Constant(keyValues),
Expression.Convert(Expression.MakeMemberAccess(parameter, pkMemberInfo), typeof(object)));
var predicateExpression = Expression.Lambda<Func<T, bool>>(body, parameter);
return dbContext.Set<T>().Where(predicateExpression).ToArrayAsync();
}
}
}
Usage
To find multiple records with specific identifiers pass the identifiers as an object array to FindAllAsync against a dbContext. In the following mocked example a category table is read into a CheckedListBox. Select more than one (one is okay to but this is about multiples), get the primary keys for the selected categories, convert the int array to an object array and pass the array to FindAllAsync which returns an array of the type, in this Categories. Suppose this was for Orders instead of Categories e.g.
Orders[] categories = await context.FindAllAsync<Orders>(
Array.ConvertAll(indices, id => (object)id));
In this code sample we are dealing with Categories
Categories[] categories = await context.FindAllAsync<Categories>(
Array.ConvertAll(indices, id => (object)id));
Example
In this example the model is Categories which all categories are read into a CheckedListBox in form Shown event.
private async void MainForm_Shown(object sender, EventArgs e)
{
using (var context = new NorthWindContext())
{
await Task.Run(async () =>
{
var categories = await context.Categories.AsNoTracking().ToListAsync();
CategoryCheckedListBox.InvokeIfRequired(clb => clb.DataSource = categories);
});
}
}
using a button click event checked categories are obtained using the following language extension method. The extension method returns a primary key for each selected category checked in the CheckedListBox as an array of int. Since FindAllAsync expects an array of object the following code converts the int array to an object array.
Array.ConvertAll(indices, id => (object)id)
Once FindAllAsync has completed in this case an array of Categories is returned which for demo purposes is iterated for products associated with each category.
Complete button click event.
private async void SelectedButton_Click(object sender, EventArgs e)
{
ResultsTextBox.Text = "";
var sb = new StringBuilder();
/*
* Get all checked categories primary key in the checked list box
*/
var indices = CategoryCheckedListBox.SelectedCategoryIdentifier();
if (indices.Length <= 0) return;
using (var context = new NorthWindContext())
{
/*
* Get all products for each category, not FindAllAsync expects an
* object array not an int array so they are converted via
* Array.ConvertAll.
*/
Categories[] categories = await context.FindAllAsync<Categories>(
Array.ConvertAll(indices, id => (object)id));
/*
* Display in a text box
*/
foreach (Categories category in categories)
{
sb.AppendLine(category.CategoryName);
var products = context.Products
.AsNoTracking().Where(prod => prod.CategoryId == category.CategoryId)
.ToList();
foreach (Products product in products)
{
sb.AppendLine($" {product.ProductId,-4}{product.ProductName}");
}
sb.AppendLine();
}
ResultsTextBox.Text = sb.ToString();
}
}
Summary
This article has presented a way to use a language extension method against a DbContext and generic type/model to find multiple records in Entity Framework Core.
See also
Entity Framework Core/Windows Forms tips and tricks
Entity Framework Core shadow properties (C#)
Setup code sample
First run the data script to create the sample database following by right clicking on solution explorer and selecting restore packages. Next build the solution and run.
Source code
Clone or download the following repository.