search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.74k stars 3.18k forks source link

OData Linq Expression not be translated issue using automapper #27460

Open xuzhg opened 2 years ago

xuzhg commented 2 years ago

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": "admin@api.com",
        "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": "user2@api.com",
        "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:

image

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 "saxu@microsoft.com. Thanks.

AndreaTazoPellegrini commented 2 years ago

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

xuzhg commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

@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

image

maumar commented 2 years ago

@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:

https://github.com/dotnet/efcore/issues/15279