Entity Framework Core Plus Query Filter

Description

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

Global

Global filter can be used by any context.

Global filter is normally preferred in most scenarios over instance filter since the filter code is centralized in one method over being spread in multiple methods.

// using Z.EntityFramework.Plus; // Don't forget to include this.
QueryFilterManager.Filter<Customer>(q => q.Where(x => x.IsActive));

var ctx = new EntitiesContext();
// TIP: You can also add this line in EntitiesContext constructor instead
QueryFilterManager.InitilizeGlobalFilter(ctx);

// SELECT * FROM Customer WHERE IsActive = true
var list = ctx.Customers.ToList();

Try it

Use entities context constructor to initialize global filter by default.

By Instance

Instance filter applies filters to the current context only. The filtering logic is added once the context is created.

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

ctx.Filter<Customer>(q => q.Where(x => x.IsActive));

// SELECT * FROM Customer WHERE IsActive = true
var list = ctx.Customers.ToList();

Try it

Use entities context constructor to make some filter "global" to all context.

By Query

Query filter applies filters to specific queries only. The filtering logic is added globally or by instance but in a disabled state and then it is enabled by these specific queries.

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

// CREATE a disabled filter
ctx.Filter<Customer>(MyEnum.EnumValue, q => q.Where(x => x.IsActive), false);

// SELECT * FROM Customer WHERE IsActive = true
var list = ctx.Customers.Filter(MyEnum.EnumValue).ToList();

Try it

By Inheritance/Interface

Filter can be enabled and disabled by class inheritance and interface.

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

// CREATE filter by inheritance
ctx.Filter<BaseDog>(q => q.Where(x => !x.IsDangerous));

// CREATE filter by interface
ctx.Filter<IAnimal>(q => q.Where(x => x.IsDomestic));

// SELECT * FROM Cat WHERE IsDomestic = true
var cats = ctx.Cats.ToList();

// SELECT * FROM Dog WHERE IsDomestic = true AND IsDangerous = false
var dogs = ctx.Dogs.ToList();

Try it

By Enable/Disable

Filters are very flexible, you can enable and disable them at any time and only for a specific inheritance or interface if desired.

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

// CREATE filter by interface
ctx.Filter<IAnimal>(MyEnum.EnumValue, q => q.Where(x => x.IsDomestic));

// DISABLE filter only for class inheriting from BaseDog
ctx.Filter(MyEnum.EnumValue).Disable();

// SELECT * FROM Dog
var dogs = ctx.Dogs.ToList();

// ENABLE filter
ctx.Filter(MyEnum.EnumValue).Enable();

// SELECT * FROM Dog WHERE IsDomestic = true
var dogs = ctx.Dogs.ToList();

Try it

By AsNoFilter

You can bypass all filters by using AsNoFilter method in a query if a special scenario doesn't require filtering.

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

this.Filter<Customer>(q => q.Where(x => x.IsActive));

// SELECT * FROM Customer WHERE IsActive = true
var list = ctx.Customers.ToList();

// SELECT * FROM Customer
var list = ctx.Customers.AsNoFilter().ToList();

Try it

Real Life Scenarios

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

Logical Data Partitioning

A common scenario is to retrieve products by category or the ones available only for a specific country. All data are stored in the same table but only a specific range should be available.

In this example, we retrieve only the products available for the selected category.

Single category by product

// myCategoryID = 9

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();
ctx.Filter<Product>(q => q.Where(x => x.CategoryID == myCategoryID));

// SELECT * FROM Product WHERE CategoryID = 9
var list = ctx.Products.ToList();

Try it

Many categories by product

// myCategoryID = 9

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();
ctx.Filter<Product>(x => ctx.ProductByCategory.Any(
                          y => y.CategoryID == myCategoryID 
                               && y.ProductID == x.ProductID))

// SELECT * FROM Product AS X WHERE EXISTS
//   (SELECT 1 FROM ProductByCategory AS Y 
//        WHERE Y.CategoryID = 9 AND Y.ProductID = X.ProductID)
var list = ctx.Products.ToList();

Multi-Tenancy

An example of multi-tenancy is an online store for which the same instance of the database is used by multiple independent applications or clients and the data should not be shared between them.

Learn more about Multi-tenancy

In this example, the application is a tenant. The customer can only see invoice from the current application.

// myApplicationID = 9
// myCustomerID = 6

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();
ctx.Filter<IApplication>(q => q.Where(x => x.ApplicationID == myApplicationID));

// SELECT * FROM Invoice WHERE ApplicationID = 9 and CustomerID= 6
var list = ctx.Invoices.Where(q => q.Where(x => x.CustomerID = myCustomerID)).ToList();

Try it

Object State

Removing inactive or soft deleted records is probably the most common scenario. A soft delete is often useful when related data cannot be deleted. For example, the customer cannot be deleted because related orders cannot be deleted instead, he becomes inactive.

In this example, we display only active category.

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();
ctx.Filter<ISoftDeleted>(q => q.Where(x => !x.IsSoftDeleted));

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

Try it

Security Access

Viewing sensible data often requires some permissions. For example, not everyone can see all posts in a forum.

In this example, some posts are only available by role level.

// myRoleID = 1; // Administrator

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();
ctx.Filter<Post>(x => q => q.Where(x.RoleID >= myRoleID));

// SELECT * FROM Posts WHERE RoleID >= 1
var list = ctx.Posts.ToList();

Try it

Default Ordering

Default ordering can be often useful for base table like category. No matter the query, you probably want to show categories by alphabetic order.

In this example, categories are sorted by name

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();
ctx.Filter<Category>(q => q.OrderByDescending(x => x.Name));

// SELECT * FROM Category ORDER BY Name
var list = ctx.Categories.ToList()

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.

Prime Library

Performance

Expression Evaluator

More Projects...

Fastest Entity Framework Extensions

Entity Framework Core Plus Query Filter

Description

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 - Limitations

Conclusion

Performance

Expression Evaluator

Related