Open Type with EF Core and Key/Value table

[BorisWilhelms]

Hello,

this is at first a question and then maybe a bug, depending if we do things correct. The full example/repro can be found here: https://github.com/BorisWilhelms/ODataEfCoreOpenType/tree/main

We want to use Open Types to represent custom properties. These custom properties are stored in a key/value table.

In our model we reference the CustomAttribute table and then build the Dictionary dynamically

public class Person
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<PersonCustomAttribute> CustomAttributes { get; set; }

    public Dictionary<string, object> Properties => CustomAttributes?.ToDictionary(a => a.Key, a => (object)a.Value);
}

In the EDM we ignore the CustomAttributes property and in the DbContext we ignore the Properties property.

In the Controller we check if a custom attribute is selected and do a manual include of the CustomAttributes table.

    [EnableQuery]
    public ActionResult<IQueryable<Person>> Get(ODataQueryOptions<Person> options)
    {
        var query = _dbContext.Persons.AsQueryable();
        
        // Check if a property is select, that does not belong to the entity and assume
        // it is a custom attribute
        var items = options.SelectExpand?.SelectExpandClause?.SelectedItems.OfType<PathSelectItem>();
        var attributeSelected = items?.SelectMany(i => i.SelectedPath).OfType<DynamicPathSegment>().Any();
        if (attributeSelected.HasValue && attributeSelected.Value)
        {
            query = query.Include(e => e.CustomAttributes);
        }

        return Ok(query);
    }

Overall this works, but we have some problems/questions:

1. Is this the way how we should do it, when having a Key/Value table?
2. If we do a OData select with a custom attribute, OData/Efcore produces an "incorrect" sql, selecting ALL fields from the person table. We assume that it should only select the Id from person

### Select field from Person and custom attribute, produces incorrect SQL (selects all fields from person):
# SELECT "p"."Id", "p"."Name", "p0"."Id", "p0"."Key", "p0"."PersonId", "p0"."Value"
# FROM "Persons" AS "p"
# LEFT JOIN "PersonCustomAttribute" AS "p0" ON "p"."Id" = "p0"."PersonId"
# ORDER BY "p"."Id"
GET https://localhost:7135/odata/Person?$select=id,City

3. If we do a OData filter on a custom attribute we get an exception

GET https://localhost:7135/odata/Person?$select=id,City&$filter=City eq 'Frankfurt'

      System.ArgumentException: Method 'System.Object get_Item(System.String)' declared on type 'System.Collections.Generic.Dictionary`2[System.String,System.Object]' cannot be called with instance of type 'System.Object'
         at System.Linq.Expressions.Expression.ValidateCallInstanceType(Type instanceType, MethodInfo method)
         at System.Linq.Expressions.Expression.ValidateAccessor(Expression instance, MethodInfo method, ParameterInfo[] indexes, ReadOnlyCollection`1& arguments, String paramName)
         at System.Linq.Expressions.Expression.ValidateIndexedProperty(Expression instance, PropertyInfo indexer, String paramName, ReadOnlyCollection`1& argList)
         at System.Linq.Expressions.Expression.MakeIndex(Expression instance, PropertyInfo indexer, IEnumerable`1 arguments)
         at System.Linq.Expressions.IndexExpression.Rewrite(Expression instance, Expression[] arguments)
         at System.Linq.Expressions.ExpressionVisitor.VisitIndex(IndexExpression node)
         at System.Linq.Expressions.IndexExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitConditional(ConditionalExpression conditionalExpression)
         at System.Linq.Expressions.ConditionalExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
         at Microsoft.EntityFrameworkCore.Sqlite.Query.Internal.SqliteSqlTranslatingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
         at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
         at Microsoft.EntityFrameworkCore.Sqlite.Query.Internal.SqliteSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
         at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)
         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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
         at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.IEnumerable.GetEnumerator()
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSetAsync(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInlineAsync(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectAsync(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatterHelper.WriteToStreamAsync(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, HttpRequest request, IHeaderDictionary requestHeaders, IODataSerializerProvider serializerProvider)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

It looks like, that EfCore is trying to filter on the Dictionary?

So, is this the right way to do that, or is this a not supported scenario?

Best regards
Boris

[BorisWilhelms]

Any Ideas @xuzhg?

[xuzhg]

@BorisWilhelms Thanks for filing this. I will look your repro and share a response with you later. By the way, it seems it's similar with https://github.com/xuzhg/mydotnetconf. Did you get a chance to try my sample first?

[BorisWilhelms]

@xuzhg thanks for the example. As far as I understand, the Binders and Serializer might help with the select and filter? Will check that out

[aboryczko]

I was doing a PoC of the exact same thing this week. I've ended up with implementing a custom FilterBinder with my own implementation of BindDynamicPropertyAccessQueryNode to dynamically return an Expression similar to this x => x.CustomAttributes.Where(y => (y.Key== "City")).Select (y => y.Value).FirstOrDefault (). I didn't try optimizing the Select portion so whenever I specified a dynamic property in the $select it always bound it to fetch all fields, even the ones I didn't need.