Why only one EF Core Global Query Filter work but others are not Hi, I'm working with EF Core 6, recently I want to use the Global Query Filters. So in OnModelCreating inside my DbContext I applied the two Gobal Query Filtersshown bellow: The Problem: The problem is only the first query filter who applied, but the IAuditable one not applied, please how do I fix this issue ? Massive thanks in advance <@&566134849111588884>

help

Root Question Message

MBARK9/22/2022

Why only one EF Core Global Query Filter work but others are not

Hi, I'm working with EF Core 6, recently I want to use the Global Query Filters.
So in OnModelCreating inside my DbContext I applied the two Gobal Query Filtersshown bellow:

The Problem:
The problem is only the first query filter who applied, but the IAuditable one not applied, please how do I fix this issue ?

Massive thanks in advance
&566134849111588884

MBARK9/22/2022

My query filters:

 foreach ( var entityType in modelBuilder.Model.GetEntityTypes() )
        {
            if ( typeof( IArchivable ).IsAssignableFrom( entityType.ClrType ) )
            {
                var parameter = Expression.Parameter( entityType.ClrType , "p" );

                var isArchivedProperty = MemberExpression.Property(parameter, "IsArchived");
                
                // check isArchived == null

                var isArchivedNullCheck = Expression.Equal( isArchivedProperty , Expression.Constant( null , typeof( bool ? ) ) );
                var isArchivedNullFunc = Expression.Lambda( isArchivedNullCheck , parameter );
                
                // check isArchived == false
                var isArchivedFalseCheck = Expression.Equal( isArchivedProperty , Expression.Constant( false , typeof( bool ? ) ) );
                var isArchivedFalseFunc = Expression.Lambda( isArchivedFalseCheck , parameter );
                
                // Combine the two expressions using OR
                var isArchivedCExpression = Expression.OrElse( isArchivedNullCheck , isArchivedFalseCheck );
                
                // Create the lambda expression
                var isArchivedFunc = Expression.Lambda( isArchivedCExpression , parameter );

                modelBuilder.Entity( entityType.ClrType ).HasQueryFilter( isArchivedFunc );

            }

MBARK9/22/2022

            else if ( typeof( IAuditable ).IsAssignableFrom( entityType.ClrType ) )
            {
                var parameter = Expression.Parameter( entityType.ClrType , "p" );
                
                var isDeletedProperty = MemberExpression.Property(parameter, "IsDeleted");
                
                // check isDeleted == null
                var isDeletedNullCheck = Expression.Equal( isDeletedProperty , Expression.Constant( null , typeof( bool ? ) ) );
                var isDeletedNullFunc = Expression.Lambda( isDeletedNullCheck , parameter );
                
                // check isDeleted == false
                var isDeletedFalseCheck = Expression.Equal( isDeletedProperty , Expression.Constant( false , typeof( bool ? ) ) );
                var isDeletedFalseFunc = Expression.Lambda( isDeletedFalseCheck , parameter );
                
                // Combine the two expressions using OR
                var isDeletedCExpression = Expression.OrElse( isDeletedNullCheck , isDeletedFalseCheck ); // IsDeleted == null || IsDeleted == false
                
                // Create the lambda expression
                var isDeletedFunc = Expression.Lambda( isDeletedCExpression , parameter ); // p => p.IsDeleted == null || p.IsDeleted == false

                modelBuilder.Entity( entityType.ClrType ).HasQueryFilter( isDeletedFunc );
            }
        }

gerard9/23/2022

Because you can only have one global filter, if set the filter with HasQueryFilter and try to add another one with HasQueryFilter, it'll actually replace the old filter.

gerard9/23/2022

You might want to combine the two expressions with Expression.AndAlso

Thaumanovic9/23/2022

Note

