Using EF.Property to target included entity property

[Enngage]

Hi!

Is it possible to use EF.Property<> to target property of included entities? Following attempts:

 var fail = await Dependencies.EntityServices.FavoriteExerciseService.GetAll()
     .Include(m => m.Exercise)
     .Where(m => EF.Functions.Contains(EF.Property<string>(m, "Exercise.DisplayName"), "\"*press*\""))
     .ToListAsync();

var fail = await Dependencies.EntityServices.FavoriteExerciseService.GetAll()
     .Include(m => m.Exercise)
     .Where(m => EF.Functions.Contains(EF.Property<string>(m, m.Exercise.DisplayName), "\"*press*\""))
     .ToListAsync();

throw

The property 'Exercise.DisplayName' on entity type 'FavoriteExercise' could not be found. Ensure that the property exists and has been included in the model.

The DisplayName on Exercise type is properly indexed and this works and returns correct results:

var ok = await Dependencies.EntityServices.ExerciseService.GetAll()
                .Where(m => EF.Functions.Contains(EF.Property<string>(m, "DisplayName"), "\"*press*\""))
                .ToListAsync();

I also tried running following query on SQL which also returns proper results.

select * from FavoriteExercises join Exercises
on FavoriteExercises.ExerciseId = Exercises.Id
where Contains(Exercises.DisplayName, '"*press*"')

Thank you for any help or suggestions.

Further technical details

EF Core version: 3.0.0-preview5.19227.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win 10
IDE: Visual Studio 2019 preview

[smitpatel]

EF.Property can be used to access property or navigation defined in EF Core metadata. But the function does not parse multi-level as you intend. The proper way to write such query would be like this.

 var fail = await Dependencies.EntityServices.FavoriteExerciseService.GetAll()
     .Include(m => m.Exercise)
     .Where(m => EF.Functions.Contains(EF.Property<string>(EF.Property<Exercise>(m, "Exercise"), "DisplayName"), "\"*press*\""))
     .ToListAsync();

The 2nd fail query is not valid since the property or navigation name is not passed as string. Compilation would fail.

[smitpatel]

Also you don't need include to access property on a navigation in where predicate. You would only need include if you are projecting the entity and want the navigation to be populated for the materialized entity.

[Enngage]

@smitpatel thank you! That indeed works. Though, the problem I have is that Exercise type can be anything and it is known only at runtime. Would it be somehow possible to pass the Type or string instead of using generics?

Edit: Just tried using interface IEntity which all my entity types implement and it seems to work with:

 var ok = await Dependencies.EntityServices.FavoriteExerciseService.GetAll()
                .Include(m => m.Exercise)
                .Where(m => EF.Functions.Contains(EF.Property<string>(EF.Property<IEntity>(m, "Exercise"), "DisplayName"), "\"*press*\""))
                .ToListAsync();

Is this a valid way of using the EF.Property?

[smitpatel]

The generic constraint on EF.Property is to allow you to compose over while writing query. (e.g. doing EF.Property<Exercise>(m, "Exercise").Name). Implementation wise, we just work with the type navigation provides us so what you have written would work.

[Enngage]

@smitpatel it seems that this has stopped working in the preview8 :/ (I'm coming from preview5 as preview6 didn't work for me)

This works ok:

await Dependencies.EntityServices.FavoriteExerciseService.GetAll()
    .Where(m => EF.Functions.Contains(
       EF.Property<string>(EF.Property<Exercise>(m, "Exercise"), "DisplayName"), "\"*press*\""))
    .ToListAsync();

This does not:

await Dependencies.EntityServices.FavoriteExerciseService.GetAll()
      .Where(m => EF.Functions.Contains(
         EF.Property<string>(EF.Property<IEntity>(m, "Exercise"), "DisplayName"), "\"*press*\""))
      .ToListAsync();

The exception thrown is:

System.InvalidOperationException: EF.Property called with wrong property name. at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at

Is this behavior intentional? Or maybe there is indeed a way of providing the type instead of generics?

[smitpatel]

Likely, somewhere a convert to interface node got added which caused client eval. Currently it is not possible for us to bind EF Property to an interface type as we don't have metadata about interface like we have for EntityType.

[Enngage]

That seems strange as it just worked :/ Can you think of any workaround? Other than possibly writing the SQL manually as that is not that simple to put it in our code as we are working with IQueryable and have no access DbSet (where sql methods were moved)

[smitpatel]

Easy work-around, don't use interfaces. If you use concrete type (which is mapped in EF Core model), it will work.

[Enngage]

I do have a Type, but I can't use it in the EF.Property because that only takes generic argument. Maybe this should be extended to also accept Type as parameter? Entire query is build at runtime so I have to work with string names or Types when necessary.

[smitpatel]

I was looking at first post to see query, can you use m.Exercise directly no?

[Enngage]

Yeah, but the query is just an example. The Where condition as in:

.Where(m => EF.Functions.Contains(EF.Property<string>(EF.Property<IEntity>(m, "Exercise"), "DisplayName"), "\"*press*\""))

Is applied dynamically on potentially any DbSet with any property and any navigation property. Imho, it would be awesome to be able to pass type to the EF.Propery method as that would likely solve the problem. Though, I'm a bit sad that the previous implementation with interfaces no longer works :( It was indeed producing proper queries on database.

[ajcvickers]

Triage: probably need to punt this too. More specifically, the value of this is low without a means to work with non-generic IQueryable in the first place, which is #17193