Entity Framework Core Plus Query Filter

Introduction

In almost every application, there are some tables which contains inactive or soft deleted data. This kind of data should not be shown to the client or used anymore every time we query these tables, we must remove them with a WHERE clause. Major ORM like NHibernate have a filter feature to select records based on a predefined filter but, unfortunately for Entity Framework users, Query Filter is only available through third party library.

EF+ Query Filter lets you change the predefined query from the context generated by Entity Framework for your own Query.

• You can filter the query with a predicate to exclude soft deleted records:

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();

ctx.Filter<Post>(q => q.Where(x => !x.IsSoftDeleted));

// SELECT * FROM Post WHERE IsSoftDeleted = false
var list = ctx.Posts.ToList();

Try it

• You can control the default query to add a default sorting:

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();

ctx.Filter<Post>(q => q.Where(x => !x.IsSoftDeleted)
                       .OrderByDescending(x => x.ViewCount));

// SELECT * FROM Post WHERE IsSoftDeleted = false ORDER BY ViewCount
var list = ctx.Posts.ToList();

Try it

• You can use a predefined filter and enable it only for a specific query:

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();

ctx.Filter<Post>(MyEnum.EnumValue, q => q.Where(x => !x.IsSoftDeleted)).Disable();

// SELECT * FROM Post WHERE IsSoftDeleted = false
var list = ctx.Posts.Filter(MyEnum.EnumValue).ToList();

Try it

Options

• Global
• By Instance
• By Query
• By Inheritance/Interface
• By Enable/Disable
• By AsNoFilter

Real Life Scenarios

• Logical Data Partitioning
• Multi-Tenancy
• Object State
• Security Access
• Default Ordering

Limitations

• Entity Framework Core
  • Doesn't work with LazyLoading
  • Doesn't work with Include
  • DO NOT support filter by inheritance/interface (Will be supported when EntityFramework team will fix this issue)

Entity Framework Core - Limitations

A ForceCast option has been added to support temporary inheritance, but some LINQ method will no longer be working in combination with ForceCast.

QueryFilterManager.ForceCast = true;

Here is a list of known method that no longer work with query filtered with the ForceCast options enabled:

• Aggregate
• Max
• Min
• Sum

Conclusion

EF+ Query Filter is very powerful and very easy to use. Our filter version covers all kinds of requirements an application could have.

Need help getting started? info@zzzprojects.com

We welcome all comments, ideas and suggestions to improve our library.