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

Question

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();

score 0 · Answer 1 · edited Jun 12 '23 at 15:22

Note this is not the main answer, just additional way of doing it.
Dynamic Select but without Cast to Entity.

v1 - Select only one Property

private static MethodCallExpression SelectProp(System.Type entityType, Expression source, string selectProp)
{
    var expression = (MethodCallExpression)source;
    ParameterExpression parameter = Expression.Parameter(entityType);
    PropertyInfo? property = entityType.GetProperty(selectProp);
    if (property != null)
    {
        MemberExpression propertyAccess = Expression.MakeMemberAccess(parameter, property);
        LambdaExpression selectExp = Expression.Lambda(propertyAccess, parameter);
        string methodName = "Select";
        var typeArgs = new System.Type[] { entityType, property.PropertyType };
        expression = Expression.Call(typeof(Queryable), methodName, typeArgs, expression, Expression.Quote(selectExp));
    }
    return expression;
}

v2 - Select more Properties (used LinqRuntimeTypeBuilder)

// gives dynamic Object but with Fields and not Properties
protected static MethodCallExpression SelectDynamic(System.Type entityType, Expression source, List<string> selectProps)
{
    Dictionary<string, PropertyInfo> sourceProperties = selectProps.ToDictionary(name => name, name => entityType.GetProperty(name)!);
    System.Type dynamicType = LinqRuntimeTypeBuilder.GetDynamicType(sourceProperties.Values) ?? typeof(string);

    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(System.Type.EmptyTypes);
    var constrTypeExp = Expression.New(dynamicType.GetConstructor(System.Type.EmptyTypes)!);
    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;
}

LinqRuntimeTypeBuilder (from expression-anonymous-type):

/// <summary>
/// Expressions
/// </summary>
public static class LinqRuntimeTypeBuilder
{
    private static AssemblyName assemblyName = new AssemblyName() { Name = "DynamicLinqTypes" };
    private static ModuleBuilder? moduleBuilder = null;
    private static Dictionary<string, System.Type> builtTypes = new Dictionary<string, System.Type>();

    static LinqRuntimeTypeBuilder()
    {
        moduleBuilder = AssemblyBuilder.DefineDynamicAssembly(assemblyName, AssemblyBuilderAccess.Run).DefineDynamicModule(assemblyName.Name);
    }

    private static string GetTypeKey(Dictionary<string, System.Type> fields)
    {string key = string.Empty;
        foreach (var field in fields)
            key += field.Key + ";" + field.Value.Name + ";";

        return key;
    }

    /// <summary>
    /// GetDynamicType
    /// </summary>
    public static System.Type GetDynamicType(Dictionary<string, System.Type> fields)
    {
        if (null == fields)
            throw new ArgumentNullException("fields");
        if (0 == fields.Count)
            throw new ArgumentOutOfRangeException("fields", "fields must have at least 1 field definition");

        try
        {
            Monitor.Enter(builtTypes);
            string className = GetTypeKey(fields);

            if (builtTypes.ContainsKey(className))
                return builtTypes[className];

            if (moduleBuilder != null)
            {
                TypeBuilder typeBuilder = moduleBuilder.DefineType(className, TypeAttributes.Public | TypeAttributes.Class | TypeAttributes.Serializable);

                foreach (var field in fields)
                    typeBuilder.DefineField(field.Key, field.Value, FieldAttributes.Public);

                if (typeBuilder != null)
                {
                    builtTypes[className] = typeBuilder.CreateType() ?? typeof(string); // typeof(string) as fix for warning null ref.asign
                }

                return builtTypes[className];
            }
        }
        finally
        {
            Monitor.Exit(builtTypes);
        }

        return typeof(string);
    }


    private static string GetTypeKey(IEnumerable<PropertyInfo> fields)
    {
        return GetTypeKey(fields.ToDictionary(f => f.Name, f => f.PropertyType));
    }

    /// <summary>
    /// GetDynamicType
    /// </summary>
    public static System.Type GetDynamicType(IEnumerable<PropertyInfo> fields)
    {
        return GetDynamicType(fields.ToDictionary(f => f.Name, f => f.PropertyType));
    }
}

Another useful link for Expressions: LinqSample

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

1 Answers1