One of the most awaited features of entity framework. With the help of Entity Framework Global Query Filter, you can filter the add configuration globally which will filter each and every query fetched from the database using the entity framework.
If you have searched for this query, it means you are aware of EF and its usage. Lets do not waste time on understanding what is Entity Framework.
What is the Entity Framework Global Filter?
By the name itself, you can realize what is the use of this feature. Adding data filtration criteria on a global level. So, you don’t have to write the same code condition everywhere in the project.
Let’s understand with an example.
I have created an Employee class in my project, which has a few properties on it, including a data filtering property called IsActive. This property will help us filter the non-active employees.
using System;
using System.Collections.Generic;
using System.Text;
namespace EfCoreDemo
{
public class Employee
{
public Guid Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public string ContactNo { get; set; }
public bool IsActive { get; set; }
}
}
We have four records in the database. where two records are active and the other two are inactive.
Now, let’s add one controller called EmployeeController. which can be used to display the list of employees using the Entity Framework.
using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
namespace EfCoreDemo.Mvc.Controllers
{
public class EmployeesController : Controller
{
private readonly EfDemoContext _context;
public EmployeesController(EfDemoContext context)
{
_context = context;
}
// GET: Employees
public async Task<IActionResult> Index()
{
return View(await _context.Employees.ToListAsync());
}
// GET: Employees/Details/5
public async Task<IActionResult> Details(Guid? id)
{
if (id == null)
{
return NotFound();
}
var employee = await _context.Employees
.FirstOrDefaultAsync(m => m.Id == id);
if (employee == null)
{
return NotFound();
}
return View(employee);
}
// GET: Employees/Create
public IActionResult Create()
{
return View();
}
// POST: Employees/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("Id,Name,Age,ContactNo,IsActive")] Employee employee)
{
if (ModelState.IsValid)
{
employee.Id = Guid.NewGuid();
_context.Add(employee);
await _context.SaveChangesAsync();
return RedirectToAction(nameof(Index));
}
return View(employee);
}
// GET: Employees/Edit/5
public async Task<IActionResult> Edit(Guid? id)
{
if (id == null)
{
return NotFound();
}
var employee = await _context.Employees.FindAsync(id);
if (employee == null)
{
return NotFound();
}
return View(employee);
}
// POST: Employees/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Edit(Guid id, [Bind("Id,Name,Age,ContactNo,IsActive")] Employee employee)
{
if (id != employee.Id)
{
return NotFound();
}
if (ModelState.IsValid)
{
try
{
_context.Update(employee);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!EmployeeExists(employee.Id))
{
return NotFound();
}
else
{
throw;
}
}
return RedirectToAction(nameof(Index));
}
return View(employee);
}
// GET: Employees/Delete/5
public async Task<IActionResult> Delete(Guid? id)
{
if (id == null)
{
return NotFound();
}
var employee = await _context.Employees
.FirstOrDefaultAsync(m => m.Id == id);
if (employee == null)
{
return NotFound();
}
return View(employee);
}
// POST: Employees/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<IActionResult> DeleteConfirmed(Guid id)
{
var employee = await _context.Employees.FindAsync(id);
_context.Employees.Remove(employee);
await _context.SaveChangesAsync();
return RedirectToAction(nameof(Index));
}
private bool EmployeeExists(Guid id)
{
return _context.Employees.Any(e => e.Id == id);
}
}
}
If you have observed line number 19 in the EmployeeController, we have an action method called Index, which returns a list of employees from the database using the Entity Framework context.
There are two ways in which we can filter the list of employees. As shown on the above page.
- by adding a where condition in the Index method of EmployeeController and returning only the active employees.
- By adding the Global Query Filter for Employees entity in the EfDbContext class
Let’s make some code changes for the first option.
// GET: Employees
public async Task<IActionResult> Index()
{
return View(await _context.Employees.Where(t=>t.IsActive).ToListAsync());
}
This will filter the list of employees for the index page. So, everywhere you load the list of active employees, you have to add this piece of code.
HasQueryFilter Method in Entity Framework Core
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace EfCoreDemo
{
public class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> modelBuilder)
{
modelBuilder.ToTable("Demo_Users");
modelBuilder.HasKey(t => t.User_Id).HasName("User_PK");
modelBuilder.Property(t => t.Email)
.HasColumnName("Email Addresss")
.IsRequired();
modelBuilder.Property(t => t.FullName)
.HasComputedColumnSql("[FirstName]+' '+[LastName]");
}
}
public class EfDemoContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Employee> Employees { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Data Source=DK_XPS;MultipleActiveResultSets=True;Initial Catalog=EfDemo;Integrated Security=True;Connection Timeout=180;");
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new UserConfiguration());
modelBuilder.Entity<UserProfile>();
modelBuilder.Entity<Address>();
modelBuilder.Entity<Role>();
modelBuilder.Entity<UserRole>();
modelBuilder.Entity<Employee>().HasKey(t=>t.Id);
modelBuilder.Entity<Employee>().HasQueryFilter(t=>t.IsActive);
base.OnModelCreating(modelBuilder);
}
}
}
We can configure the employee table to always fetch only active records using the HasQeueryExtension method. And, it will be applicable globally. Anyone making calls to these tables will always receive records that are active only.
eg: query of fetching employes list remain same.
// GET: Employees
public async Task<IActionResult> Index()
{
return View(await _context.Employees.ToListAsync());
}
Use cases for Entity Framework Global Query Filter
- Filtering the Tenant specific data using the TenantId column.
- Fetch only non-deleted records using SoftDelete columns like IsDeleted
Conclusion
We tried understanding the use of a global query filter in the entity framework core with the help of one example.
Comments are closed.