Navigation Properties with OData and EF Core

[DavidM29]

Hello, I am fetching data from SQL Server which is used by Microsoft Navision and then I am exposing that data with ASP .NET Core OData. My problem now is that, Navision has "FlowFields" that are columns calculated from other tables during runtime (They are not stored in DB), so I have to calculate those columns myself, right now I am doing it this way:
(ManufacturerName is "FlowField" and it is being calculated from Manufacturer Table)

dbContext.Set<Item>().AsNoTracking().Where(item => item.No == key)
            .Select(s => new Item
            {
                No = s.No,
                Description = s.Description,
                Vehicle = s.Vehicle,
                ItemCategoryCode = s.ItemCategoryCode,
                ItemCreationDate = s.ItemCreationDate,
                OnHold = s.OnHold,
                InventoryPostingGroup = s.InventoryPostingGroup,
                ManufacturerCode = s.ManufacturerCode,
                ManufacturerName = s.Manufacturer == null ? string.Empty : s.Manufacturer.Name,
                ParametersGroupingCodeAs = s.ParametersGroupingCodeAs,
                Manufacturer = s.Manufacturer,
                ItemTranslations = s.ItemTranslations,
                ItemPictures = s.ItemPictures
            })

This works, but it causes all navigation properties to be fetched always(Manufacturer, ItemTranslations, ItemPictures) and if $expand is used in the OData Query, this navigation properties are fetched twice. If I omit the navigation properties in the Select, then the OData $expand does not work anymore, is it possible to not select the Navigation properties in the Select and make the $expand work?

I can provide more details if needed.

Thanks in advance.

[xuzhg]

@DavidM29 is https://github.com/xuzhg/WebApiSample/tree/main/v9.x/CalculatedPropertyTest the one what you are looking for?

[DavidM29]

Hello @xuzhg, yes that is exactly what I am facing.
Regarding the third case that is written there, when I try to use $expand and I have not included the navigation properties in the Select, I get this exception:

{
  "error": {
    "code": "",
    "message": "The query specified in the URI is not valid. The LINQ expression '$it => $it.ItemNo' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.",
    "details": [],
    "innererror": {
      "message": "The LINQ expression '$it => $it.ItemNo' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.",
      "type": "System.InvalidOperationException",
      "stacktrace": "   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateProjection(Expression expression, Boolean applyDefaultTypeMapping)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.IEnumerable.GetEnumerator()\r\n   at Microsoft.AspNetCore.OData.Query.EnableQueryAttribute.SingleOrDefault(IQueryable queryable, ControllerActionDescriptor actionDescriptor)\r\n   at Microsoft.AspNetCore.OData.Query.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, ControllerActionDescriptor actionDescriptor, HttpRequest request)\r\n   at Microsoft.AspNetCore.OData.Query.EnableQueryAttribute.OnActionExecuted(ActionExecutedContext actionExecutedContext, Object responseValue, IQueryable singleResultCollection, ControllerActionDescriptor actionDescriptor, HttpRequest request)"
    }
  }
}

Is there a workaround for that?

[DavidM29]

Hello @xuzhg,
are there any news regarding this? Is what I am trying to do possible without always selecting all navigation properties, or should I start looking for other ways for getting calculated properties?

[DavidM29]

Whoever encounters this, if you are using EF Core, this problem can be avoided by using EntityFrameworkCore.Projectables nuget package.