Entity Framework Core 3.1 - Linq - Could not be translated. Either rewrite the query in a form that can be translated

Question

When I try to use a select in a Linq query and create a viewmodel in the select statement, it works. When I try to extract the creation of the viewmodel to a static method, it gives the following error: "Could not be translated. Either rewrite the query in a form that can be translated". Why is this happening?

The calling of SiteViewModel.GetSiteViewModel(s) generates this error.

    public async Task<IEnumerable<ContractViewModel>> ExecuteAsync(CancellationToken token = new CancellationToken())
    {
        var contracts = await _domainContext.Contracts.Include(s => s.Sites)
               .AuthorizedFor(User)
               .Select(c => new ContractViewModel()
               {
                   CreationDate = c.CreationDate,
                   ContractId = c.Id,
                   ContractLineWiatingForConfirmation = c.ContractLines.Any(l => l.Status == ContractLineStatus.WaitingForConfirmation),
                   ModificationDate = c.ModificationDate,
                   ExpirationDate = c.ExpirationDate,
                   Title = c.Title,
                   Status = c.Status,
                   Sites = c.Sites.Select(s => SiteViewModel.GetSiteViewModel(s)).OrderBy(s => s.SiteName)
               })
               .ToListAsync(token);

        return contracts;
    }

GetSiteViewModel looks like this:

    public static SiteViewModel GetSiteViewModel(ContractSite x)
    {
        return new SiteViewModel
        {
            SiteId = x.Site.Id,
            SiteCode = x.Site.Code,
            SiteName = x.Site.Name,
            AgressoCode = x.Site?.ExternalReference?.Identifier,
            AgressoAdministration = x.Site?.ExternalReference?.Source
        };
    }

Update:

When I remove the orderby, it works. When I remove the static method to create a viewmodel, and place the creation of the viewmodel in the query itself and DONT remove the orderby, it then also works...

Answer 1

It is because LINQ translator can not look into your GetSiteViewModel method body and recognize how properties are remapped.

Simplest fix, is to do OrderBy before GetSiteViewModel call

Sites = c.Sites.OrderBy(s => s.Site.Name).Select(s => SiteViewModel.GetSiteViewModel(s))

But if it is not an option, then you can solve that by several methods.

Simplest and without third-party dependencies is to change your function to return IQueryable

public static class MyDtoExtensions
{
   public static IQueryable<SiteViewModel> GetSiteViewModel(this IQueryable<ContractSite> query)
   {
      return query.Select(x => new SiteViewModel
      {
          SiteId = x.Site.Id,
          SiteCode = x.Site.Code,
          SiteName = x.Site.Name,
          AgressoCode = x.Site?.ExternalReference?.Identifier,
          AgressoAdministration = x.Site?.ExternalReference?.Source
      };
   }
}

Then you can use this extension:

Sites = c.Sites.AsQueryable().GetSiteViewModel().OrderBy(s => s.SiteName)

But for better extensibility I would suggest to use this extension (similar functionality is coming with LINQKit) https://github.com/axelheer/nein-linq/

And rewrite your function

public static class MyDtoExtensions
{
    [InjectLambda]
    public static SiteViewModel GetSiteViewModel(this ContractSite x)
    {
        _getSiteViewModel =?? GetSiteViewModel().Compile();
        return _getSiteViewModel(x);
    }

    private static Func<ContractSite, SiteViewModel> _getSiteViewModel; 

    private static Expression<Func<ContractSite, SiteViewModel>> GetSiteViewModel()
    {
        return x => new SiteViewModel
        {
            SiteId = x.Site.Id,
            SiteCode = x.Site.Code,
            SiteName = x.Site.Name,
            AgressoCode = x.Site?.ExternalReference?.Identifier,
            AgressoAdministration = x.Site?.ExternalReference?.Source
        };
    }
}

Then you can use GetSiteViewModel in queries without limitations. But do not forget to call ToEntityInjectable()

public async Task<IEnumerable<ContractViewModel>> ExecuteAsync(CancellationToken token = default)
{
    var contracts = await _domainContext.Contracts
           .ToEntityInjectable()
           .Include(s => s.Sites)
           .AuthorizedFor(User)
           .Select(c => new ContractViewModel()
           {
               CreationDate = c.CreationDate,
               ContractId = c.Id,
               ContractLineWiatingForConfirmation = c.ContractLines.Any(l => l.Status == ContractLineStatus.WaitingForConfirmation),
               ModificationDate = c.ModificationDate,
               ExpirationDate = c.ExpirationDate,
               Title = c.Title,
               Status = c.Status,
               Sites = c.Sites.Select(s => s.GetSiteViewModel()).OrderBy(s => s.SiteName)
           })
           .ToListAsync(token);

    return contracts;
}