CODEWITHSUNDEEP
c#.netlinq
Neir0
Neir0

Reputation: 13367

Inner method in Linq query that return IQueryable

I would like to do following thing in Entity Framework code first:

Data.GroupBy(x => x.Person.LastName)
.Select(x => x.OtherGrouping(...)).Where(...).GroupBy(...)...etc



public static class Extensions
{

public static IQueryable<IGrouping<T, T>> OtherGrouping<T>(this IQueryable<T> collection /**** Here I want to pass consts and lambdas\expressions*****/)
    {
        // Grouping, filtration and other stuff here
        return collection.GroupBy(x => x);
    }
}

The problem is .net compiles OtherGrouping method, it doesn't represent like an expression, and couldn't be transformed to the SQL.

I found LinqKit library which suppose to help in such cases, but I coudn't figure out how to apply it in my specific case. It works fine for simple cases, when I just have expression like x => x+2, but I get stucked with return IQueryable. Probably it is possible to write expression tree completely by hand but I don't want to go so deep.

Any ideas how it might be done or where I can read about it?

Here is what I tried to do based on Rob's comment

void Main()
{
    AddressBases.GroupBy(x => x.ParentId).Select(x => new {x.Key, Items = x.AsQueryable().OtherGrouping(a => a.Country) }).Dump();

}

public static class Extensions
{
    public static IQueryable<IGrouping<TKey, TSource>> OtherGrouping<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector)
    {   
        return source.Provider.CreateQuery<IGrouping<TKey, TSource>>(
            source.GroupBy(keySelector).Expression);
    }
}

And I got an exception:

NotSupportedException: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[System.Linq.IGrouping`2[System.String,InsuranceData.EF.AddressBase]] OtherGrouping[AddressBase,String](System.Linq.IQueryable`1[InsuranceData.EF.AddressBase], System.Linq.Expressions.Expression`1[System.Func`2[InsuranceData.EF.AddressBase,System.String]])' method, and this method cannot be translated into a store expression.

Couldn't figure out yet why I have OtherGrouping method in expression tree? OtherGrouping method should just attach another grouping to the expression and pass it to the provider, but not put itself to the tree.

Upvotes: 3

Views: 1199

Answers (1)

Rob
Rob

Reputation: 27357

Your current code after the edit is correct - where you're going wrong is a common issue with the syntactic sugar we're given by C#.

If you write the following:

void Main()
{
    var res = Containers.OtherGrouping(c => c.ContainerID);
    res.Expression.Dump();
    res.Dump();
}

public static class Extensions
{
    public static IQueryable<IGrouping<TKey, TSource>> OtherGrouping<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector)
    {
        return source.Provider.CreateQuery<IGrouping<TKey, TSource>>(
            source.GroupBy(keySelector).Expression
        );
    }
}

You'll see that we get the output:

Table(Container).GroupBy(c => c.ContainerID)

And the result executes with an issue, properly grouping by our predicate. However, you're invoking OtherGrouping while inside an expression, when you're passing it to Select. Let's try a simple case without OtherGrouping:

var res = Containers.OtherGrouping(c => c.ContainerID)
    .Select(x => new
    {
        x.Key,
        Items = x.GroupBy(c => c.ContainerID),
    });
res.Expression.Dump();

What you're providing to Select is an expression tree. That is, the inner GroupBy's method is never actually invoked. If you change the query to x.AsQueryable().OtherGrouping and put a breakpoint in OtherGrouping, it will only be hit the first time.

In addition to that, x is IEnumerable<T>, not IQueryable<T>. Invoking AsQueryable() gives you an IQueryable, but it also gives you a new query provider. I'm not 100% sure as to the inner workings of entity framework, but I'd wager to say that this is invalid - as we no longer have the database as a target for the provider. Indeed, with linq2sql (using LINQPad), we get an error saying .AsQueryable() is not supported.

So, what can you do? Unfortunately there's no clean way to do this. Since the expression tree is already built before OtherGrouping has a chance, it doesn't matter what the body of OtherGrouping is. We'll need to change the expression tree after it's built, but before it's executed.

For that, you'll need to write an expression visitor which will look for .OtherGrouping expression calls, and replace it with Queryable.GroupBy

Upvotes: 1

Related Questions