Execution-Deferred IQueryable<T> from Dynamic Linq?

Question

I am using Dynamic Linq to perform some queries (sorry but it's my only option). As a result, I am getting an IQueryable instead of an IQueryable<T>. In my case, I want an IQueryable<Thing> where Thing is a concrete type.

My query is as such:

public IQueryable<Thing> Foo(MyContext db)
{
    var rootQuery = db.People.Where(x => x.City != null && x.State != null);
    var groupedQuery = rootQuery.GroupBy("new ( it.City, it.State )", "it", new []{"City", "State"});
    var finalLogicalQuery = groupedQuery.Select("new ( Count() as TotalNumber, Key.City as City, Key.State as State )");
    var executionDeferredResults = finalLogicalQuery.Take(10); // IQueryable

    IQueryable<Thing> executionDeferredTypedThings = ??; // <--- Help here!!!!

    return executionDeferredTypedThings;
}

My Thing.cs:

public class Thing
{
    public int TotalNumber { get; set; }
    public string City { get; set; }
    public string State { get; set; }
}

Yes, I know the exact above thing can be done without Dynamic Linq but I have some variableness going on that I've simplified out of here. I can get it to work with my variableness if my return type is simply IQueryable but I can't figure out how to convert to IQueryable<Thing> while keeping it execution-deferred and while also keeping Entity Framework happy. I do have the dynamic Select always returning something (with the correct data) that looks like a Thing. But I simply can't figure how to return the IQueryable<Thing> and could use some help there. Thanks!!

Failed Attempt 1

Based on Rex M's suggestion, I am now trying to use AutoMapper to solve this problem (although I am not committed to this approach and am willing to try other approaches). For the AutoMapper approach, I am doing it as such:

IQueryable<Thing> executionDeferredTypedThings = executionDeferredResults.ProjectTo<Thing>(); // <--- Help here!!!!

But this results in an InvalidOperationException:

Missing map from DynamicClass2 to Thing. Create using Mapper.CreateMap.

The thing is, while I have defined Thing, I have not defined DynamicClass2 and as such, I cannot map it.

Failed Attempt 2

IQueryable<Thing> executionDeferredTypedThings = db.People.Provider.CreateQuery<Thing>(executionDeferredResults.Expression);

This gives an InvalidCastException and seems to be the same underlying problem that the above AutoMapper fail hits:

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery'1[DynamicClass2]' to type 'System.Linq.IQueryable'1[MyDtos.Thing]'.

Eyot

Does DLinq let you project a strongly typed object? .Take(10).Select(x => new Thing() { })? Sorry, I haven't used it before. – Wiedmann

If so, I'm not sure how to do this. Especially in my case where I conditionally want it to either be set to a default (null or 0 in most cases) unless it's a column I'm grouping by. So a pseudocode of that would look something like this: .Take(10).Select(x => new Thing{City = Key.City ?? null)), if that makes sense. I don't think that's really the right path to take for this because I'm doing conditional grouping. – Eyot

By the way, have you tried IQueryable.Cast ? – Kilan

@hazimdikenli: That gives me an InvalidOperationException: Unable to cast the type 'DynamicClass2' to type 'MyDtos.Thing'. LINQ to Entities only supports casting EDM primitive or enumeration types. – Eyot

Do you really need all this? I mean, you are not receiving the information as strings, right? From what you wrote, I assume you build GroupBy and Select strings dynamically from something else just to let Dynamic Linq do the plumbing part. But all that have an overhead, so I'm wondering if some strongly typed group/select helpers wouldn't do that better. – Symbiosis

@IvanStoev - I receive an array of up to 15 column names to group by (ultimately it's user-defined grouping). The code I have simplified out will build the strings in both the GroupBy and Select based on that array. By changing the GroupBy clause in Linq, you change the definition of the anonymous that it is strongly-typed to (both the Key object and the Select object). Because anonymous types must be known at compile time yet these structures are not known until runtime, I don't believe strongly-typed helpers will work unless I have a type for all 15! = 1,307,674,368,000 combinations. – Eyot

Do you really need IQueryable<Thing> or just IEnumerable<Thing> that has deferred execution? The difference in this case between the two is that IQueryable<Thing> can be further modified (e.g. you can further filter the query) and IEnumerable<Thing> can't. – Isidroisinglass

@YacoubMassad I need it to be further modified. So IQueryable<T>. – Eyot

Answer 1

If I understand correctly, the following extension method should do the job for you

public static class DynamicQueryableEx
{
    public static IQueryable<TResult> Select<TResult>(this IQueryable source, string selector, params object[] values)
    {
        if (source == null) throw new ArgumentNullException("source");
        if (selector == null) throw new ArgumentNullException("selector");
        var dynamicLambda = System.Linq.Dynamic.DynamicExpression.ParseLambda(source.ElementType, null, selector, values);
        var memberInit = dynamicLambda.Body as MemberInitExpression;
        if (memberInit == null) throw new NotSupportedException();
        var resultType = typeof(TResult);
        var bindings = memberInit.Bindings.Cast<MemberAssignment>()
            .Select(mb => Expression.Bind(
                (MemberInfo)resultType.GetProperty(mb.Member.Name) ?? resultType.GetField(mb.Member.Name),
                mb.Expression));
        var body = Expression.MemberInit(Expression.New(resultType), bindings);
        var lambda = Expression.Lambda(body, dynamicLambda.Parameters);
        return source.Provider.CreateQuery<TResult>(
            Expression.Call(
                typeof(Queryable), "Select",
                new Type[] { source.ElementType, lambda.Body.Type },
                source.Expression, Expression.Quote(lambda)));
    }
}

(Side note: Frankly I have no idea what values argument is for, but added it to match the corresponding DynamicQueryable.Select method signature.)

So your example will become something like this

public IQueryable<Thing> Foo(MyContext db)
{
    var rootQuery = db.People.Where(x => x.City != null && x.State != null);
    var groupedQuery = rootQuery.GroupBy("new ( it.City, it.State )", "it", new []{"City", "State"});
    var finalLogicalQuery = groupedQuery.Select<Thing>("new ( Count() as TotalNumber, Key.City as City, Key.State as State )");  // IQueryable<Thing>
    var executionDeferredTypedThings = finalLogicalQuery.Take(10);
    return executionDeferredTypedThings;
}

How it works

The idea is quite simple.

The Select method implementation inside the DynamicQueryable looks something like this

public static IQueryable Select(this IQueryable source, string selector, params object[] values)
{
    if (source == null) throw new ArgumentNullException("source");
    if (selector == null) throw new ArgumentNullException("selector");
    LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, null, selector, values);
    return source.Provider.CreateQuery(
        Expression.Call(
            typeof(Queryable), "Select",
            new Type[] { source.ElementType, lambda.Body.Type },
            source.Expression, Expression.Quote(lambda)));
}

What it does is to dynamically create a selector expression and bind it to the source Select method. We take exactly the same approach, but after modifying the selector expression created by the DynamicExpression.ParseLambda call.

The only requirement is that the projection is using "new (...)" syntax and the names and types of the projected properties match, which I think fits in your use case.

The returned expression is something like this

(source) => new TargetClass
{
    TargetProperty1 = Expression1(source),
    TargetProperty2 = Expression2(source),
    ...
}

where TargetClass is a dynamically generated class.

All we want is to keep the source part and just replace that target class/properties with the desired class/properties.

As for the implementation, first the property assignments are converted with

var bindings = memberInit.Bindings.Cast<MemberAssignment>()
    .Select(mb => Expression.Bind(
        (MemberInfo)resultType.GetProperty(mb.Member.Name) ?? resultType.GetField(mb.Member.Name),
        mb.Expression));

and then the new DynamicClassXXX { ... } is replaced with with

var body = Expression.MemberInit(Expression.New(resultType), bindings);

Symbiosis

I'm just looking at this from my phone right now but will that complex Select statement work with Entity Framework in the database? Either way I'll get to try this tomorrow when I get to my workstation. – Eyot

@Eyot I believe so. Basically this is the code of the Dynamic Linq Select implementation with few lines added to replace some parts of the generated new { .. }. – Symbiosis

Initial tests are good! I'm handing this off to another developer to further verify this for me. I hope to close this here in a few hours. – Eyot

This solution ended up being exactly what was needed! We were able to adopt this code to work perfectly with what we needed to do. Thanks a ton, Ivan!! – Eyot

I just wanted to follow-up and say that this solution has worked very well for us. I had to tweak a few things for our purposes but I would have never gotten there without your help. Thanks again!! – Eyot

Answer 2

You can use AutoMapper's Queryable Extensions to produce an IQueryable which wraps the underlying IQueryable, thus preserving the original IQueryable's IQueryProvider and the deferred execution, but adds in a mapping/translating component to the pipeline to convert from one type to another.

There's also AutoMapper's UseAsDataSource which makes some common query extension scenarios easier.

Monolithic

I see how to do this when both the source and destination classes are concrete classes. I'm not sure how to do this, though, when my Dynamic Linq is providing an unknown (to me) class type. If I try it with no mapping, I get the missing map error, as expected. The exact message: Missing map from DynamicClass2 to Thing. Create using Mapper.CreateMap<DynamicClass2, Thing>. While Thing is defined by me, the DynamicClass2 class is not something I define nor can I reference. They should both have the same properties but I don't know how to map that. – Eyot

I was never able to find a way to utilize AutoMapper to perform this translation successfully. – Eyot

Answer 3

Would something like this be of benefit to you?

public static IQueryable<TEntity> GetQuery<TEntity>(this DbContext db, bool includeReferences = false) where TEntity : class
    {
        try
        {
            if (db == null)
            {
                return null;
            }

            var key = typeof(TEntity).Name;
            var metaWorkspace = db.ToObjectContext().MetadataWorkspace;
            var workspaceItems = metaWorkspace.GetItems<EntityType>(DataSpace.OSpace);
            var workspaceItem = workspaceItems.First(f => f.FullName.Contains(key));
            var navProperties = workspaceItem.NavigationProperties;

            return !includeReferences
                    ? db.Set<TEntity>()
                    : navProperties.Aggregate((IQueryable<TEntity>)db.Set<TEntity>(), (current, navProperty) => current.Include(navProperty.Name));
        }
        catch (Exception ex)
        {
            throw new ArgumentException("Invalid Entity Type supplied for Lookup", ex);
        }
    }

You may want to take a look into the Generic Search project on Github located here: https://github.com/danielpalme/GenericSearch

Driedup

I'm afraid I don't understand what you're suggesting I do with this. There's no obvious way I see to utilize this code to help me. – Eyot

The Github project I mentioned uses custom expression trees and generics to query data and then generate search filters/paging/etc, The custom expression trees and generics will allow you to utilize the entity framework models and DbContexts in a generic sense without requiring knowledge of the schema beforehand. This would eliminate a need for dynamic linq and would allow you to use linq expressions and generics to query your data without a third party solution – Driedup

Instead of using db.People.Whatever . . . you could use db.GetQuery<YourModel>() and use lambda expressions – Driedup

I cannot use Generics because of my dynamic grouping. Sometimes I group by just City. Sometimes just State. Sometimes by LastName. Sometimes by Age. Sometimes by some of the above. Sometimes by all of the above. And more. The moment I start using generics in my expression trees, is either the moment dynamic grouping does not work or is the moment that I have to have 250 different conditional statements building each one up. As I mentioned in my question, my code is more complex than above. The above code is just a simplified reproduction of the problem. – Eyot

Answer 4

There is no need for Dynamic Linq on this one.

var groupedQuery = from p in db.People
    where p.City != null && p.State != null
    group p by new {p.City, p.State}
    into gp
    select new Thing {
        TotalNumber = gp.Count(),
        City = gp.Key.City,
        State = gp.Key.State
    };

IQueryable<Thing> retQuery = groupedQuery.AsQueryable(); 
retQuery= retQuery.Take(10);
return retQuery;

Kilan

This is standard Linq To Entities. You do not need to use Dynamic Linq. If you need to apply a filter to people data set before taking the sum/count, you can pass that in as an Expression, and apply it in your query. – Kilan

As I said in the original post, I have simplified some stuff out. I do need Dynamic Linq because I have a changing set of columns that I'm grouping by. I have ~25 different columns and if I don't use Dynamic Linq then I have to have some kind of conditional logic handling all possible combinations. That's a lot! And not an option. – Eyot