It is currently not possible to define multiple query filters on the same entity - only the last one will be applied. However, you can define a single filter with multiple conditions using the logical AND operator (&& in C#).

(https://learn.microsoft.com/en-us/ef/core/querying/filters)

MBARK9/23/2022

But the problem is the Entities types will be differents because one filter should be applied on IAuditable entities and another filter should be applied on IArchivable entties.
Is I miss something ?

MBARK9/23/2022

I'm gonna crazy with this issue

MBARK9/23/2022

hmmmmm, omg maybe I should write query filter for every entity manually

gerard9/23/2022

That shouldn't be an issue, the parameter in the filter is the entity itself:

var parameter = Expression.Parameter( entityType.ClrType , "p" );

which can both implement IAuditable and IArchivable. So instead of doing an "else-if", you change it to an if-statement that combines the expression.

gerard9/23/2022

Example:

foreach ( var entityType in modelBuilder.Model.GetEntityTypes() )
{
    var parameter = Expression.Parameter( entityType.ClrType , "p" );

    Expression condition? = null;

    if ( typeof( IArchivable ).IsAssignableFrom( entityType.ClrType ) )
    {
        var isArchivedProperty = MemberExpression.Property(parameter, "IsArchived");
        
        // check isArchived == null

        var isArchivedNullCheck = Expression.Equal( isArchivedProperty , Expression.Constant( null , typeof( bool ? ) ) );
        var isArchivedNullFunc = Expression.Lambda( isArchivedNullCheck , parameter );
        
        // check isArchived == false
        var isArchivedFalseCheck = Expression.Equal( isArchivedProperty , Expression.Constant( false , typeof( bool ? ) ) );
        var isArchivedFalseFunc = Expression.Lambda( isArchivedFalseCheck , parameter );
        
        // Combine the two expressions using OR
        condition = Expression.OrElse( isArchivedNullCheck , isArchivedFalseCheck );
    }

    if ( typeof( IAuditable ).IsAssignableFrom( entityType.ClrType ) )
    {
        var isDeletedProperty = MemberExpression.Property(parameter, "IsDeleted");
        
        // check isDeleted == null
        var isDeletedNullCheck = Expression.Equal( isDeletedProperty , Expression.Constant( null , typeof( bool ? ) ) );
        var isDeletedNullFunc = Expression.Lambda( isDeletedNullCheck , parameter );
        
        // check isDeleted == false
        var isDeletedFalseCheck = Expression.Equal( isDeletedProperty , Expression.Constant( false , typeof( bool ? ) ) );
        var isDeletedFalseFunc = Expression.Lambda( isDeletedFalseCheck , parameter );
        
        // Combine the two expressions using OR
        var isDeletedCExpression = Expression.OrElse( isDeletedNullCheck , isDeletedFalseCheck ); // IsDeleted == null || IsDeleted == false
        
        condition = condition == null ? isDeletedCExpression : Expression.AndAlso( condition , isDeletedCExpression );
    }

    if ( condition != null )
    {
        var filter = Expression.Lambda( condition , parameter );
        modelBuilder.Entity( entityType.ClrType ).HasQueryFilter( filter );
    }
}

MBARK9/23/2022

@114280336535453703 sorry for annoying mate, I want to thanking you about your help and time
I almost used your provided solution

  foreach ( var entityType in modelBuilder.Model.GetEntityTypes() )
        {
            var          parameter  = Expression.Parameter( entityType.ClrType , "p" );
            Expression ? conditions = null;


            if ( typeof( IArchivable ).IsAssignableFrom( entityType.ClrType ) )
            {
                var isArchivedProperty = MemberExpression.Property( parameter , "IsArchived" );
                
                // check isArchived == null
                var isArchivedNullCheck = Expression.Equal( isArchivedProperty , Expression.Constant( null , typeof( bool ? ) ) );
                var isArchivedNullFunc = Expression.Lambda( isArchivedNullCheck , parameter );
                
                // check isArchived == false
                var isArchivedFalseCheck = Expression.Equal( isArchivedProperty , Expression.Constant( false , typeof( bool ? ) ) );
                var isArchivedFalseFunc = Expression.Lambda( isArchivedFalseCheck , parameter );
                
                // Combine the two expressions using OR
                conditions = Expression.OrElse( isArchivedNullFunc , isArchivedFalseFunc );
            }

MBARK9/23/2022

            
            if ( typeof( IAuditable ).IsAssignableFrom( entityType.ClrType ) )
            {
                var isDeletedProperty = MemberExpression.Property(parameter, "IsDeleted");
                
                // check isDeleted == null
                var isDeletedNullCheck = Expression.Equal( isDeletedProperty , Expression.Constant( null , typeof( bool ? ) ) );
                var isDeletedNullFunc = Expression.Lambda( isDeletedNullCheck , parameter );
                
                // check isDeleted == false
                var isDeletedFalseCheck = Expression.Equal( isDeletedProperty , Expression.Constant( false , typeof( bool ? ) ) );
                var isDeletedFalseFunc = Expression.Lambda( isDeletedFalseCheck , parameter );
                
                // Combine the two expressions using OR
                var isDeletedCExpression = Expression.OrElse( isDeletedNullCheck , isDeletedFalseCheck ); 

                // Add the Expression to the Conditions
                conditions = conditions == null ? isDeletedCExpression : Expression.AndAlso( conditions , isDeletedCExpression );
            }
            
            if ( conditions != null )
            {
                var filter = Expression.Lambda( conditions , parameter );
                modelBuilder.Entity( entityType.ClrType ).HasQueryFilter( filter );
            }
        }

MBARK9/23/2022

the problem is when I start my ASP.net core 6 app I get this exception

MBARK9/23/2022

InvalidOperationException: The binary operator OrElse is not defined for the types 'System.Func2[MBSM.Core.Entities.ContinuousFormation,System.Boolean]' and 'System.Func2[MBSM.Core.Entities.ContinuousFormation,System.Boolean]'.

MBARK9/23/2022

which my ContinuousFormation is :

 public class ContinuousFormation : IAuditable, IArchivable
    {
        // Other properties removed for clarity
        public string     CreatedBy    { get; set; }
        public DateTime   CreatedOn    { get; set; }
        public bool       IsEdited     { get; set; }
        public string     LastEditor   { get; set; }
        public DateTime   LastEditDate { get; set; }
        public bool?      IsDeleted    { get; set; }
        public string?    DeletedBy    { get; set; }
        public DateTime?  DeletedOn    { get; set; }

        public bool ?     IsArchived   { get; set; }
        public string     ArchivedBy   { get; set; }
        public DateTime ? ArchivedOn   { get; set; }
    }

MBARK9/23/2022

I hope if you have any idea about how do I can fix this issue ?

MBARK9/23/2022

and massive thanks in advance

MBARK9/23/2022

ooooh my gosh I just did a mistake instead of

 conditions = Expression.OrElse( isArchivedNullFunc , isArchivedFalseFunc );

I have to

 conditions = Expression.OrElse( isArchivedNullCheck , isArchivedFalseCheck );

MBARK9/23/2022

Massive thanks bro I really appreciate your help and effort

MBARK9/23/2022

all love <3

Contact Frequently Asked Questions Join The Discord Bugs & Feature Requests Terms & Privacy