EF Core multiple "where" are not translated

.net-core c# entity-framework-core

Question

I have a db model that I want to filter (during runtime) on different attributes. For that I have methods each running a "where" clause depending on the attribute and return an IQueryable. Thing is that the clause cannot be translated to SQL, so the query runs with no "WHERE" statements, returns the whole table and the server is doing the filtering, which is both slow and expensive.

From the logs, I get a message:

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where (new EntityId([ed].EntityId).Id == __entityId_Id_1)' could not be translated and will be evaluated locally.

My question is: am I on the right track here, or not? And if yes where should I look next?

Main function(s)

public static EntityDataDTO GetEntityByKey(this IQueryable<EntityDTO> query, IEntityId entityId, EntityTypeDTO type, string key)
{
    return query
        .HasEntity(entityId)
        .HasType(type)
        .HasKey(key)
        .FirstOrDefault();
}

public static EntityDataDTO GetEntity(this IQueryable<EntityDTO> query, IEntityId entityId, EntityTypeDTO type)
{
    return query
        .HasEntity(entityId)
        .HasType(type)
        .FirstOrDefault();
}

Subfunctions

public static IQueryable<EntityDDTO> HasType(this IQueryable<EntityDTO> query, EntityTypeDTO type)
{
    return query.Where(ed => ed.Type == type);
}

public static IQueryable<EntityDTO> HasEntity(this IQueryable<EntityDTO> query, IEntityId entityId)
{
    return query.Where(ed => ed.EntityId.Id == entityId.Id);
}

EntityDTO

public class EntityDTO
    {
        public int Id { get; set; }
        public EntityTypeDTO Type { get; set; }
        public string Key { get; set; }
        public string Value { get; set; }
        public IEntityId EntityId { get; set; }
        public IPartnerId PartnerId { get; set; }
    }
1
1
4/2/2019 1:31:54 PM

Popular Answer

I see no issue with any of the code you've posted. However, you neglected to post the definition of your HasKey method, so I'm assuming the issue is actually there.

In any case, the warning is telling you exactly what the problematic code is: new EntityId(ed.EntityId).Id. Find that bit of code and correct it. Something like a call to new up a type cannot be translated to SQL, so EF must run that in memory. Technically, you can proceed and just allow EF to do this. However, that's generally a sign that you're going to running inefficient queries, so you should look for a better way that doesn't involve having to run that part of the query in memory.

2
4/2/2019 1:30:20 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow