OData Linq Expression not be translated issue using automapper #27460

xuzhg · 2022-02-16T19:59:38Z

File a bug

One of our customers want to query data using

https://localhost:7059/api/Consulente?PageNumber=1&PageSize=10&$expand=N_Consulente_Commessa

It works fine, we can get the following:

[
    {
        "N_Consulente_Commessa": [
            {
                "ConsulenteId": 1,
                "CommessaId": 1,
                "Id": 1,
                "AddedDate": "2022-02-15T14:42:19.9415024",
                "ModifiedDate": null
            },
            {
                "ConsulenteId": 1,
                "CommessaId": 2,
                "Id": 2,
                "AddedDate": "2022-02-15T14:42:19.9415064",
                "ModifiedDate": null
            },
            {
                "ConsulenteId": 1,
                "CommessaId": 3,
                "Id": 3,
                "AddedDate": "2022-02-15T14:42:19.9415069",
                "ModifiedDate": null
            }
        ],
        "NumeroMatricola": "33333333333",
        "CodiceFiscale": "33333333333",
        "Email": "[email protected]",
        "Name": "Marco",
        "Surname": "Gio",
        "CommesseIds": [
            1,
            2,
            3
        ],
        "Id": 1,
        "AddedDate": "2022-02-15T14:42:19.9485044",
        "ModifiedDate": null
    },
    {
        "N_Consulente_Commessa": [
            {
                "ConsulenteId": 2,
                "CommessaId": 4,
                "Id": 4,
                "AddedDate": "2022-02-15T14:42:19.9415074",
                "ModifiedDate": null
            },
            {
                "ConsulenteId": 2,
                "CommessaId": 1,
                "Id": 5,
                "AddedDate": "2022-02-15T14:42:19.9415079",
                "ModifiedDate": null
            }
        ],
        "NumeroMatricola": "33333333366",
        "CodiceFiscale": "33333333366",
        "Email": "[email protected]",
        "Name": "Mario",
        "Surname": "Ro",
        "CommesseIds": [
            4,
            1
        ],
        "Id": 2,
        "AddedDate": "2022-02-15T14:42:19.9517946",
        "ModifiedDate": null
    }
]

However, if we put a $filter in the URI like:

https://localhost:7059/api/Consulente?PageNumber=1&PageSize=10&$expand=N_Consulente_Commessa($filter=Id eq 2)

It throws exception as:

The LINQ expression '$it => new SelectAll<Consulente_CommessaDto>{ 
    Model = __TypedProperty_2, 
    Instance = $it, 
    UseInstanceForProperties = True 
}
' 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.

Here's the screenshot:

