C# EF6 conditional property selection?

Question

Suppose I have code-first model:

public class FooBar
{
    [Key]
    public int Id {get;set;}
    [MaxLength(254)]
    public string Title {get;set;}
    public string Description {get;set;}
}

And method to retrieve some subsets of data of rows:

public IQueryable<FooBar> GetDataQuery(bool includeTitle, bool includeDescription)
{
    var query = ctx.FooBars.AsNoTracking().Where(Id > 123);
    //how to inlcude/exclude???
    return query;
}

The question is how to build query with specific fields without hardcoding anonymous types? Basically, I want to tell SQL query builder to build query with specified fields, without post filtering that on client. So if I exclude Description - it will not be sent over wire.

Also, had experience like this:

public IQueryable<FooBar> GetDataQuery(bool includeTitle, bool includeDescription)
{
    var query = ctx.FooBars.AsNoTracking().Where(Id > 123);
    query = query.Select(x=> new
    {  
         Id = x.Id
         Title = includeTitle ? x.Title : null,
         Description = includeDescription ? x.Description : null,
    })
    .MapBackToFooBarsSomehow();//this will fail, I know, do not want to write boilerplate to hack this out, just imagine return type will be correctly retrieved
    return query;
}

But this will send over wire includeTitle, includeDescription properties as SQL parameters for EXEC and query will be inefficient in most cases compared to simple non-conditional anonymous query without this clutter - but writing every possible permutation of anonymous structure is not an option.

PS: in reality there is big list of "include/exclude" properties, I just presented two for simplicity.

UPDATE:

Inspired by @reckface answer, I wrote extension for those who want to achieve fluent-like execution and mapping to entity at the end of their query:

public static class CustomSqlMapperExtension
{
    public sealed class SpecBatch<T>
    {
        internal readonly List<Expression<Func<T, object>>> Items = new List<Expression<Func<T, object>>>();

        internal SpecBatch()
        {
        }

        public SpecBatch<T> Property(Expression<Func<T, object>> selector, bool include = true)
        {
            if (include)
            {
                Items.Add(selector);
            }
            return this;
        }
    }

    public static List<T> WithCustom<T>(this IQueryable<T> source, Action<SpecBatch<T>> configurator)
    {
        if (source == null)
            return null;

        var batch = new SpecBatch<T>();
        configurator(batch);
        if (!batch.Items.Any())
            throw new ArgumentException("Nothing selected from query properties", nameof(configurator));

        LambdaExpression lambda = CreateSelector(batch);
        var rawQuery = source.Provider.CreateQuery(
            Expression.Call(
                typeof(Queryable),
                nameof(Queryable.Select),
                new[]
                {
                    source.ElementType,
                    lambda.Body.Type
                }, 
                source.Expression, 
                Expression.Quote(lambda))
        );
        return rawQuery.ToListAsync().Result.ForceCast<T>().ToList();
    }

    private static IEnumerable<T> ForceCast<T>(this IEnumerable<object> enumer)
    {
        return enumer.Select(x=> Activator.CreateInstance(typeof(T)).ShallowAssign(x)).Cast<T>();
    }

    private static object ShallowAssign(this object target, object source)
    {
        if (target == null || source == null)
            throw new ArgumentNullException();
        var type = target.GetType();
        var data = source.GetType().GetProperties()
            .Select(e => new
            {
                e.Name,
                Value = e.GetValue(source)
            });
        foreach (var property in data)
        {
            type.GetProperty(property.Name).SetValue(target, property.Value);
        }
        return target;
    }

    private static LambdaExpression CreateSelector<T>(SpecBatch<T> batch)
    {
        var input = "new(" + string.Join(", ", batch.Items.Select(GetMemberName<T>)) + ")";
        return System.Linq.Dynamic.DynamicExpression.ParseLambda(typeof(T), null, input);
    }

    private static string GetMemberName<T>(Expression<Func<T, object>> expr)
    {
        var body = expr.Body;
        if (body.NodeType == ExpressionType.Convert)
        {
            body = ((UnaryExpression) body).Operand;
        }
        var memberExpr = body as MemberExpression;
        var propInfo = memberExpr.Member as PropertyInfo;
        return propInfo.Name;
    }
}

Usage:

public class Topic
{
    public long Id { get; set; }

    public string Title { get; set; }

    public string Body { get; set; }

    public string Author { get; set; }

    public byte[] Logo { get; set; }

    public bool IsDeleted { get; set; }
}
public class MyContext : DbContext
{
    public DbSet<Topic> Topics { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        using (var ctx = new MyContext())
        {
            ctx.Database.Log = Console.WriteLine;

            var query = (ctx.Topics ?? Enumerable.Empty<Topic>()).AsQueryable();
            query = query.Where(x => x.Title != null);
            var result = query.WithCustom(
                cfg => cfg                         //include whitelist config
                    .Property(x => x.Author, true) //include
                    .Property(x => x.Title, false) //exclude
                    .Property(x=> x.Id, true));    //include

        }
    }
}

Important to mention that those entities can't be used in EF, until you explicitly attach them.

Answer 1

I used System.Linq.Dynamic for this very successfully. You can pass a string as the select statement in the following format: .Select("new(Title, Description)")

So your example would become:

