How to use Global Query Filter in Entity Framework core 2.0

Global Query Filter is used for adding the data filtration criteria on a global level. So, you don't have to write the same code condition everywhere in the project.

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest

Hello Everyone, Welcome back, today we are going to discuss the one of the great feature available in Entity Framework core 2.0 called (Global Query Filter).

What is Entity Framework core 2.0?

Entity Framework core is an ORM tool available for.Net core applications. However, this is not the continuation of an Entity Frmework series. It is the separate project itself. With lot more modular approach and support of multiple databases like MySql, SqlLite, and InMemory.  InMemory database is quite helpful for writing test cases, also at the time of the development phase.

What is the Global Query 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 start with some example, so you can understand it in a better way

I have created an Employee class in my project, which has few properties on it including a data filtering property called IsActive. This property will help us to filter the nonactive employes.

 

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; }
    }
}

And, We have four records in the database. Where two records is active and the other two are inactive.

Id Name Age Contacto IsActive
3459E39D-D153-435F-A2E1-16CFD50DAB20 San 29 8763456727 1
A7BDBD84-D917-4FE7-8D31-96738E80F8BE Kapil 29 98373635 0
0BCC817F-9426-4F15-BB06-B4B4035DFF9A Deependra 29 918147118963 1
F437B4F0-7E4B-49AD-AE0A-BB05406F41D4 DK 29 8293838789 0

Now, let’s add one controller called EmployeeController. Which can be used to display the list of employees using 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 the line no# 19 in the EmployeeController, We have an action method called Index, which is returning list of Employees from database using Entity Framework context.

Global Query Filter,Entity Framework Core 2.0There are two ways by which we can filter the list of employees. Shown in the above page.

  1. By, adding where condition in the Index method of  EmployeeController and returns only the active employees.
  2. By, adding 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.

Let’s make some code changes for the second option.

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);
        }
    }
}

As you can see in the above EmployeeDbContext class, we have added additional configuration for filtering the Employees. Using the extension method called HasQueryFilter. Where you can define the condition for one of the property. 

Once you add this configuration in the Dbcontext. You don’t have to repeat yourself.

// GET: Employees
   public async Task<IActionResult> Index()
   {
       return View(await _context.Employees.ToListAsync());
   }

Now, Again we have modified the index action method in the EmployeeController class. As you can see in the above piece of code, we have removed the where clause from the code.  Because we have already applied the filtration in the EfDbContext configuration.

Let see how the results look like on the front page.

Global Query Filter,Entity Framework 2.0,Beetechnical

Best fit use cases for Global Query Filter

TenantID/AccountId/CustomerId: Different name but the same but same task, If you have ever worked on the SASS applications. Adding tenantId in each table is one of the strategies. And, putting TenantId in the Global filter can be quite helpful.

IsDeletd: In many cases, you want to just soft delete the record instead of doing hard delete. And, While retrieving the data you want to get only the records where IsDeleted is false. So, putting IsDeleted in the Global filter can be quite helpful.

If you have any other use case where you can utilize the concept of Global Query Filter. We will be happy to extend the list. You can update us on below comments section.
I hope this article was useful. If you have any comments or concerns. Let us know in the below comment section.

 

 

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Share on whatsapp
Share on email
Author Bio

Leave a Reply

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

Recommended for you
Connect With Us
Stay Connected

Subscribe to our newsletter today to receive updates on latest tech news, tutorials SEO tips many more.

Latest Articles

Free hub for guest blogging nerds and newbie writers

  • Submit Guest Post
  • Share your Knowledge
  • Build your Backlinks
  • Advertise your products
  • Make money by writing
Subscribe to our newsletter today to receive updates on latest tech news, tutorials SEO tips many more.