The LINQ expression could not be translated but only for some fields

warmfire540 commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Product

Hot Chocolate

Describe the bug

I've added AutoMapper to our HC 12 + EF Core 7 project to complicate things a bit more. After lots of self reflection I finally got everything setup and projections / overfetching working by following this issue #4724 and a few others. Maybe my issue is related to some of the last minute warnings in the last comment?

My present issue is that sometimes filtering is not translated the way I'd expect and instead I get an Exception from EF.

Steps to reproduce

Project Setup

My DTO

public class InventSite
{
    public string DataAreaId { get; set; } = null!;
    public string SiteId { get; set; } = null!;
    public string? Dimension4 { get; set; }
    public string? Name { get; set; }
    public int Disabled { get; set; }
}

My exposed Graph entity

public class LazySite
{
    public int Id { get; init; }
    public int? AxSiteId { get; init; }
    public string? Name { get; init; }
    public int Disabled { get; init; }
}

AutoMapper profile

public MappingProfile()
    {
        CreateMap<InventSite, LazySite>()
            .ForMember(d => d.Id, e => e.MapFrom(s => Convert.ToInt32(s.SiteId)))
            .ForMember(d => d.AxSiteId, e => e.MapFrom(s => !string.IsNullOrWhiteSpace(s.Dimension4) ? (int?)Convert.ToInt32(s.Dimension4) : null))
            //.ForMember(d => d.Disabled, e => e.MapFrom(s => s.Disabled))
            .ForAllMembers(i => i.ExplicitExpansion());
    }

Query

public class Query
{
    [Authorize]
    [UseProjection]
    [UseFiltering]
    [UseSorting]
    public IQueryable<LazySite> GetSites([Service] ISiteService siteService, IResolverContext resolverContext)
        => siteService.GetSites(resolverContext);
}

// ISiteService method:
public IQueryable<LazySite> GetSites(IResolverContext resolverContext)
        => _edrHelper.GetSites().ProjectTo<InventSite, LazySite>(resolverContext);

// IEdrHelper here
public IQueryable<InventSite> GetSites() =>
        _context.InventSites.AsNoTracking();

Issues

Here's 2 working queries

{
  sites(where: { id: { eq: 1 } }) {
    id
    name
  }
}

{
  sites(where: { id: { eq: 1 } }) {
    id
    name
  }
}

results

{
   "data": {
      "sites": [
         {
            "id": 1,
            "name": "Site 1 - Tampa"
         }
      ]
   }
}

same as above

sql

SELECT CONVERT(int, [i].[SiteID]) AS [Id], [i].[Name]
FROM [dbo].[InventSite] AS [i]
WHERE CONVERT(int, [i].[SiteID]) = @__p_0

SELECT CONVERT(int, [i].[SiteID]) AS [Id], [i].[Name]
FROM [dbo].[InventSite] AS [i]
WHERE [i].[Name] = @__p_0

However if I filter on the disabled property - I get an exception

{
  sites(where: { disabled: { eq: 1 } }) {
    id
    name
  }
}

System.InvalidOperationException: 'The LINQ expression 'DbSet<InventSite>()
    .Where(i => new LazySite{ 
        Id = Convert.ToInt32(i.SiteId), 
        Name = i.Name 
    }
    .Disabled == __p_0)' 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.'

I'm not too sure what's different here - I would expect the Where to be something like Where(i => i.Disabled == __p_0)

I updated the ISiteService code to use .Map instead of .ProjectTo and no queries work

// also can't translate
  public IQueryable<LazySite> GetSites(IResolverContext resolverContext)
  {
      var mapper = resolverContext.Service<AutoMapper.IMapper>();
      return _edrHelper.GetSites().Select(s => mapper.Map<InventSite, LazySite>(s));
  }

I now get

System.InvalidOperationException: 'The LINQ expression 'DbSet<InventSite>()
    .Where(i => __mapper_0.Map<InventSite, LazySite>(i).Name == __p_1)' 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.'

If I don't use AutoMapper or the HC extension and manually resolve the object, it works:

// this works
public IQueryable<LazySite> GetSites(IResolverContext resolverContext)
    => _edrHelper.GetSites().Select(s => new LazySite
    {
        Id = Convert.ToInt32(s.SiteId),
        AxSiteId = Convert.ToInt32(s.Dimension4), // or to match mapping profile !string.IsNullOrWhiteSpace(s.Dimension4) ? (int?)Convert.ToInt32(s.Dimension4) : null
        Name = s.Name,
        Disabled = s.Disabled
    });

SELECT CONVERT(int, [i].[SiteID]) AS [Id], [i].[Name]
FROM [dbo].[InventSite] AS [i]
WHERE [i].[DISABLED] = @__p_0

Any help is appreciated! The end goal would be to use HC, EF Core, and AutoMapper. Ideally AutoMapper is just doing the object translation, I trust HC to do projections/selections/filtering since it was doing that just fine before I added AM. I get that the extension though passes the projection on to AM, I could change that if needed and not use the extension by HC.

Relevant log output

No response

Additional Context?

resources I've considered

Version

12.16.0

warmfire540 commented 1 year ago

YAY! someone from SO helped me out

Apparently the problem is with filter fields which are not included in the result. And the reason is the way HC builds the query when combined with AM - projection first (using explicit mapping, i.e. include only the specified fields), then filter. While as I understand, w/o AM they use filter first, then projection. That's why the manual Select approach works. Or because in manual approach you include all the fields in the select. Remove AM explicit expansion or do not use AM or wait HC to resolve it (if they can and willing to). – Ivan Stoev

by adding the fields explicitly in the SELECT, I don't get an exception:

{
  sites(
    where: { and: [{ disabled: { eq: 0 }, type: { id: { in: [1, 3] } } }] }
  ) {
    id
    name
    disabled
    type {
      id
    }
  }
}

While I don't need those fields returned, these is a small API so it won't hurt. Is there a way to make it all work w/o needing to include them in the projection?

glen-84 commented 9 months ago

Closing as a duplicate of #5079.

ChilliCream / graphql-platform