2023-05-29

Dynamic sub Select using Expression with FromSqlRaw in .Net Core (v7) with cast to Entity EF (Ex: The required column not present of 'FromSql')

This is simplified example of what is needed.
Could this somehow be made to work?

Entity:

public class Entry
{
    public int EntryId { get; set; }
    public string Name { get; set; } = null!;
    public string? Description { get; set; }
}

DbContext:

public DbSet<Entry> Entries { get; set; } = null!;

Code:

public void RunTest(DbContext context)
{
    // direct linq Select two column into Entity
    var list = context.Entries.Select(a => new Entry { EntryId = a.EntryId, Name = a.Name }).ToList();
    // Generated Sql (seen in profiler): SELECT [e].[EntryId], [e].[Name] FROM [Entry] AS [e]

    var type = typeof(Entry);

    // dynamic Select with Expression (result should be same as direct above)

    // does NOT Work for subSelect
    // Exception: 'The required column 'Description' was not present in the results of a 'FromSql' operation.'
    var sqlQuery2 = "SELECT [EntryId], [Name] FROM [dbo].[Entry]";
    var selectProps2 = new List<string> { "EntryId", "Name" };
    var listExp2 = QueryTable(context, type, sqlQuery2, selectProps2).Cast<object>().ToList();
    // generated Sql: SELECT 1 FROM ( SELECT [EntryId], [Name] FROM [dbo].[Entry] ) AS [e]

    // but works if all columns are selected
    var sqlQueryAll = "SELECT [EntryId], [Name], [Description] FROM [dbo].[Entry]";
    var selectPropsAll = new List<string> { "EntryId", "Name", "Description" };
    var listExpAll = QueryTable(context, type, sqlQueryAll, selectPropsAll).Cast<object>().ToList();
}

Helper methods:

protected IEnumerable QueryTable(DbContext context, System.Type entityType, string sqlQuery, List<string> selectProps)
{
    var parameter = Expression.Parameter(typeof(DbContext));
    var expression = Expression.Call(parameter, "Set", new System.Type[] { entityType });

    expression = Expression.Call(typeof(RelationalQueryableExtensions), "FromSqlRaw", new System.Type[] { entityType }, expression,
                                 Expression.Constant(sqlQuery), Expression.Constant(Array.Empty<object>()));

    expression = Select(entityType, expression, selectProps);

    var expressionResult = Expression.Lambda<Func<DbContext, IEnumerable>>(expression, parameter);

    var compiled = EF.CompileQuery(expressionResult);
    var result = compiled(context);
    return result;
}

protected static MethodCallExpression Select(System.Type entityType, Expression source, List<string> selectProps)
{
    Dictionary<string, PropertyInfo> sourceProperties = selectProps.ToDictionary(name => name, name => entityType.GetProperty(name)!);
    var dynamicType = entityType;

    var expression = (MethodCallExpression)source;
    ParameterExpression parameter = Expression.Parameter(entityType);

    IEnumerable<MemberBinding> bindings = dynamicType.GetFields().Select(p =>
        Expression.Bind(p, Expression.Property(parameter, sourceProperties[p.Name]))).OfType<MemberBinding>();

    var constrType = dynamicType.GetConstructor(new System.Type[] { entityType });
    if (constrType != null)
    {
        var constrTypeExp = Expression.New(constrType);
        Expression selector = Expression.Lambda(Expression.MemberInit(constrTypeExp, bindings), parameter);

        var typeArgs = new System.Type[] { entityType, dynamicType };
        expression = Expression.Call(typeof(Queryable), "Select", typeArgs, expression, selector);
    }
    return expression;
}

One more remark, following code (when expression not used) works fine:

var listRaw = context.Set<Entry>().FromSqlRaw(sqlQuery2).Select(a => new Entry { EntryId = a.EntryId, Name = a.Name }).ToList();

UPDATE
Have found a way to get dynamic List without Cast to entity but that is not the solution to the issue of asked Question.
v1 - select only one Property
v2 - Select more then one
Still is somebody would have that usecase code sample is in post below.

PS
If method QueryTable does not have to be dynamic then Select Expressions can be build on top of IQueryable like here: LINQ : Dynamic select Sample:

var set = context.Set<Entry>();
var selectExp = Helpers.DynamicSelectGenerator<Entry>("EntryId, Name");

// works but loads all columns into memory and for efficiency not what is needed
var result = set.AsQueryable().Select(selectExp).ToList();

// throws the same error
var resultRaw = set.FromSqlRaw(sqlQuery2).Select(selectExp).ToList();


No comments:

Post a Comment