C#
C#

help

Root Question Message

MBARK
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
MBARK
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 );

            }
MBARK
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 );
            }
        }
gerard
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.
gerard
gerard9/23/2022
You might want to combine the two expressions with Expression.AndAlso
Thaumanovic
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)
MBARK
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 ?
MBARK
MBARK9/23/2022
I'm gonna crazy with this issue
MBARK
MBARK9/23/2022
hmmmmm, omg maybe I should write query filter for every entity manually
gerard
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.
gerard
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 );
    }
}
MBARK
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 );
            }
MBARK
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 );
            }
        }
MBARK
MBARK9/23/2022
the problem is when I start my ASP.net core 6 app I get this exception
MBARK
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]'.
MBARK
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; }
    }
MBARK
MBARK9/23/2022
I hope if you have any idea about how do I can fix this issue ?
MBARK
MBARK9/23/2022
and massive thanks in advance
MBARK
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 );
MBARK
MBARK9/23/2022
Massive thanks bro I really appreciate your help and effort
MBARK
MBARK9/23/2022
all love <3
ContactFrequently Asked QuestionsJoin The DiscordBugs & Feature RequestsTerms & Privacy