How to use the where clause with IQueryable

Question

I am using Entity Framework Core with reflection to generate some forms dynamically. Everything is working except the WHERE Clause. I get the following error:

An expression tree may not contain a dynamic operation

I am able to fix this by converting my IQueryable to a List, but that introduces different problems that i would like to avoid.

Here is my code:

public async void ViewCollection(PropertyInfo propertyInfo)
{
    Type propertyType = propertyInfo.PropertyType;
    InversePropertyAttribute inversePropertyAttribute = (InversePropertyAttribute)ReflectionHelpers.GetAttribute(propertyInfo, typeof(InversePropertyAttribute));


    //GET THE TYPE OF THE COLLECTION
    Type collectionType = propertyInfo.PropertyType.GenericTypeArguments[0];

    //GET THE INVERSE PROPERTY INFO
    PropertyInfo inverseProperty = collectionType.GetProperty(inversePropertyAttribute.Property);

    //GET THE FOREIGN KEY ATTRIBUTE FROM THE INVERSE PROPERTY
    ForeignKeyAttribute foreignKeyAttribute = (ForeignKeyAttribute)ReflectionHelpers.GetAttribute(inverseProperty, typeof(ForeignKeyAttribute));

    //GET THE FOREIGN KEY PROPERTY FROM THE FOREIGN KEY ATTRIBUTE
    PropertyInfo foreignKeyProperty = collectionType.GetProperty(foreignKeyAttribute.Name);

    //GET INCLUDED TYPE NAMES BY FOREIGN KEY
    IEnumerable<string> includedTypes = collectionType.GetProperties().Where(p => p.PropertyType.IsClass).Where(p => ReflectionHelpers.HasAttribute(p, typeof(ForeignKeyAttribute))).Select(r => r.Name);

    //GET THE DATA SET
    IQueryable<dynamic> items = ReflectionHelpers.GetDbCollectionByType(Db, collectionType);

    //INCLUDE THE INCLUDED TYPES BY NAME
    foreach (string includedType in includedTypes) items = items.Include(includedType);

    //THIS IS WHERE THE ERROR IS
    items = items.Where(i => foreignKeyProperty.GetValue(i, null) == PrimaryKeyProperty.GetValue(Item, null));

    await ShowCollection(collectionType, items, propertyInfo.Name);
}

How can i solve this with out changing my type to a list?

score 1 · Answer 1 · answered Jun 18 '20 at 06:28

You can't. IQueryable is part of language integrated query (LINQ) - it's statically typed, so you cannot use value types.

IQueryable behind the scenes is an expression tree that represents a query that has not yet been executed. The expression tree part represents the query. The static types represent the data that the query operates over.

Your best alternative is to build the expression tree by hand using the expression tree API.

score 1 · Answer 2 · answered Jun 18 '20 at 10:05

1

You can't build queries over dynamic because it's not supported natively in expression trees. Instead you should base your work on either non generic IQueryable or generic IQueryable<object>. e.g. if the ReflectionHelpers.GetDbCollectionByType is calling DbContext.Set<T> dynamically (similar to here Dynamically access table in EF Core 2.0), you should be able to cast it to IQueryable<object>:

var items = (IQueryable<object>)ReflectionHelpers.GetDbCollectionByType(Db, collectionType);

To add Where clause, you shouldn't use reflection calls inside the predicate expression, but build it dynamically using the Expression class methods (from System.Linq.Expressions namespace). Something like this:

// (object i) => (({collectionType})i).{ForeignKey} == Item.{PrimaryKey}
var parameter = Expression.Parameter(typeof(object), "i");
var body = Expression.Equal(
    Expression.Property(Expression.Convert(parameter, collectionType), foreignKeyProperty),
    Expression.Property(Expression.Constant(Item), PrimaryKeyProperty));
var predicate = Expression.Lambda<Func<object, bool>>(body, parameter);

and then

items = items.Where(predicate); // still IQueryable<object>

answered Jun 18 '20 at 10:05

Ivan Stoev

195,425
15
312
343

Ivan thank you for the effort. When trying your suggestion i get this error: Error CS1929 'IQueryable – Tom Crosman Jun 18 '20 at 17:51
Ivan, this is my code for GetDbCollectionByType: (IQueryable)db.GetType().GetMethod("Set").MakeGenericMethod(propertyType).Invoke(db, null); – Tom Crosman Jun 18 '20 at 17:53
Change `IQueryable` to `IQueryable – Ivan Stoev Jun 18 '20 at 19:29
https://imgur.com/a/Wqk69c2 if you want to see an image – Tom Crosman Jun 18 '20 at 19:35
Hmm, interesting, you have `Include` but not `Where`. Very strange. When you type `items.Where`, what does Intellisense show? You are supposed to see 4 overloads - 2 for `IEnumerable – Ivan Stoev Jun 18 '20 at 19:45
I get 5 overloads...hard to explain them – Tom Crosman Jun 18 '20 at 19:48
Only `items = ((IQueryable – IS4 Aug 03 '20 at 02:15

How to use the where clause with IQueryable

2 Answers2