What is the best way to create a reusable function to return a value based on related data in an EF Core data model?

Question

I have a standard EF Core data model with several one-many and many-to-many relationships.

I want to create a way to produce various data calculations or commonly run procedures. These should return values to be used throughout the application and not values to be stored in the database.

I'll give a fictional example here of a scenario:

Entity 1 - YearGroup

Entity 2 - Student

Relationship - One YearGroup to many Students

Now I understand you could simply write in the controller:

int student = _context.Students.Where(s => s.YearGroupId == ygId).Count()

But let's say I wanted to simplify this by creating a method somewhere which returns data such as this, so I don't have to specify the query every time I get the number of Students in a YearGroup. I appreciate this is a simple example, but others could be more complex.

I was thinking adding a field to yeargroup.cs model such as:

public int TotalStudents { //code here to get students from students table }

Which I could then use like:

@model.YearGroup.TotalStudents

But I don't know how to get this to include relational data, i.e. from the students table.

I would prefer not create random methods in separate, unrelated classes such as GetStudentsInYearGroup(ygId). It would be nice to include this in the object model if possible.

What would be the best practice way of acheiving this?

Answer 1

Note: I don't have a code editor nor a project setup so what I am going to write might not be compilable.

As simple as your fictional example

For any case as simple as your fictional example, if you just want to get the total students per a year group, and I assume their relationships are properly setup, you can just simply use the navigation property:

// Find the target year group
var yearGroup = _context.YearGroups
    .SingleOrDefault(x => x.Id == ygId);

int totalStudents = 0;
if (yearGroup != null)
{
    totalStudents = yearGroup.Students.Count();
}

Extension methods

The other way I can think of is to define whatever you need as extension methods of the entity:

public static class YearGroupExtensions
{
    public static int GetTotalStudents(this YearGroup yearGroup)
    {
        if (yearGroup == null)
        {
            return 0;
        }

        return yearGroup.Students.Count();
    }

    public static int GetTotalStudents(this YearGroup yearGroup, Gender gender)
    {
        if (yearGroup == null)
        {
            return 0;
        }

        if (gender == null)
        {
            return yearGroup.GetTotalStudents();
        }

        return yearGroup
            .Students
            .Count(x => x.Gender == gender);
    }
}

// usage
// YearGroup yearGroup = GetYearGroup(ygId);
// int totalStudents = yearGroup.GetTotalStudents();

Repository Pattern

If you find yourself repeating similar methods you need for most of your entities, it might be good to define a generic repository for them.

I am not here arguing whether this is just a wrapper of DbContext as that itself is already using repository pattern.

public interface IEntity { }

public interface IRepository<T> where T : IEntity
{
    IEnumerable<T> GetAll();
    T GetById(int id);
    void Insert(T entity);
    ...
}

public abstract class RepositoryBase<T> : IRepository<T> where T : IEntity
{
    protected readonly AppDbContext _dbContext;

    protected DbSet<T> _entities;

    private RepositoryBase(AppDbContext dbContext)
    {
        _dbContext = dbContext;
        _entities = dbContext.Set<T>();
    }

    public virtual IEnumerable<T> GetAll()
    {
        return _entities.AsEnumerable();
    }

    ...
}

public class YearGroupRepository : RepositoryBase<YearGroup>
{
    ...
}

Separate Persistence and Domain Model

This is my preference just because I'm a DDD guy, and I want to build anything from the domain first (what businesss problems you're trying to solve), without thinking its backend persistence.

The basic idea here is to have 2 sets of models. They could be similar, or totally different. 1 set of models is called the domain model, which reflects your business domain. The other set of models is called the persistence model. That could be your regular Entity Framework Entities.

More on this: https://stackoverflow.com/a/14042539/2410655

Answer 2

Given a DbContext, an entity and a navigation property, you can construct an IQueryable as follows;

public static IQueryable AsQueryable(DbContext context, object entity, string navigation){
    var entry = context.Entry(entity);
    if (entry.State == EntityState.Detatched)
        return null;
    var nav = entry.Navigation(navigation);
    return nav.Query();
}

I feel like there should be an existing method for this, but I can't seem to find one right now.

You should then be able to use this method in a fairly generic way for any navigation property, without needing to replicate the foreign key criteria all over the place.

public int TotalStudents(DbContext context) =>
    AsQueryable(context, this, nameof(Students))?.Count() ?? 0;

While this would add some minor performance overhead, you could extract the base entity and navigation property from a LamdaExpression, and write some extension methods;

public class QueryableVisitor : ExpressionVisitor
{
    private object obj;
    public object BaseObject { get; private set; }
    public string Navigation { get; private set; }
            
    protected override Expression VisitConstant(ConstantExpression node)
    {
        BaseObject = obj = node.Value;
        return base.VisitConstant(node);
    }

    protected override Expression VisitMember(MemberExpression node)
    {
        Visit(node.Expression);
        BaseObject = obj;
        if (node.Member is PropertyInfo prop)
            obj = prop.GetValue(obj);
        else if (node.Member is FieldInfo field)
            obj = field.GetValue(obj);
        Navigation = node.Member.Name;
        return node;
    }
}

public static IQueryable<T> AsQueryable<T>(this DbContext context, Expression<Func<IEnumerable<T>>> expression)
{
    var visitor = new QueryableVisitor();
    visitor.Visit(expression);
    var query = AsQueryable(context, visitor.BaseObject, visitor.Navigation);
    return (IQueryable<T>)query;
}

public static int Count<T>(this DbContext context, Expression<Func<IEnumerable<T>>> expression) =>
    AsQueryable(context, expression)?.Count() ?? 0;

Enabling strongly typed usage like the following;

public int TotalStudents(DbContext context) =>
    context.Count(() => this.Students);

public int ActiveStudents(DbContext context) =>
    context.AsQueryable(() => this.Students)?.Where(s => s.Active).Count() ?? 0;