Here's the call-stack:

   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs:line 502
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs:line 122
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs:line 117
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 221
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 485
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 227
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitConditional(ConditionalExpression conditionalExpression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 270
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 227
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 414
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node) in /_/src/libraries/System.Linq.Expressions/src/System/Linq/Expressions/ExpressionVisitor.cs:line 594
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 452
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 243
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 414
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node) in /_/src/libraries/System.Linq.Expressions/src/System/Linq/Expressions/ExpressionVisitor.cs:line 594
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 452
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 243
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs:line 81
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 863
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore/Query/QueryableMethodTranslatingExpressionVisitor.cs:line 407
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) in /_/src/EFCore/Query/QueryCompilationContext.cs:line 189
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) in /_/src/EFCore/Storage/Database.cs:line 76
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) in /_/src/EFCore/Query/Internal/QueryCompiler.cs:line 111
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0() in /_/src/EFCore/Query/Internal/QueryCompiler.cs:line 95
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) in /_/src/EFCore/Query/Internal/CompiledQueryCache.cs:line 74
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in /_/src/EFCore/Query/Internal/QueryCompiler.cs:line 91
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in /_/src/EFCore/Query/Internal/EntityQueryProvider.cs:line 78
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator() in /_/src/EFCore/Query/Internal/EntityQueryable`.cs:line 90
   at TimeSheet.API.Service.ConsulenteRepository.<Get>d__6.MoveNext() in D:\github\temp\TimeSheetApp-Net6\BookStore-API\Service\ConsulenteRepository.cs:line 104

Here's the Linq expression "C#" notation:

--
-- Not implemented - NodeType: Extension not implemented.
--.Include((Consulente b) => b.N_Consulente_Commessa).ThenInclude((Consulente_Commessa x) => x.Commessa).Select((Consulente dtoConsulente) => new ConsulenteDto() {
    AddedDate = dtoConsulente.AddedDate,
    CodiceFiscale = dtoConsulente.CodiceFiscale,
    Email = dtoConsulente.Email,
    Id = dtoConsulente.Id,
    ModifiedDate = dtoConsulente.ModifiedDate,
    N_CaricamentiTimeSheet = dtoConsulente.N_CaricamentiTimeSheet.Select((Time_Sheet dtoTime_Sheet) => new TimeSheetDto() {
        AddedDate = dtoTime_Sheet.AddedDate,
        Anno = dtoTime_Sheet.Anno,
        ConsulenteId = dtoTime_Sheet.ConsulenteId,
        FileExcel = dtoTime_Sheet.ExcelPath,
        Id = dtoTime_Sheet.Id,
        Mese = dtoTime_Sheet.Mese,
        ModifiedDate = dtoTime_Sheet.ModifiedDate,
        RowTimeSheetDetail = dtoTime_Sheet.RowTimeSheetDetail.Select((RowTimeSheet dtoRowTimeSheet) => new RowTimeSheetDto() {
            AddedDate = dtoRowTimeSheet.AddedDate,
            Giorno = dtoRowTimeSheet.Giorno,
            Id = dtoRowTimeSheet.Id,
            ModifiedDate = dtoRowTimeSheet.ModifiedDate,
            N_Commessa_RowTimeSheet = dtoRowTimeSheet.N_Commessa_RowTimeSheet.Select((Commessa_RowTimeSheet dtoCommessa_RowTimeSheet) => new Commessa_RowTimeSheetDto() {
                Commessa = dtoCommessa_RowTimeSheet.Commessa == null ? null : new CommessaDto() {
                    AddedDate = dtoCommessa_RowTimeSheet.Commessa.AddedDate,
                    CommessaCompletata = dtoCommessa_RowTimeSheet.Commessa.CommessaCompletata,
                    Id = dtoCommessa_RowTimeSheet.Commessa.Id,
                    ModifiedDate = dtoCommessa_RowTimeSheet.Commessa.ModifiedDate,
                    N_Consulente_Commessa = dtoCommessa_RowTimeSheet.Commessa.N_Consulente_Commessa.Select((Consulente_Commessa dtoConsulente_Commessa) => new Consulente_CommessaDto() {
                        AddedDate = dtoConsulente_Commessa.AddedDate,
                        CommessaId = dtoConsulente_Commessa.CommessaId,
                        ConsulenteId = dtoConsulente_Commessa.ConsulenteId,
                        Id = dtoConsulente_Commessa.Id,
                        ModifiedDate = dtoConsulente_Commessa.ModifiedDate
                    }).ToList(),
                    Name = dtoCommessa_RowTimeSheet.Commessa.Name
                },
                CommessaId = dtoCommessa_RowTimeSheet.CommessaId,
                OreOrdinarie = dtoCommessa_RowTimeSheet.OreOrdinarie,
                OreStraordinarie = dtoCommessa_RowTimeSheet.OreStraordinarie,
                RowTimeSheetId = dtoCommessa_RowTimeSheet.RowTimeSheetId
            }).ToList(),
            OreFerie_Permessi = dtoRowTimeSheet.OreFerie_Permessi,
            TimeSheetCaricamentoId = dtoRowTimeSheet.TimeSheetCaricamentoId
        }).ToList()
    }).ToList(),
    N_Consulente_Commessa = dtoConsulente.N_Consulente_Commessa.Select((Consulente_Commessa dtoConsulente_Commessa) => new Consulente_CommessaDto() {
        AddedDate = dtoConsulente_Commessa.AddedDate,
        Commessa = dtoConsulente_Commessa.Commessa == null ? null : new CommessaDto() {
            AddedDate = dtoConsulente_Commessa.Commessa.AddedDate,
            CommessaCompletata = dtoConsulente_Commessa.Commessa.CommessaCompletata,
            Id = dtoConsulente_Commessa.Commessa.Id,
            ModifiedDate = dtoConsulente_Commessa.Commessa.ModifiedDate,
            N_Commessa_RowTimesheet = dtoConsulente_Commessa.Commessa.N_Commessa_RowTimesheet.Select((Commessa_RowTimeSheet dtoCommessa_RowTimeSheet) => new Commessa_RowTimeSheetDto() {
                CommessaId = dtoCommessa_RowTimeSheet.CommessaId,
                OreOrdinarie = dtoCommessa_RowTimeSheet.OreOrdinarie,
                OreStraordinarie = dtoCommessa_RowTimeSheet.OreStraordinarie,
                RowTimeSheet = dtoCommessa_RowTimeSheet.RowTimeSheet == null ? null : new RowTimeSheetDto() {
                    AddedDate = dtoCommessa_RowTimeSheet.RowTimeSheet.AddedDate,
                    Giorno = dtoCommessa_RowTimeSheet.RowTimeSheet.Giorno,
                    Id = dtoCommessa_RowTimeSheet.RowTimeSheet.Id,
                    ModifiedDate = dtoCommessa_RowTimeSheet.RowTimeSheet.ModifiedDate,
                    OreFerie_Permessi = dtoCommessa_RowTimeSheet.RowTimeSheet.OreFerie_Permessi,
                    TimeSheetCaricamento = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento == null ? null : new TimeSheetDto() {
                        AddedDate = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento.AddedDate,
                        Anno = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento.Anno,
                        ConsulenteId = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento.ConsulenteId,
                        FileExcel = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento.ExcelPath,
                        Id = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento.Id,
                        Mese = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento.Mese,
                        ModifiedDate = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento.ModifiedDate,
                        RowTimeSheetDetail = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamento.RowTimeSheetDetail.Select((RowTimeSheet dtoRowTimeSheet) => new RowTimeSheetDto() {
                            AddedDate = dtoRowTimeSheet.AddedDate,
                            Giorno = dtoRowTimeSheet.Giorno,
                            Id = dtoRowTimeSheet.Id,
                            ModifiedDate = dtoRowTimeSheet.ModifiedDate,
                            OreFerie_Permessi = dtoRowTimeSheet.OreFerie_Permessi,
                            TimeSheetCaricamentoId = dtoRowTimeSheet.TimeSheetCaricamentoId
                        }).ToList()
                    },
                    TimeSheetCaricamentoId = dtoCommessa_RowTimeSheet.RowTimeSheet.TimeSheetCaricamentoId
                },
                RowTimeSheetId = dtoCommessa_RowTimeSheet.RowTimeSheetId
            }).ToList(),
            Name = dtoConsulente_Commessa.Commessa.Name
        },
        CommessaId = dtoConsulente_Commessa.CommessaId,
        ConsulenteId = dtoConsulente_Commessa.ConsulenteId,
        Id = dtoConsulente_Commessa.Id,
        ModifiedDate = dtoConsulente_Commessa.ModifiedDate
    }).ToList(),
    Name = dtoConsulente.FirstName,
    NumeroMatricola = dtoConsulente.NumeroMatricola,
    Surname = dtoConsulente.LastName
}).Select((ConsulenteDto $it) => new SelectAllAndExpand<ConsulenteDto>() {
    Model = #TypedLinqParameterContainer<IEdmModel>.TypedProperty,
    Instance = $it,
    UseInstanceForProperties = true,
    Container = new NamedProperty<IEnumerable<SelectAll<Consulente_CommessaDto>>>() {
        Name = "N_Consulente_Commessa",
        Value = ($it == null ? null : $it.N_Consulente_Commessa == null ? null : $it.N_Consulente_Commessa.Where((Consulente_CommessaDto $it) => $it.Id == #TypedLinqParameterContainer<int>.TypedProperty)) == null ? null : ($it == null ? null : $it.N_Consulente_Commessa == null ? null : $it.N_Consulente_Commessa.Where((Consulente_CommessaDto $it) => $it.Id == #TypedLinqParameterContainer<int>.TypedProperty)).Select((Consulente_CommessaDto $it) => new SelectAll<Consulente_CommessaDto>() {
            Model = #TypedLinqParameterContainer<IEdmModel>.TypedProperty,
            Instance = $it,
            UseInstanceForProperties = true
        })
    }
})

Here's Linq Expression debug view:

.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Select(
        .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ThenInclude(
            .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.Include(
                .Extension<Microsoft.EntityFrameworkCore.Query.QueryRootExpression>,
                '(.Lambda #Lambda1<System.Func`2[TimeSheet.API.Models.Entity.Consulente,System.Collections.Generic.ICollection`1[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa]]>))
            ,
            '(.Lambda #Lambda2<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Entity.Commessa]>))
        ,
        '(.Lambda #Lambda3<System.Func`2[TimeSheet.API.Models.Entity.Consulente,TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto]>))
    ,
    '(.Lambda #Lambda11<System.Func`2[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAllAndExpand`1[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto]]>))

