CODEWITHSUNDEEP
c#.net-coreentity-framework-core
Guerrilla
Guerrilla

Reputation: 14866

EF Core 2.0 include nested entities with dynamic query

I am using System.Linq.Dynamic.Core; and the following extension method to access a DbSet dynamically by name and generate queries from strings.

This is extesion method:

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomExtensions
    {
        public static IQueryable Query(this DbContext context, string entityName) =>
            context.Query(context.Model.FindEntityType(entityName).ClrType);

        public static IQueryable Query(this DbContext context, Type entityType) =>
            (IQueryable)((IDbSetCache)context).GetOrAddSet(context.GetDependencies().SetSource, entityType);
    }
}

This is how I access the DbSet:

IQueryable<T> dbSet = (IQueryable<T>)_db.Query(entityName);

This works fine, I can build a query and then create a list but none of the nested entities load. It seems IQueryable doesn't have a definition for Include(). I can see include method if I access db context directly in normal fashion but not using this dynamic method.

How do I include nested entities using dynamic method?

Upvotes: 2

Views: 7381

Answers (2)

Ramil Aliyev 007
Ramil Aliyev 007

Reputation: 5442

I have a piece code for this problem. I use Expression tree.

My entity model like as below:

public class PortfolioTechnology
{
    public int PortfolioId { get; set; }
    public Portfolio Portfolio { get; set; }

    public int TechnologyId { get; set; }
    public Technology Technology { get; set; }
}

My main code like as below:

public SharpListResponse<PortfolioTechnology> GetAll(
    Expression<Func<PortfolioTechnology, bool>> predicate,
    params Expression<Func<PortfolioTechnology,object>>[] includes)
{
    var query = _dbContext.PortfolioTechnology.AsQueryable();

    foreach (var include in includes)
    {
        var memberExpression = include.Body as MemberExpression;

        if (memberExpression != null)
            query = query.Include(memberExpression.Member.Name);
    }

    var result = query.Where(predicate).ToList();

    return new SharpListResponse<PortfolioTechnology>(result);
}

And usage this method like as below:

var list = _unitOfWork.PortfolioTechnologyRepository.GetAll(x => x.PortfolioId == id, 
                                                            y => y.Technology);

If you want include multiple entities for example include Portfolio and Technology entities, your code like as below:

 var list = _unitOfWork.PortfolioTechnologyRepository.GetAll(x => x.PortfolioId == id, 
                                                             y => y.Technology,
                                                             x => x.Portfolio);

Note: SharpListResponse is wrapper class. Code is working without it

More details for SharpListResponse : https://www.nuget.org/packages/SharpRequestResponseWrapper/

Upvotes: 9

programtreasures
programtreasures

Reputation: 4298

First of all You are using GetOrAddSet of IDbSetCache

public interface IDbSetCache

// // Summary: // This API supports the Entity Framework Core infrastructure and is not intended // to be used directly from your code. This API may change or be removed in future

Include is method of IQueryable<TEntity> in class EntityFrameworkQueryableExtensions of Microsoft.EntityFrameworkCore, not for IQueryable. Your function returns IQueryable

I would recommend you to create extention method like below,

        public static IQueryable<T> MyQuery<T>(this DbContext context)
            where T : class
        {
            return context.Set<T>().AsQueryable();
        }

And you can consume by,

  var result = _dbContext.MyQuery<Employee>().Include("Department");

For Dynamic include,

    /// <summary>
    /// Query with dynamic Include
    /// </summary>
    /// <typeparam name="T">Entity</typeparam>
    /// <param name="context">dbContext</param>
    /// <param name="includeProperties">includeProperties with ; delimiters</param>
    /// <returns>Constructed query with include properties</returns>
    public static IQueryable<T> MyQueryWithDynamicInclude<T>(this DbContext context, string includeProperties)
       where T : class
    {            
        string[] includes = includeProperties.Split(';');
        var query = context.Set<T>().AsQueryable();

        foreach (string include in includes)
            query = query.Include(include);

        return query;
    }

Upvotes: 12

Related Questions