How can I implement multiple Include in Entity Framework?

Question

I use Entity framework 6. I have a Transaction object with several navigation properties. It is easy to implement eager loading using multiple Include.

 var aa = db.Transactions.Include(p => p.Account).Include(p => p.Instrument);

How can I implement the same if the fields to be included are parameters?

var aa = db.Transactions.IncludeMore(delegatesToBeIncluded);   

If delegatesToBeIncluded is null then there is nothing to be included.

https://mcmap.net/q/127769/-ef-linq-include-multiple-and-nested-entities This is similar what I want but it uses string instead of delegates.

https://mcmap.net/q/1963698/-pass-a-lambda-parameter-to-an-include-statement This is also interesting.

How to pass lambda 'include' with multiple levels in Entity Framework Core? This focuses on multiple level (I have one level)

https://mcmap.net/q/2036798/-entity-framework-multiple-include-at-runtime This is promising also.

Which direction should I go?

Revision 1: Why I need this? Based on the elements of aa new objects will be created. I realized that at each object creation EF reads the DB (lazy loading is used). It is just 50 ms, but it is repeated n times. This function is implemented in a template class, so Transactions is also a parameter.

Revision 2: In the full code there is filtering (pagination to be exact), and ToList() at then end. The tricky part that it is implemented in a template function. dbTableSelector is a delegate: readonly Func<MainDbContext, DbSet<TDbTable>> dbTableSelector;

 var myList = dbTableSelector(db).Where(WhereCondition).
             Skip(numberOfSkippedRows).Take(PageSize).OrderBy(OrderByCondition).ToList();

After that I transform each element of myList to another type of object. This is where lazy loading is activated one by one for each element. That is why I try to use Include. If dbTableSelector(db) returns Transactions I have to Include different elements when it returns let us say Instruments. So IncludeMore should have a List parameter which defines the fields to be included.

To

What are you trying to do? First of all, you aren't loading anything. aa is a queryable that hasn't executed yet. If the final query's Select touches on any related entities, they'll be loaded as well, even without Include. Second, since aa is a queryable, you can "append" Include or Where calls as needed, eg foreach(var exp in expressions){ aa=aa.Include(exp);} – Publius

The only reason you'd need to append Include calls dynamically would be if you were trying to create a dynamic query generator without actually knowing what the type is. Since you already know what the type is though, trying to "parameterize" the calls will end up more verbose than simply using the Include calls you need in each case. – Publius

That's not necessarily true. Back in March, I had to deal with a method which was called from a million places and the Include()s were the worst compromise you could ask for. It had to be the union of everything since the poor method could not be certain which linked entity might be used. – Hatchet

@PanagiotisKanavos see Revision 1 – To

This simply repeats what the question says so all the comments still apply. It isn't even accurate, precisely because aa is still an incomplete query. You need to either iterate over it or use ToList(), ToArray() etc to execute it. If you write a Select that needs the related entities, they'll be loaded. – Publius

@IstvanHeckl the only reason you'd need multiple Include() is if you tried to load all objects from the database without filtering. Which is extremely rare and typically an indicator of a bug - perhaps an attempt to force client-side evaluation? The answer shows that an IncludeMultiple is no better than actually writing the Include calls too – Publius

@IstvanHeckl finally, if you really want to load all Transactions and related entities, the easiest way would be to create a property or method in the DbContext that applied all Include()s and returned the IQueryable, eg public IQueryable<Transaction> TransactionsGraph=>Transactions.Include(p=>..).Include(p=>...); – Publius

@PanagiotisKanavos I added additional detail in Revision 2. – To

After that that's the problem. Why don't you transform the objects in the LINQ query, and avoid loading unwanted data? Have you tried and got errors complaining the expression can't be converted to SQL? That's your actual problem, not Include. Post the transformation code and the errors you got. There are always ways to bypass the problem, possibly by loading an intermediate form of just the data you need in the Select. Tools like AutoMapper can even work with EF queries directly to simplify mapping – Publius

BTW this is probably another case of the XY problem. You have a problem X (how to map data) and think the solution is Y (load everything in memory, transform it later). When that failed, you asked about Y, not the original problem, X. In this case though Y is a bad solution as it ends up loading far more data than necessary, wasting network and IO bandwidth and increasing blocking. – Publius

Answer 1

Here is the solution. It is based on this.

public static class IQueryableExtensions
{
    public static IQueryable<T> IncludeMultiple<T, TProperty>(this IQueryable<T> query,
        Expression<Func<T, TProperty>>[] includeDelegates) where T : class
    {
        foreach (var includeDelegate in includeDelegates)
            query = query.Include(includeDelegate);
        return query;
    }
}

This is the calling:

var pathsA = new Expression<Func<ViewTransaction, object>>[2] { p => p.Account, p => p.Instrument };
var pathsB = new Expression<Func<ViewTransaction, object>>[1] { p => p.Account};
var pathsC = Array.Empty<Expression<Func<ViewTransaction, object>>>();


var a = db.ViewTransactions.IncludeMultiple(pathsA).Single(e => e.Id == 100);

To

Not quite correct yet. To enable pass the includes expressions directly in the params, remove generic type param TProperty and change to object? in param expression type. Based on this: learn.microsoft.com/en-us/answers/questions/1315166/… – Acarid

Answer 2

Based on: https://learn.microsoft.com/en-us/answers/questions/1315166/parameterizing-theninclude()-in-entity-framework

public static class IQueryableExtensions
{
    public static IQueryable<T> IncludeMultiple<T>(this IQueryable<T> query,
        params Expression<Func<T, object?>>[] includes) where T : class
    {
        foreach (var include in includes)
        {
            query = query.Include(include);
        }
        return query;
    }
}

Usage:

var query = db.Entity.IncludeMultiple(n => n.NavProp1, n => n.NavProp2);

Acarid