.Lambda #Lambda1<System.Func`2[TimeSheet.API.Models.Entity.Consulente,System.Collections.Generic.ICollection`1[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa]]>(TimeSheet.API.Models.Entity.Consulente $b)
{
    $b.N_Consulente_Commessa
}

.Lambda #Lambda2<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Entity.Commessa]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa $x)
{
    $x.Commessa
}

.Lambda #Lambda3<System.Func`2[TimeSheet.API.Models.Entity.Consulente,TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto]>(TimeSheet.API.Models.Entity.Consulente $dtoConsulente)
{
    .New TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoConsulente.AddedDate,
        CodiceFiscale = $dtoConsulente.CodiceFiscale,
        Email = $dtoConsulente.Email,
        Id = $dtoConsulente.Id,
        ModifiedDate = $dtoConsulente.ModifiedDate,
        N_CaricamentiTimeSheet = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                $dtoConsulente.N_CaricamentiTimeSheet,
                .Lambda #Lambda4<System.Func`2[TimeSheet.API.Models.Entity.Time_Sheet,TimeSheet.API.Models.Dto.Api.TimeSheetF.TimeSheetDto]>)),
        N_Consulente_Commessa = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                $dtoConsulente.N_Consulente_Commessa,
                .Lambda #Lambda8<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]>)
        ),
        Name = $dtoConsulente.FirstName,
        NumeroMatricola = $dtoConsulente.NumeroMatricola,
        Surname = $dtoConsulente.LastName
    }
}

