ExplicitExpansion() cause OData expand not work

Question

When using ExplicitExpansion() Odata expand does not work. My DTo and EF Models can be found below link. Querying DTOs based on EF using Odata

My Automapper class:

public class AutoMapperProfile : Profile
{
    public AutoMapperProfile()
    {
        CreateMap<ClientRef, ClientContract>().
        ForMember(dest => dest.ValidFrom,
        opt =>
        {
            opt.MapFrom(y => y.Clients.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).ValidFrom);
        }).
        ForMember(dest => dest.ValidTo,
        opt =>
        {
            opt.MapFrom(y => y.Clients.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).ValidTo);
        }).
       ForMember(dest => dest.FirstName,
        opt =>
        {
            opt.MapFrom(y => y.PhysicalPeople.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).FirstName);
        }).
        ForMember(dest => dest.LastName,
        opt =>
        {
            opt.MapFrom(y => y.PhysicalPeople.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).LastName);
        }).
        ForMember(dest => dest.BirthDate,
        opt =>
        {
            opt.MapFrom(y => y.PhysicalPeople.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).BirthDate);
        }).
        ForMember(dest => dest.FatherName,
        opt =>
        {
            opt.MapFrom(y => y.PhysicalPeople.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).FatherName);
        }).
        ForMember(dest => dest.CompanyName,
        opt =>
        {
            opt.MapFrom(y => y.Companies.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).CompanyName);

        })
        .
        ForMember(dest => dest.PinNumber,
        opt =>
        {
            opt.MapFrom(y => y.PhysicalPeople.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).Pin);

        }).
        ForMember(dest => dest.Position,
        opt =>
        {
            opt.MapFrom(y => y.PhysicalPeople.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).Position);

        }).
        ForMember(dest => dest.PositionCustom,
        opt =>
        {
            opt.MapFrom(y => y.PhysicalPeople.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).PositionCustom);

        }).
        ForMember(dest => dest.ClientType,
        opt =>
        {
            opt.MapFrom(y => y.Clients.FirstOrDefault(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).ClientType);

        })
        .
        ForMember(dest => dest.Documents,
        opt =>
        {
            opt.MapFrom(y => y.Documents.Where(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now));
            //opt.ExplicitExpansion();
        })
        .ForMember(dest => dest.ContactsInfo,
        opt =>
        {
            opt.MapFrom(y => y.ClientContactInfoComps.Where(x => x.ValidFrom <= DateTime.Now && x.ValidTo > DateTime.Now).Select(x => x.ContactInfo));
            //opt.ExplicitExpansion();
        }).
        ForMember(dest => dest.ClientComment,
        opt =>
        {
            opt.MapFrom(y => y.CommentComps.Where(x => x.Contact == null).Select(x => x.Comment));
            //opt.ExplicitExpansion();
        }).
        ForMember(dest => dest.Relations,
        opt =>
        {
            opt.MapFrom(y => y.ClientRelationCompClient1Navigations);
            //opt.ExplicitExpansion();
        })
        ;


        CreateMap<Document, DocumentContract>();

        CreateMap<ContactInfo, ContactInfoContract>().
        ForMember(dest => dest.ContactComments,
        opt =>
        {
        opt.MapFrom(y => y.CommentComps.Select(x => x.Comment));
        });

        CreateMap<ClientRelationComp, RelationContract>().
            ForMember(dest => dest.ClientINN,
            opt => {
                opt.MapFrom(x => x.Client2);
            }).
            ForMember(dest => dest.RelationType,
            opt => {
                opt.MapFrom(x => x.RelationId);
            });

        CreateMap<ICollection<Client>, ClientContract>();
        CreateMap<ICollection<PhysicalPerson>, ClientContract>();
        CreateMap<ICollection<Company>, ClientContract>();
        CreateMap<Comment, CommentContract>();
        CreateMap<ICollection<Comment>, ICollection<ContactInfoContract>>();
        CreateMap<ICollection<ClientRelationComp>, ClientRef>();
        
    }
}

My Controller:

public class ClientContractController : ODataController
{
    CRMContext _context;
    IMapper _mapper;
    public ClientContractController(CRMContext ctx, IMapper mapper )
    {
        _context = ctx;
        _mapper = mapper;
    }

