C
C#•3y ago
M B V R K

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 @Database
6 Replies
M B V R K
M B V R KOP•3y ago
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 );

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

            }
            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 );
            }
        }
            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
gerard•3y ago
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. You might want to combine the two expressions with Expression.AndAlso
Unknown User
Unknown User•3y ago
Message Not Public
Sign In & Join Server To View
M B V R K
M B V R KOP•3y ago
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 ? I'm gonna crazy with this issue hmmmmm, omg maybe I should write query filter for every entity manually
gerard
gerard•3y ago
That shouldn't be an issue, the parameter in the filter is the entity itself: 
var parameter = Expression.Parameter( entityType.ClrType , "p" );
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. 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 );
    }
}
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 );
    }
}
M B V R K
M B V R KOP•3y ago
@gerard 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 );
            }
  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 );
            }
            
            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 );
            }
        }
            
            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 );
            }
        }
the problem is when I start my ASP.net core 6 app I get this exception
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]'.
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; }
    }
 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; }
    }
I hope if you have any idea about how do I can fix this issue ? and massive thanks in advance ooooh my gosh I just did a mistake instead of 
 conditions = Expression.OrElse( isArchivedNullFunc , isArchivedFalseFunc );
 conditions = Expression.OrElse( isArchivedNullFunc , isArchivedFalseFunc );
I have to 
 conditions = Expression.OrElse( isArchivedNullCheck , isArchivedFalseCheck );
 conditions = Expression.OrElse( isArchivedNullCheck , isArchivedFalseCheck );
Massive thanks bro I really appreciate your help and effort all love <3

Did you find this page helpful?