.Lambda #Lambda4<System.Func`2[TimeSheet.API.Models.Entity.Time_Sheet,TimeSheet.API.Models.Dto.Api.TimeSheetF.TimeSheetDto]>(TimeSheet.API.Models.Entity.Time_Sheet $dtoTime_Sheet)
{
    .New TimeSheet.API.Models.Dto.Api.TimeSheetF.TimeSheetDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoTime_Sheet.AddedDate,
        Anno = $dtoTime_Sheet.Anno,
        ConsulenteId = $dtoTime_Sheet.ConsulenteId,
        FileExcel = $dtoTime_Sheet.ExcelPath,
        Id = $dtoTime_Sheet.Id,
        Mese = $dtoTime_Sheet.Mese,
        ModifiedDate = $dtoTime_Sheet.ModifiedDate,
        RowTimeSheetDetail = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                $dtoTime_Sheet.RowTimeSheetDetail,
                .Lambda #Lambda5<System.Func`2[TimeSheet.API.Models.Entity.RowTimeSheet,TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto]>)
        )
    }
}

.Lambda #Lambda5<System.Func`2[TimeSheet.API.Models.Entity.RowTimeSheet,TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto]>(TimeSheet.API.Models.Entity.RowTimeSheet $dtoRowTimeSheet)
{
    .New TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoRowTimeSheet.AddedDate,
        Giorno = $dtoRowTimeSheet.Giorno,
        Id = $dtoRowTimeSheet.Id,
        ModifiedDate = $dtoRowTimeSheet.ModifiedDate,
        N_Commessa_RowTimeSheet = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                $dtoRowTimeSheet.N_Commessa_RowTimeSheet,
                .Lambda #Lambda6<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto]>)
        ),
        OreFerie_Permessi = $dtoRowTimeSheet.OreFerie_Permessi,
        TimeSheetCaricamentoId = $dtoRowTimeSheet.TimeSheetCaricamentoId
    }
}

.Lambda #Lambda6<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet $dtoCommessa_RowTimeSheet)
{
    .New TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto(){
        Commessa = .If ($dtoCommessa_RowTimeSheet.Commessa == null) {
            null
        } .Else {
            .New TimeSheet.API.Models.Dto.Api.CommessaF.CommessaDto(){
                AddedDate = (System.Nullable`1[System.DateTime])($dtoCommessa_RowTimeSheet.Commessa).AddedDate,
                CommessaCompletata = ($dtoCommessa_RowTimeSheet.Commessa).CommessaCompletata,
                Id = ($dtoCommessa_RowTimeSheet.Commessa).Id,
                ModifiedDate = ($dtoCommessa_RowTimeSheet.Commessa).ModifiedDate,
                N_Consulente_Commessa = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                        ($dtoCommessa_RowTimeSheet.Commessa).N_Consulente_Commessa,
                        .Lambda #Lambda7<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]>)
                ),
                Name = ($dtoCommessa_RowTimeSheet.Commessa).Name
            }
        },
        CommessaId = $dtoCommessa_RowTimeSheet.CommessaId,
        OreOrdinarie = $dtoCommessa_RowTimeSheet.OreOrdinarie,
        OreStraordinarie = $dtoCommessa_RowTimeSheet.OreStraordinarie,
        RowTimeSheetId = $dtoCommessa_RowTimeSheet.RowTimeSheetId
    }
}

.Lambda #Lambda7<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa $dtoConsulente_Commessa)
{
    .New TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoConsulente_Commessa.AddedDate,
        CommessaId = $dtoConsulente_Commessa.CommessaId,
        ConsulenteId = $dtoConsulente_Commessa.ConsulenteId,
        Id = $dtoConsulente_Commessa.Id,
        ModifiedDate = $dtoConsulente_Commessa.ModifiedDate
    }
}

.Lambda #Lambda8<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa $dtoConsulente_Commessa)
{
    .New TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoConsulente_Commessa.AddedDate,
        Commessa = .If ($dtoConsulente_Commessa.Commessa == null) {
            null
        } .Else {
            .New TimeSheet.API.Models.Dto.Api.CommessaF.CommessaDto(){
                AddedDate = (System.Nullable`1[System.DateTime])($dtoConsulente_Commessa.Commessa).AddedDate,
                CommessaCompletata = ($dtoConsulente_Commessa.Commessa).CommessaCompletata,
                Id = ($dtoConsulente_Commessa.Commessa).Id,
                ModifiedDate = ($dtoConsulente_Commessa.Commessa).ModifiedDate,
                N_Commessa_RowTimesheet = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                        ($dtoConsulente_Commessa.Commessa).N_Commessa_RowTimesheet,
                        .Lambda #Lambda9<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto]>)
                ),
                Name = ($dtoConsulente_Commessa.Commessa).Name
            }
        },
        CommessaId = $dtoConsulente_Commessa.CommessaId,
        ConsulenteId = $dtoConsulente_Commessa.ConsulenteId,
        Id = $dtoConsulente_Commessa.Id,
        ModifiedDate = $dtoConsulente_Commessa.ModifiedDate
    }
}