// ensure you import the System.Linq.Dynamic namespace
public IQueryable<FooBar> GetDataQuery(bool includeTitle, bool includeDescription)
{
    // build a list of columns, at least 1 must be selected, so maybe include an Id
    var columns = new List<string>(){nameof(FooBar.Id)};        
    if (includeTitle)
        columns.Add(nameof(FooBar.Title));
    if (includeDescription)
        columns.Add(nameof(FooBar.Description));
    // join said columns
    var select = $"new({string.Join(", ", columns)})";
    var query = ctx.FooBars.AsQueryable()
        .Where(f => f.Id > 240)
        .Select(select)
        .OfType<FooBar>();
    return query;
}

EDIT

Turns out OfType() may not work here. If that's the case, here's a poor man's extension method:

// not ideal, but it fits your constraints
var query = ctx.FooBars.AsQueryable()
            .Where(f => f.Id > 240)
            .Select(select)
            .ToListAsync().Result
            .Select(r => new FooBar().Fill(r));

public static T Fill<T>(this T item, object element)
{
    var type = typeof(T);
    var data = element.GetType().GetProperties()
        .Select(e => new
        {
            e.Name,
            Value = e.GetValue(element)
        });
    foreach (var property in data)
    {
        type.GetProperty(property.Name).SetValue(item, property.Value);
    }
    return item;
}

Update

But wait there's more!

var query = ctx.FooBars
    .Where(f => f.Id > 240)
    .Select(select)
    .ToJson() // using Newtonsoft.JSON, I know, I know, awful. 
    .FromJson<IEnumerable<FooBar>>()
    .AsQueryable(); // this is no longer valid or necessary
return query;

public static T FromJson<T>(this string json)
{
    var serializer = new JsonSerializer();
    using (var sr = new StringReader(json))
    using (var jr = new JsonTextReader(sr))
    {
        var result = serializer.Deserialize<T>(jr);
        return result;
    }
}

public static string ToJson(this object data)
{
    if (data == null)
        return null;
    var json = JsonConvert.SerializeObject(data, Newtonsoft.Json.Formatting.Indented);
    return json;
}

Results

Answer 2

As far as I know, there is no clean way to do that in EF. You can use some workarounds of various ugliness, below is one. It will work only if you are not going to update\attach\delete returned entities, which I assume is fine for this use case.

Suppose we want to include only properties "ID" and "Code". We need to construct expression of this form:

fooBarsQuery.Select(x => new FooBar {ID = x.ID, Code = x.Code))

We can do that manually like this:

public static IQueryable<T> IncludeOnly<T>(this IQueryable<T> query, params string[] properties) {
    var arg = Expression.Parameter(typeof(T), "x");
    var bindings = new List<MemberBinding>();

    foreach (var propName in properties) {
        var prop = typeof(T).GetProperty(propName);
        bindings.Add(Expression.Bind(prop, Expression.Property(arg, prop)));
    }
    // our select, x => new T {Prop1 = x.Prop1, Prop2 = x.Prop2 ...}
    var select = Expression.Lambda<Func<T, T>>(Expression.MemberInit(Expression.New(typeof(T)), bindings), arg);
    return query.Select(select);
}

But if we actually try that:

// some test entity I use
var t = ctx.Errors.IncludeOnly("ErrorID", "ErrorCode", "Duration").Take(10).ToList();

It will fail with exception

The entity or complex type ... cannot be constructed in a LINQ to Entities query

So, new SomeType is illegal in Select if SomeType is type of mapped entity.

But what if we have a type inherited from entity and use that?

public class SomeTypeProxy : SomeType {}

Well, then it will work. So we need to get such proxy type somewhere. It's easy to generate it at runtime with built-in tools, since all we need is to inherit from some type and that's all.

With that in mind, our method becomes:

static class Extensions {
    private static ModuleBuilder _moduleBuilder;
    private static readonly Dictionary<Type, Type> _proxies = new Dictionary<Type, Type>();

    static Type GetProxyType<T>() {
        lock (typeof(Extensions)) {
            if (_proxies.ContainsKey(typeof(T)))
                return _proxies[typeof(T)];

            if (_moduleBuilder == null) {
                var asmBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly(
                    new AssemblyName("ExcludeProxies"), AssemblyBuilderAccess.Run);

                _moduleBuilder = asmBuilder.DefineDynamicModule(
                    asmBuilder.GetName().Name, false);
            }

            // Create a proxy type
            TypeBuilder typeBuilder = _moduleBuilder.DefineType(typeof(T).Name + "Proxy",
                TypeAttributes.Public |
                TypeAttributes.Class,
                typeof(T));

            var type = typeBuilder.CreateType();
            // cache it
            _proxies.Add(typeof(T), type);
            return type;
        }
    }

    public static IQueryable<T> IncludeOnly<T>(this IQueryable<T> query, params string[] properties) {
        var arg = Expression.Parameter(typeof(T), "x");
        var bindings = new List<MemberBinding>();

        foreach (var propName in properties) {
            var prop = typeof(T).GetProperty(propName);
            bindings.Add(Expression.Bind(prop, Expression.Property(arg, prop)));
        }

        // modified select, (T x) => new TProxy {Prop1 = x.Prop1, Prop2 = x.Prop2 ...}
        var select = Expression.Lambda<Func<T, T>>(Expression.MemberInit(Expression.New(GetProxyType<T>()), bindings), arg);
        return query.Select(select);
    }
}

And now it works fine and generates select sql query with only included fields. It really returns a list of proxy types, but that's not a problem, since proxy type inherits from your query type. Thought as I said before - you cannot attach\update\remove it from context.

Of course you can also modify this method to exclude, accept property expressions instead of pure strings and so on, that's just idea proof code.