Extending DbSet for easy filter expressions

Databases often have entities which are often by a repeated expression. A common one is filtering something by a UserId. In SQL, this looks like:

select * 
from orders
where userid = @userid;

Using Entity Framework, we may write something like this:

dataContext.Orders.Where(x => x.UserId == userId);

But I’d really like to make it more expressive and consistent, like this:


Fortunately, it is possible, with an interface and an extension method.

The interface, which I will call IUserEntity, will expose the common filtered expressions.

public interface IUserEntity
	int UserId { get; set; }
	User User { get; set; }

Any class that can be filtered by users should inherit this class.

public class Order : IUserEntity
	public int Id { get; set; }
	public int UserId { get; set; }
	public User User { get; set; } = null!;

Then our expression method will extend any DbSet with a type of IUserEntity to include our extension method, which simply returns a filtered DbSet.

public static class DbSetExtensions
	public static IQueryable<T> ForUser<T>(this DbSet<T> userEntities, int? userId) where T : class, IUserEntity
		if (userId.HasValue)
			return userEntities.Where(x => x.UserId == userId.Value);
		return userEntities;

Note how in the above I made the userId nullable — this is not required, but it does give you a bit more flexibility.

This can be replicated for any other common filter, which will make your code more expressive and easy to read. If you wanted similar extensions on other elements, such as Lists, you could just make a copy of the extension method for that object type, and the appropriate return types.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.