.Lambda #Lambda9<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet $dtoCommessa_RowTimeSheet)
{
    .New TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto(){
        CommessaId = $dtoCommessa_RowTimeSheet.CommessaId,
        OreOrdinarie = $dtoCommessa_RowTimeSheet.OreOrdinarie,
        OreStraordinarie = $dtoCommessa_RowTimeSheet.OreStraordinarie,
        RowTimeSheet = .If ($dtoCommessa_RowTimeSheet.RowTimeSheet == null) {
            null
        } .Else {
            .New TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto(){
                AddedDate = (System.Nullable`1[System.DateTime])($dtoCommessa_RowTimeSheet.RowTimeSheet).AddedDate,
                Giorno = ($dtoCommessa_RowTimeSheet.RowTimeSheet).Giorno,
                Id = ($dtoCommessa_RowTimeSheet.RowTimeSheet).Id,
                ModifiedDate = ($dtoCommessa_RowTimeSheet.RowTimeSheet).ModifiedDate,
                OreFerie_Permessi = ($dtoCommessa_RowTimeSheet.RowTimeSheet).OreFerie_Permessi,
                TimeSheetCaricamento = .If (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento == null) {
                    null
                } .Else {
                    .New TimeSheet.API.Models.Dto.Api.TimeSheetF.TimeSheetDto(){
                        AddedDate = (System.Nullable`1[System.DateTime])(($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).AddedDate,
                        Anno = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).Anno,
                        ConsulenteId = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).ConsulenteId,
                        FileExcel = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).ExcelPath,
                        Id = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).Id,
                        Mese = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).Mese,
                        ModifiedDate = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).ModifiedDate,
                        RowTimeSheetDetail = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                                (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).RowTimeSheetDetail,
                                .Lambda #Lambda10<System.Func`2[TimeSheet.API.Models.Entity.RowTimeSheet,TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto]>))
                    }
                },
                TimeSheetCaricamentoId = ($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamentoId
            }
        },
        RowTimeSheetId = $dtoCommessa_RowTimeSheet.RowTimeSheetId
    }
}

.Lambda #Lambda10<System.Func`2[TimeSheet.API.Models.Entity.RowTimeSheet,TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto]>(TimeSheet.API.Models.Entity.RowTimeSheet $dtoRowTimeSheet)
{
    .New TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoRowTimeSheet.AddedDate,
        Giorno = $dtoRowTimeSheet.Giorno,
        Id = $dtoRowTimeSheet.Id,
        ModifiedDate = $dtoRowTimeSheet.ModifiedDate,
        OreFerie_Permessi = $dtoRowTimeSheet.OreFerie_Permessi,
        TimeSheetCaricamentoId = $dtoRowTimeSheet.TimeSheetCaricamentoId
    }
}

.Lambda #Lambda11<System.Func`2[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAllAndExpand`1[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto]]>(TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto $$it)
{
    .New Microsoft.AspNetCore.OData.Query.Wrapper.SelectAllAndExpand`1[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto](){
        Model = .Constant<Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]>(Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]).TypedProperty,
        Instance = $$it,
        UseInstanceForProperties = True,
        Container = .New Microsoft.AspNetCore.OData.Query.Container.NamedProperty`1[System.Collections.Generic.IEnumerable`1[Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]]]()
        {
            Name = "N_Consulente_Commessa",
            Value = .If (.If ($$it == null) {
                null
            } .Else {
                .If ($$it.N_Consulente_Commessa == null) {
                    null
                } .Else {
                    .Call System.Linq.Enumerable.Where(
                        $$it.N_Consulente_Commessa,
                        .Lambda #Lambda12<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,System.Boolean]>)
                }
            } == null) {
                null
            } .Else {
                .Call System.Linq.Enumerable.Select(
                    .If ($$it == null) {
                        null
                    } .Else {
                        .If ($$it.N_Consulente_Commessa == null) {
                            null
                        } .Else {
                            .Call System.Linq.Enumerable.Where(
                                $$it.N_Consulente_Commessa,
                                .Lambda #Lambda12<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,System.Boolean]>)
                        }
                    },
                    .Lambda #Lambda13<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]]>)
            }
        }
    }
}

.Lambda #Lambda12<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,System.Boolean]>(TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto $$it)
{
    $$it.Id == .Constant<Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]>(Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty
}

.Lambda #Lambda13<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]]>(TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto $$it)
{
    .New Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]()
    {
        Model = .Constant<Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]>(Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]).TypedProperty,
        Instance = $$it,
        UseInstanceForProperties = True
    }
}

Welcome any hint. If you need more detail, please contact 'Sam Xu' at "[email protected]. Thanks.

AndreaTazoPellegrini · 2022-02-16T20:09:13Z

This is a Entity Relationship of project if it can help.
Job is a "Commessa" class and Consultant is "Consulente" class

xuzhg · 2022-02-17T21:09:30Z

Updated:

If i enabled 'HandleNullPropagation = false', it works fine.

@maumar Why does "nullable" can't work? Or I misunderstand something?

var settings = new ODataQuerySettings { HandleNullPropagation = HandleNullPropagationOption.False };
var queried = ODataQuery.ApplyTo(baseQuery, settings) as IQueryable<dynamic>;

Here's the debug view (This is working linq expression).

.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Select(
        .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ThenInclude(
            .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.Include(
                .Extension<Microsoft.EntityFrameworkCore.Query.QueryRootExpression>,
                '(.Lambda #Lambda1<System.Func`2[TimeSheet.API.Models.Entity.Consulente,System.Collections.Generic.ICollection`1[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa]]>))
            ,
            '(.Lambda #Lambda2<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Entity.Commessa]>))
        ,
        '(.Lambda #Lambda3<System.Func`2[TimeSheet.API.Models.Entity.Consulente,TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto]>))
    ,
    '(.Lambda #Lambda11<System.Func`2[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAllAndExpand`1[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto]]>))

.Lambda #Lambda1<System.Func`2[TimeSheet.API.Models.Entity.Consulente,System.Collections.Generic.ICollection`1[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa]]>(TimeSheet.API.Models.Entity.Consulente $b)
{
    $b.N_Consulente_Commessa
}

.Lambda #Lambda2<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Entity.Commessa]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa $x)
{
    $x.Commessa
}

.Lambda #Lambda3<System.Func`2[TimeSheet.API.Models.Entity.Consulente,TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto]>(TimeSheet.API.Models.Entity.Consulente $dtoConsulente)
{
    .New TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoConsulente.AddedDate,
        CodiceFiscale = $dtoConsulente.CodiceFiscale,
        Email = $dtoConsulente.Email,
        Id = $dtoConsulente.Id,
        ModifiedDate = $dtoConsulente.ModifiedDate,
        N_CaricamentiTimeSheet = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                $dtoConsulente.N_CaricamentiTimeSheet,
                .Lambda #Lambda4<System.Func`2[TimeSheet.API.Models.Entity.Time_Sheet,TimeSheet.API.Models.Dto.Api.TimeSheetF.TimeSheetDto]>)),
        N_Consulente_Commessa = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                $dtoConsulente.N_Consulente_Commessa,
                .Lambda #Lambda8<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]>)
        ),
        Name = $dtoConsulente.FirstName,
        NumeroMatricola = $dtoConsulente.NumeroMatricola,
        Surname = $dtoConsulente.LastName
    }
}

