C#•12mo ago

✅ EF Core Query Filter Exception

Hi, I'm working with EF Core with PostgreSQL, using Code First approach, I have this Entity Type Configuration:

public class CategoryEntityConfig : IEntityTypeConfiguration<CategoryEntity>
{
    public void Configure(EntityTypeBuilder<CategoryEntity> builder)
    {
        builder.HasKey(c => c.Id);
        
        builder.Property(c => c.Name).IsRequired().HasMaxLength(50);
            
        builder.Property(c => c.Description).IsRequired(false);


        builder.Property(c => c.CreatedAt).IsRequired(true);
        builder.Property(c => c.CreatedBy).IsRequired(true);
        
        builder.Property(c => c.LastUpdatedAt).IsRequired(false);
        builder.Property(c => c.LastUpdatedBy).IsRequired(false);
        builder.Property(c => c.DeletedAt).IsRequired(false);
        builder.Property(c => c.DeletedBy).IsRequired(false);
        


        builder.HasOne(c => c.User)
            .WithMany(u => u.Categories)
            .HasForeignKey(c => c.UserId)
            .OnDelete(DeleteBehavior.Cascade);
            
        
        builder.HasQueryFilter( e => OnlyNonDeletedCategoriesQueryFilter(e));
    }
    
    
    /// <summary>
    /// Determines if a category entity is not deleted.
    /// </summary>
    /// <param name="categoryEntity">The category entity to check.</param>
    /// <returns><c>true</c> if the category entity is not deleted; otherwise, <c>false</c>.</returns>
    private static bool OnlyNonDeletedCategoriesQueryFilter(CategoryEntity categoryEntity)
    {
        return categoryEntity.IsDeleted == false;
    }
}

public class CategoryEntityConfig : IEntityTypeConfiguration<CategoryEntity>
{
    public void Configure(EntityTypeBuilder<CategoryEntity> builder)
    {
        builder.HasKey(c => c.Id);
        
        builder.Property(c => c.Name).IsRequired().HasMaxLength(50);
            
        builder.Property(c => c.Description).IsRequired(false);


        builder.Property(c => c.CreatedAt).IsRequired(true);
        builder.Property(c => c.CreatedBy).IsRequired(true);
        
        builder.Property(c => c.LastUpdatedAt).IsRequired(false);
        builder.Property(c => c.LastUpdatedBy).IsRequired(false);
        builder.Property(c => c.DeletedAt).IsRequired(false);
        builder.Property(c => c.DeletedBy).IsRequired(false);
        


        builder.HasOne(c => c.User)
            .WithMany(u => u.Categories)
            .HasForeignKey(c => c.UserId)
            .OnDelete(DeleteBehavior.Cascade);
            
        
        builder.HasQueryFilter( e => OnlyNonDeletedCategoriesQueryFilter(e));
    }
    
    
    /// <summary>
    /// Determines if a category entity is not deleted.
    /// </summary>
    /// <param name="categoryEntity">The category entity to check.</param>
    /// <returns><c>true</c> if the category entity is not deleted; otherwise, <c>false</c>.</returns>
    private static bool OnlyNonDeletedCategoriesQueryFilter(CategoryEntity categoryEntity)
    {
        return categoryEntity.IsDeleted == false;
    }
}

After running the app I get an exception that tells me that the EF Core couldn't translate that query filter to SQL, as shown in the attached video. Please any help to fix this issue guys ??? and massive thanks in advance <3

2 Replies

Saber•12mo ago

try moving the logic into the query filter instead of calling the method

M B V R KOP•12mo ago

I found a solution by not letting the method return the bool but to return the Expression itself:

    /// <summary>
    /// Returns an expression that filters out only non-deleted categories.
    /// </summary>
    /// <returns>An expression that represents the filter condition.</returns>
    private static Expression<Func<CategoryEntity, bool>> OnlyNonDeletedCategoriesQueryFilter()
    {
        return categoryEntity => categoryEntity.IsDeleted == false;
    }

    /// <summary>
    /// Returns an expression that filters out only non-deleted categories.
    /// </summary>
    /// <returns>An expression that represents the filter condition.</returns>
    private static Expression<Func<CategoryEntity, bool>> OnlyNonDeletedCategoriesQueryFilter()
    {
        return categoryEntity => categoryEntity.IsDeleted == false;
    }

Then I call it like this :

builder.HasQueryFilter( OnlyNonDeletedCategoriesQueryFilter() );

builder.HasQueryFilter( OnlyNonDeletedCategoriesQueryFilter() );

Also, your solution is works fine too Massive thanks <3 I appreciate

Gaming

Programming

✅ EF Core Query Filter Exception