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:

Example

// 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();
  • You can control the default query to add a default sorting:

Example

// 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();
  • You can use a predefined filter and enable it only for a specific query:

Example

// 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();

EF+ Query Filter Global

Global filter can be used by any context.

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

Example

// 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();

Use entities context constructor to initialize global filter by default.

EF+ Query Filter By Instance

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

Example

// 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();

Use entities context constructor to make some filter “global” to all context.

EF+ Query Filter 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.

Example

// 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();

EF+ Query Filter By Inheritance/Interface

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

Example

// 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();

EF+ Query Filter 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.

Example

// 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();

EF+ Query Filter AsNoFilter

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

Example

// 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();

Real Life Scenarios

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

Example

// 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();

Many categories by product

Example

// 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.

Example

// 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();

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. By example, the customer cannot be deleted because related orders cannot be deleted instead, he becomes inactive.

In this example, we display only active category.

Example

// 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();

Security Access

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

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

Example

// 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();

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

Example

// 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()

Include Filtering

Including related entities can be useful. By example, every time you load a post, you want to include all active comments related.

Require: EF+ Query Include

In this example, All active comments are included for every post.

Example

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

// Load all active comments related to post loaded
var list = ctx.Posts.Take(20).ToList();

Limitations

  • Entity Framework 5
    • Doesn’t work with LazyLoading
    • Doesn’t work with Include 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 6 - Limitations

Property Filter

To some backward compatibility, filtering works already on collection but are not enabled on a property by default.

You must enable the option AllowPropertyFilter

Example

QueryFilterManager.AllowPropertyFilter = true;

using (var ctx = new EntityContext())
{
ctx.Filter<Invoice>(q => q.Where(x => !x.IsDeleted));
ctx.Filter<InvoiceItem>(q => q.Where(x => !x.IsDeleted));

var list = ctx.InvoiceItems.Where(x => x.Invoice.Total > 400).Include("Invoice").ToList();
}

Context Filter

Since the QueryCacheManager is global, our library have some limitation with Filter by context (Global Filter doesn’t have this issue since it apply the same logic to all query.

  • LazyLoading only work with GlobalFilter
  • context.Set only work with GlobalFilter (You can use the method SetFiltered instead)

Example

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.SetFiltered<Post>().ToList();

For this kind of scenario, we recommend using instead: EntityFramework.DynamicFilters.

Entity Framework Core - Limitations

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

Example

QueryFilterManager.ForceCast = true;

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

  • Aggregate
  • Max
  • Min
  • Sum

Requirements

  • EF+ Query Filter: Full version or Standalone version
  • Database Provider: All supported
  • Entity Framework Version: EF5, EF6, EF Core
  • Minimum Framework Version: .NET Framework 4

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.