.Lambda #Lambda4<System.Func`2[TimeSheet.API.Models.Entity.Time_Sheet,TimeSheet.API.Models.Dto.Api.TimeSheetF.TimeSheetDto]>(TimeSheet.API.Models.Entity.Time_Sheet $dtoTime_Sheet)
{
    .New TimeSheet.API.Models.Dto.Api.TimeSheetF.TimeSheetDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoTime_Sheet.AddedDate,
        Anno = $dtoTime_Sheet.Anno,
        ConsulenteId = $dtoTime_Sheet.ConsulenteId,
        FileExcel = $dtoTime_Sheet.ExcelPath,
        Id = $dtoTime_Sheet.Id,
        Mese = $dtoTime_Sheet.Mese,
        ModifiedDate = $dtoTime_Sheet.ModifiedDate,
        RowTimeSheetDetail = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                $dtoTime_Sheet.RowTimeSheetDetail,
                .Lambda #Lambda5<System.Func`2[TimeSheet.API.Models.Entity.RowTimeSheet,TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto]>)
        )
    }
}

.Lambda #Lambda5<System.Func`2[TimeSheet.API.Models.Entity.RowTimeSheet,TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto]>(TimeSheet.API.Models.Entity.RowTimeSheet $dtoRowTimeSheet)
{
    .New TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoRowTimeSheet.AddedDate,
        Giorno = $dtoRowTimeSheet.Giorno,
        Id = $dtoRowTimeSheet.Id,
        ModifiedDate = $dtoRowTimeSheet.ModifiedDate,
        N_Commessa_RowTimeSheet = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                $dtoRowTimeSheet.N_Commessa_RowTimeSheet,
                .Lambda #Lambda6<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto]>)
        ),
        OreFerie_Permessi = $dtoRowTimeSheet.OreFerie_Permessi,
        TimeSheetCaricamentoId = $dtoRowTimeSheet.TimeSheetCaricamentoId
    }
}

