I have C# application and I have written the following LINQ expression.

var invoices = await this.Context
    .Set<InvoiceItem>().Where(x => x.Status == "Created")
    .Where(query.Filter)
    .OrderBy(query.Sort)
    .Paginate(pageInfo)
    .ToListAsync();

foreach (var item in invoices)
{
    item.ServiceEndDate = this.Context.InvoiceLineItems
        .FirstOrDefault(x => x.InvoiceId == item.Id) != null 
            ? this.Context.InvoiceLineItems.FirstOrDefault(x => x.InvoiceId == item.Id).ServiceEndDate 
            : null;
}
return invoices;

whenever this expression is executed i get the following error

The LINQ expression 'DbSet()\r\n .Where(i => i.BusinessId == __ef_filter__BusinessId_0 || __ef_filter__IsMedCompliUser_1)\r\n .Where(i => i.Status == "Created")\r\n .Where(i => i.IsActive == __Value_0)\r\n .OrderBy(i => (object)i.ServiceEndDate.Value)' could not be translated. Additional information: Translation of member 'ServiceEndDate' on entity type 'InvoiceItem' failed. This commonly occurs when the specified member is unmapped.\r\nTranslation of member 'ServiceEndDate' on entity type 'InvoiceItem' failed. This commonly occurs when the specified member is unmapped. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable',

Part of model:

public class InvoiceItem : IHasTenant
{
    public long Id { get; set; }
    public long BusinessId { get; set; }

    public bool IsActive { get; set; }

    public DateTime? InvoiceDate { get; set; }
    public DateTime? DateOfService { get; set; }
    [NotMapped]
    public DateTime? ServiceEndDate { get; set; }

PageInfo:

public class PageInfo : MedCompli.Common.Contracts.PageInfo
{
}

public static class PageInfoExtensions
{
    public static bool IsPageNumberValid(this PageInfo pageInfo)
    {
        return MedCompli.Common.Extensions.PageInfoExtensions.IsPageNumberValid(pageInfo);
    }

    public static bool IsPageSizeValid(this PageInfo pageInfo)
    {
        return MedCompli.Common.Extensions.PageInfoExtensions.IsPageSizeValid(pageInfo);
    }
}

Query:

namespace MedCompli.Core.Contracts
{
    //
    // Summary:
    //     Object that contains sorting and filtering parameters for an Entity Framework
    //     query.
    public class Query
    {
        public Query();

        //
        // Summary:
        //     Filtering parameters.
        [FromQuery(Name = "q")]
        [JsonProperty("q")]
        public QueryFilter Filter { get; set; }
        //
        // Summary:
        //     Sorting parameters.
        [FromQuery(Name = "s")]
        [JsonProperty("s")]
        public IDictionary<string, SortOperator> Sort { get; set; }

        public virtual QueryFilter ConvertFilterProperties(IMapper mapper);
        public virtual IDictionary<string, SortOperator> ConvertSortProperties(IMapper mapper);
    }
}

CodePudding user response：

The error says:

Translation of member 'ServiceEndDate' on entity type 'InvoiceItem' failed.

And the expression contains OrderBy(i => (object)i.ServiceEndDate.Value) So the provider doesn't know how to map this condition, since the property is not mapped.

You need to either map this property or not use in query (conditions, sorting, etc.)

CodePudding user response：

If you want to make not mapped property to be translatable, EF Core should know how this property is mapped to the database. In your case you have to project ALL properties manually with additional property ServiceEndDate

var invoices = await this.Context
    .Set<InvoiceItem>().Where(x => x.Status == "Created")
    .Selec(x => new InvoiceItem
    {
        Id = x.Id,
        BusinessId = x.BusinessId,
        IsActive = x.IsActive,
        InvoiceDate = x.InvoiceDate,
        DateOfService = x.DateOfService,
        ServiceEndDate = x.ServiceEndDate,

        // other propeties

        ServiceEndDate = (DateTime?)this.Context.InvoiceLineItems
            .Where(li => li.InvoiceId == item.Id)
            .Select(li => li.ServiceEndDate)
            .FirstOrDefault()
    })
    .Where(query.Filter)
    .OrderBy(query.Sort)
    .Paginate(pageInfo)
    .ToListAsync();

Note that FirstOrDefault has no sense without OrderBy.