Question posted in
The official documentation can be found here.

Asp.net – Invalid LINQ Expression … could not be translated

I don’t know why, when I execute this LINQ request, this exception is returned :

System.InvalidOperationException: The LINQ expression '__ids_0
.Contains(StructuralTypeShaperExpression: 
    Patron.Domain.RoleAggregate.Role
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.Id.Value)' could not be translated.

That is the code :

public async Task<List<Role>> GetRolesByIdsAsync(List<RoleId> roleIds)
{
    var ids = roleIds.Select(id => id.Value).ToList();
    return await dbContext.Roles
        .Where(r => ids.Contains(r.Id.Value))
        .ToListAsync();
}

RoleId definition :

public sealed class RoleId : AggregateRootId<Guid>
{
    private RoleId(Guid value) : base(value)
    {
    }

    public static RoleId Create(Guid userId) =>
        new(userId);

    public static RoleId CreateUnique() =>
        new(Guid.NewGuid());

    public static ErrorOr<RoleId> Create(string value)
    {
        if (!Guid.TryParse(value, out var guid))
        {
            return Errors.Role.InvalidRoleId;
        }

        return new RoleId(guid);
    }
}

AggregateRootId :

public abstract class AggregateRootId<TId> : EntityId<TId>
{
    protected AggregateRootId(TId value) : base(value)
    {
    }
}

EntityId :

public abstract class EntityId<TId>(TId value) : ValueObject
{
    public TId Value { get; } = value;

    public override IEnumerable<object?> GetEqualityComponents()
    {
        yield return Value;
    }

    public override string? ToString() => Value?.ToString() ?? 
        base.ToString();
}

I tryed with the Any() method instead of Contains() but I have the same message.

I found a solution but it’s very ugly and not optimised (I want to use LINQ) :

public async Task<List<Role>> GetListRoleByListId(List<RoleId> roleIds)
{
    var roles = new List<Role>();
    foreach (var role in await dbContext.Roles.ToListAsync())
    {
        foreach (var roleId in roleIds)
        {
            if (role.Id.Equals(roleId))
            {
                roles.Add(role);
            }
        }
    }

    return roles;
}

It’s weird because I can compare two RoleId here :

public async Task<Role?> GetAsync(RoleId id) =>
    await dbContext.Roles.FirstOrDefaultAsync(r => r.Id == id);

Someone knows where this error comes from and how fix it please ?

The GitLab of the project :
https://gitlab.com/victor.nardel/training-project-ddd-clean-architecture

You can find the problem in ./Patron.Infrastructure/Persistence/Repositories/RoleRepository.cs

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So, the only alternative that I found is to build my sql query :

        var parameters = new SqlParameter("roleIds", SqlDbType.VarChar)
    {
        Value = string.Join(",", roleIds.Select(id => id.ToString())),
    };

    const string sqlQuery = "SELECT * FROM Roles WHERE Id IN (SELECT value 
        FROM STRING_SPLIT(@roleIds, ','))";
    
    return await dbContext
        .Roles
        .FromSqlRaw(sqlQuery, parameters)
        .ToListAsync();

    If someone want to criticize my method, don't hesitate to share your opinion !

    Thanks


  2. 0
    1. Problem at hand

    You get an error in .Where(r => ids.Contains(r.Id.Value)) because the data provider you are using does not know how to translate List<RoleId>.Contains(RoleId) to a query – data store doesn’t know the C# type RoleId you defined in application code and the translation fails. Think about it – how could e.g. SQL check if a set of RoleIds contains a RoleId if it doesn’t even know what that type is?

    1. Why this works and why it’s bad:
    public async Task<List<Role>> GetListRoleByListId(List<RoleId> roleIds)
{
    var roles = new List<Role>();
    foreach (var role in await dbContext.Roles.ToListAsync())
    {
        foreach (var roleId in roleIds)
        {
            if (role.Id.Equals(roleId))
            {
                roles.Add(role);
            }
        }
    }

    return roles;
}

    You’re not getting the right data from EF Core call, but rather you’re loading all of the data into memory and then perform some additional operations. You should avoid doing so because it’s way less performant from the point of execution time and memory pressure.

    1. Why this works?

    It’s weird because I can compare two RoleId here :

    public async Task<Role?> GetAsync(RoleId id) =>
    await dbContext.Roles.FirstOrDefaultAsync(r => r.Id == id);

    It’s not weird at all. It’s not comparison, it’s equality check operator. Equality only tells you if something is equal or not, while comparing actually enhances on it by telling you which one is lower/greater if not equal which is impossible to get right without type knowledge. Equality operator doesn’t need that context – it can just do reference comparison or treat instances memory as byte[] and do a SequenceEqual between them.

    1. Solution:

    The easiest solution is using primitive type compare, if data store supports the type, EF Core will be able to translate Contains() among other operations. This call loads only required data so it’s way more efficient.

    public async Task<List<Role>> GetListRoleByListId(List<RoleId> roleIds)
{
    // flatten RoleIds to Guids
    var roleIdValues = roleIds
        .Select(static r => r.Value)
        .ToList(); // Not sure materialising here is necessary, verify - maybe IEnumerable<T> coming from Select() is enough

    return await dbContext
        .Roles
        .Where(r => roleIdValues.Contains(r.Id.Value)) // working with Guids here (check if set of Guids contains specific Guid), no more custom types so EF Core should be able to translate this to a query.
        .ToListAsync(); // actually call for the data
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search