.Lambda #Lambda6<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet $dtoCommessa_RowTimeSheet)
{
    .New TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto(){
        Commessa = .If ($dtoCommessa_RowTimeSheet.Commessa == null) {
            null
        } .Else {
            .New TimeSheet.API.Models.Dto.Api.CommessaF.CommessaDto(){
                AddedDate = (System.Nullable`1[System.DateTime])($dtoCommessa_RowTimeSheet.Commessa).AddedDate,
                CommessaCompletata = ($dtoCommessa_RowTimeSheet.Commessa).CommessaCompletata,
                Id = ($dtoCommessa_RowTimeSheet.Commessa).Id,
                ModifiedDate = ($dtoCommessa_RowTimeSheet.Commessa).ModifiedDate,
                N_Consulente_Commessa = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                        ($dtoCommessa_RowTimeSheet.Commessa).N_Consulente_Commessa,
                        .Lambda #Lambda7<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]>)
                ),
                Name = ($dtoCommessa_RowTimeSheet.Commessa).Name
            }
        },
        CommessaId = $dtoCommessa_RowTimeSheet.CommessaId,
        OreOrdinarie = $dtoCommessa_RowTimeSheet.OreOrdinarie,
        OreStraordinarie = $dtoCommessa_RowTimeSheet.OreStraordinarie,
        RowTimeSheetId = $dtoCommessa_RowTimeSheet.RowTimeSheetId
    }
}

.Lambda #Lambda7<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa $dtoConsulente_Commessa)
{
    .New TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoConsulente_Commessa.AddedDate,
        CommessaId = $dtoConsulente_Commessa.CommessaId,
        ConsulenteId = $dtoConsulente_Commessa.ConsulenteId,
        Id = $dtoConsulente_Commessa.Id,
        ModifiedDate = $dtoConsulente_Commessa.ModifiedDate
    }
}

.Lambda #Lambda8<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Consulente_Commessa $dtoConsulente_Commessa)
{
    .New TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoConsulente_Commessa.AddedDate,
        Commessa = .If ($dtoConsulente_Commessa.Commessa == null) {
            null
        } .Else {
            .New TimeSheet.API.Models.Dto.Api.CommessaF.CommessaDto(){
                AddedDate = (System.Nullable`1[System.DateTime])($dtoConsulente_Commessa.Commessa).AddedDate,
                CommessaCompletata = ($dtoConsulente_Commessa.Commessa).CommessaCompletata,
                Id = ($dtoConsulente_Commessa.Commessa).Id,
                ModifiedDate = ($dtoConsulente_Commessa.Commessa).ModifiedDate,
                N_Commessa_RowTimesheet = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                        ($dtoConsulente_Commessa.Commessa).N_Commessa_RowTimesheet,
                        .Lambda #Lambda9<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto]>)
                ),
                Name = ($dtoConsulente_Commessa.Commessa).Name
            }
        },
        CommessaId = $dtoConsulente_Commessa.CommessaId,
        ConsulenteId = $dtoConsulente_Commessa.ConsulenteId,
        Id = $dtoConsulente_Commessa.Id,
        ModifiedDate = $dtoConsulente_Commessa.ModifiedDate
    }
}

.Lambda #Lambda9<System.Func`2[TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet,TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto]>(TimeSheet.API.Models.Entity.ThirdTableRelationship.Commessa_RowTimeSheet $dtoCommessa_RowTimeSheet)
{
    .New TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Commessa_RowTimeSheetDto(){
        CommessaId = $dtoCommessa_RowTimeSheet.CommessaId,
        OreOrdinarie = $dtoCommessa_RowTimeSheet.OreOrdinarie,
        OreStraordinarie = $dtoCommessa_RowTimeSheet.OreStraordinarie,
        RowTimeSheet = .If ($dtoCommessa_RowTimeSheet.RowTimeSheet == null) {
            null
        } .Else {
            .New TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto(){
                AddedDate = (System.Nullable`1[System.DateTime])($dtoCommessa_RowTimeSheet.RowTimeSheet).AddedDate,
                Giorno = ($dtoCommessa_RowTimeSheet.RowTimeSheet).Giorno,
                Id = ($dtoCommessa_RowTimeSheet.RowTimeSheet).Id,
                ModifiedDate = ($dtoCommessa_RowTimeSheet.RowTimeSheet).ModifiedDate,
                OreFerie_Permessi = ($dtoCommessa_RowTimeSheet.RowTimeSheet).OreFerie_Permessi,
                TimeSheetCaricamento = .If (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento == null) {
                    null
                } .Else {
                    .New TimeSheet.API.Models.Dto.Api.TimeSheetF.TimeSheetDto(){
                        AddedDate = (System.Nullable`1[System.DateTime])(($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).AddedDate,
                        Anno = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).Anno,
                        ConsulenteId = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).ConsulenteId,
                        FileExcel = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).ExcelPath,
                        Id = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).Id,
                        Mese = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).Mese,
                        ModifiedDate = (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).ModifiedDate,
                        RowTimeSheetDetail = .Call System.Linq.Enumerable.ToList(.Call System.Linq.Enumerable.Select(
                                (($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamento).RowTimeSheetDetail,
                                .Lambda #Lambda10<System.Func`2[TimeSheet.API.Models.Entity.RowTimeSheet,TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto]>))
                    }
                },
                TimeSheetCaricamentoId = ($dtoCommessa_RowTimeSheet.RowTimeSheet).TimeSheetCaricamentoId
            }
        },
        RowTimeSheetId = $dtoCommessa_RowTimeSheet.RowTimeSheetId
    }
}