    [EnableQuery(MaxExpansionDepth = 10)]
    public IQueryable<ClientContract> Get()
    {
        return _mapper.ProjectTo<ClientContract>(_context.ClientRefs).Where(x => x.ValidFrom <= DateTime.Now && x.ValidTo >= DateTime.Now);
    }
}

this gives the following exception https://localhost:44371/odata/clientcontract?$expand=relations

System.InvalidOperationException: The LINQ expression '$it' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment) at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression) at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression1 node) at System.Linq.Expressions.Expression1.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes) at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment) at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression) at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment) at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression) at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken) at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadInternal[T](Object value) at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, Object asyncEnumerable, Func2 reader) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|21_0(ResourceInvoker invoker, IActionResult result) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters() --- End of stack trace from previous location where exception was thrown --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Michael Wang · Accepted Answer · 2020-08-21T08:06:55.170

1

Update 21/08/20202

Without explicit instructions, AutoMapper will expand all members in the result.

To control which members are expanded during projection, set ExplicitExpansion in the configuration and then pass in the members you want to explicitly expand:

dbContext.Orders.ProjectTo<OrderDto>(configuration,
    dest => dest.Customer,
    dest => dest.LineItems);
// or string-based
dbContext.Orders.ProjectTo<OrderDto>(configuration,
    null,
    "Customer",
    "LineItems");
// for collections
dbContext.Orders.ProjectTo<OrderDto>(configuration,
    null,
    dest => dest.LineItems.Select(item => item.Product));

Use the property name and not the attribute Name when use OData.

OData client library relies on it's own attribute OriginalNameAttribute to gain knowledge about class/member names as server emits them. The details you can see from here.

edited Aug 21 '20 at 08:06

answered Aug 20 '20 at 15:46

Michael Wang

3,782
1
5
15

Not worked. I removed DataContract/DataMember . But the exception is thrown – user3315759 Aug 20 '20 at 19:11
If I remove ExplicitExpansion() it works but 2 join generated in SQL. But if I added ExplicitExpansion I get exception above. – user3315759 Aug 21 '20 at 07:41
Maybe you could check this [issue](https://stackoverflow.com/questions/36284142/using-dtos-with-odata-web-api). It same to yours – Michael Wang Aug 21 '20 at 07:52
This works, but I discover other problem that, expand generates 2 left join to the same table. – user3315759 Aug 21 '20 at 17:41
@user3315759, if this issue is solved, please [mark it](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work/5235#5235) for helping more people. And please check the answer I posted to your left join problem. – Michael Wang Aug 24 '20 at 03:10
Thank you for your answers. There is other problem I saw that , always 2 join generated in SQL without using ExplicitExpansion(). If I added ExplicitExpansion() and pass in members which i want to expand, then again 2 join is generated. What can be problem? – user3315759 Aug 24 '20 at 07:31
exec sp_executesql N'SELECT [c].[inn], [d].[document_expire_date], [d].[document_number], [d].[document_type], [d].[id], @__TypedProperty_1, [d0].[document_expire_date], [d0].[document_number], [d0].[document_type], [d0].[id], CAST(1 AS bit) FROM [ClientRef] AS [c] LEFT JOIN [Document] AS [d] ON [c].[inn] = [d].[inn] LEFT JOIN [Document] AS [d0] ON [c].[inn] = [d0].[inn] ORDER BY [c].[inn], [d].[id], [d0].[id]',N'@__TypedProperty_1 nvarchar(4000)',@__TypedProperty_1=N'c3b02432-1d14-4584-944e-0ddb16ce66e3' – user3315759 Aug 24 '20 at 08:33
Did you generate the SQL above, when use `$expand` to access `Get()` controller? – Michael Wang Aug 24 '20 at 08:51
yes, I got it from sql profiler with following link: clientcontract?$expand=documents – user3315759 Aug 24 '20 at 16:33
do you see 2 left join to same table in sql server profiler? – user3315759 Aug 25 '20 at 07:47
Sorry, I didn't code complete your model in my project, I think it must be related with your model relationship(either a 1-1 or 1-N join) causing these duplicate left join. – Michael Wang Aug 25 '20 at 07:58
It's huge model and takes time to read. But I‘m still following up with it. – Michael Wang Aug 25 '20 at 08:00
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/220414/discussion-between-lightman-and-user3315759). – Michael Wang Aug 25 '20 at 09:48
I commented in chat – user3315759 Aug 28 '20 at 10:42

ExplicitExpansion() cause OData expand not work

1 Answers1

Linked