.Lambda #Lambda10<System.Func`2[TimeSheet.API.Models.Entity.RowTimeSheet,TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto]>(TimeSheet.API.Models.Entity.RowTimeSheet $dtoRowTimeSheet)
{
    .New TimeSheet.API.Models.Dto.Api.RowTimeSheetF.RowTimeSheetDto(){
        AddedDate = (System.Nullable`1[System.DateTime])$dtoRowTimeSheet.AddedDate,
        Giorno = $dtoRowTimeSheet.Giorno,
        Id = $dtoRowTimeSheet.Id,
        ModifiedDate = $dtoRowTimeSheet.ModifiedDate,
        OreFerie_Permessi = $dtoRowTimeSheet.OreFerie_Permessi,
        TimeSheetCaricamentoId = $dtoRowTimeSheet.TimeSheetCaricamentoId
    }
}

.Lambda #Lambda11<System.Func`2[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAllAndExpand`1[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto]]>(TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto $$it)
{
    .New Microsoft.AspNetCore.OData.Query.Wrapper.SelectAllAndExpand`1[TimeSheet.API.Models.Dto.Api.ConsulenteF.ConsulenteDto](){
        Model = .Constant<Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]>(Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]).TypedProperty,
        Instance = $$it,
        UseInstanceForProperties = True,
        Container = .New Microsoft.AspNetCore.OData.Query.Container.NamedProperty`1[System.Collections.Generic.IEnumerable`1[Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]]]()
        {
            Name = "N_Consulente_Commessa",
            Value = .Call System.Linq.Enumerable.Select(
                .Call System.Linq.Enumerable.Where(
                    $$it.N_Consulente_Commessa,
                    .Lambda #Lambda12<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,System.Boolean]>),
                .Lambda #Lambda13<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]]>)
        }
    }
}

.Lambda #Lambda12<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,System.Boolean]>(TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto $$it)
{
    $$it.CommessaId < .Constant<Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]>(Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty
}

.Lambda #Lambda13<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]]>(TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto $$it)
{
    .New Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]()
    {
        Model = .Constant<Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]>(Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]).TypedProperty,
        Instance = $$it,
        UseInstanceForProperties = True
    }
}

maumar · 2022-02-18T02:57:41Z

with null propagation:

            Value = .If (.If ($$it == null) {
                null
            } .Else {
                .If ($$it.N_Consulente_Commessa == null) {
                    null
                } .Else {
                    .Call System.Linq.Enumerable.Where(
                        $$it.N_Consulente_Commessa,
                        .Lambda #Lambda12<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,System.Boolean]>)
                }
            } == null) {
                null
            } .Else {
                .Call System.Linq.Enumerable.Select(
                    .If ($$it == null) {
                        null
                    } .Else {
                        .If ($$it.N_Consulente_Commessa == null) {
                            null
                        } .Else {
                            .Call System.Linq.Enumerable.Where(
                                $$it.N_Consulente_Commessa,
                                .Lambda #Lambda12<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,System.Boolean]>)
                        }
                    },
                    .Lambda #Lambda13<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]]>)
            }

without null propagation:

            Value = .Call System.Linq.Enumerable.Select(
                .Call System.Linq.Enumerable.Where(
                    $$it.N_Consulente_Commessa,
                    .Lambda #Lambda12<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,System.Boolean]>),
                .Lambda #Lambda13<System.Func`2[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto,Microsoft.AspNetCore.OData.Query.Wrapper.SelectAll`1[TimeSheet.API.Models.Dto.Api.ThirdTableRelationship.Consulente_CommessaDto]]>)
        }

OData is adding these extra terms for null propagation which complicates the query to a point that we can't translate it (at the moment). We should try to recognize/simplify these patterns, although they are largely unnecessary - EF Core is pretty good with handling null propagation itself

xuzhg · 2022-02-18T05:49:39Z

@maumar Thanks for your input.

We have below comments for the Null propagation setting for a perid of time, So, it seems it's still a problem in EF Core 6.0?

https://github.com/OData/AspNetCoreOData/blob/main/src/Microsoft.AspNetCore.OData/Query/HandleNullPropagationOptionHelper.cs#L53-L58

maumar · 2022-02-19T03:31:58Z

@xuzhg in EFCore 5 and 6 we have improved a number of translations involving projections and composing on top of them. It's possible that some scenarios that OData produces are still broken - let me add some tests in out OData suite and see how things are now.

Basically, before EF Core 3 we used to do a lot of client evaluations, if we didn't know how to translate something, we would fall back to in memory and then stitch the results together. In newer versions, we don't allow that anymore, apart from projection, but we got much better at translating more patterns - now we try to translate everything and if we can't, we throw.

here is an example of translation(s) that have been enabled recently:

#15279

xuzhg added the customer-reported label Feb 16, 2022

maumar self-assigned this Feb 17, 2022

ajcvickers modified the milestones: 7.0.0, Backlog Feb 18, 2022

ajcvickers added type-bug area-query labels Feb 18, 2022

xuzhg mentioned this issue May 28, 2024

$expand fails with DTO - EF cannot translate the query expression OData/AspNetCoreOData#1245

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

OData Linq Expression not be translated issue using automapper #27460

OData Linq Expression not be translated issue using automapper #27460

xuzhg commented Feb 16, 2022

AndreaTazoPellegrini commented Feb 16, 2022 •

edited

Loading

xuzhg commented Feb 17, 2022 •

edited

Loading

maumar commented Feb 18, 2022

xuzhg commented Feb 18, 2022

maumar commented Feb 19, 2022

OData Linq Expression not be translated issue using automapper #27460

OData Linq Expression not be translated issue using automapper #27460

Comments

xuzhg commented Feb 16, 2022

File a bug

AndreaTazoPellegrini commented Feb 16, 2022 • edited Loading

xuzhg commented Feb 17, 2022 • edited Loading

maumar commented Feb 18, 2022

xuzhg commented Feb 18, 2022

maumar commented Feb 19, 2022

AndreaTazoPellegrini commented Feb 16, 2022 •

edited

Loading

xuzhg commented Feb 17, 2022 •

edited

Loading