Filtering DbContext data dynamically by user input in WPF applications

Question

I need to filter database data based on filters available to the end user in the form of search term text box, select boxes etc.

I have put together this code and need feedback if this is a good way to do it or if there are any better solutions.

using Multi.Model;
using System;
using System.Linq;
using System.Windows.Controls;
namespace Multi.Pages
{
 /// <summary>
 /// Interaction logic for Page1.xaml
 /// </summary>
 public partial class Page1 : Page
 {
 public Page1()
 {
 InitializeComponent();
 textBoxName.TextChanged += TextBoxName_TextChanged;
 PopulateDataGrid();
 }
 private void PopulateDataGrid()
 {
 using (var db = new optisysEntities())
 {
 var items = db.clients.AsQueryable();
 items = FilterClients(db, items);
 dataGrid.ItemsSource = items.ToList();
 }
 }
 private System.Linq.IQueryable<Multi.Model.clients> FilterClients(optisysEntities db, System.Linq.IQueryable<Multi.Model.clients> clients)
 {
 if (!String.IsNullOrWhiteSpace(textBoxName.Text)) clients = db.clients.Where(c => c.name.Contains(textBoxName.Text)
 || c.phone.Contains(textBoxName.Text));
 // if (!String.IsNullOrWhiteSpace(search.Email)) clients = clients.Where(u => u.Email.Contains(search.Email));
 // if (search.UsertypeId.HasValue) clients = clients.Where(u => u.UsertypeId == search.UsertypeId.Value);
 return clients;
 }
 private void TextBoxName_TextChanged(object sender, TextChangedEventArgs e)
 {
 PopulateDataGrid();
 }
 }
}

• t3chb0t: What's with the code that is commented out?
• W.M.: @t3chb0t, they were just examples of possible filtering elements (filter by email string containing .., has category x, etc..)

Answer 1

Reducing your code to what I think is the core of your question:

private System.Linq.IQueryable<Multi.Model.clients> FilterClients(optisysEntities db, System.Linq.IQueryable<Multi.Model.clients> clients)
{
 if (!String.IsNullOrWhiteSpace(textBoxName.Text)) 
 clients = db.clients.Where(c => c.name.Contains(textBoxName.Text) || c.phone.Contains(textBoxName.Text));
 if (!String.IsNullOrWhiteSpace(search.Email)) 
 clients = clients.Where(u => u.Email.Contains(search.Email));
 if (search.UsertypeId.HasValue) 
 clients = clients.Where(u => u.UsertypeId == search.UsertypeId.Value);
 return clients;
}

Yes, this is one of the better ways to do dynamic filtering.

---

I would suggest abstracting the data retrieval into a separate layer. You don't want your form logic handling your underlying ORM directly.

This is exacerbated by the fact that you've put the FilterClients() method by itself. Your UI form therefore contains a method whose reponsibility has nothing to do with the UI.

However, I get the feeling that this application is either tiny, or has only just been developed. So I can understand that this abstraction is something for a later stage. I would suggest doing it immediately to make it less painful in the future, but that's your choice.

• W.M.: Thank you very much @Flater for the valuable information. I wrote that mostly for learning about EF and WPF. I am not sure if you had paid attention to var items = db.clients.AsQueryable(); items = FilterClients(db, items); dataGrid.ItemsSource = items.ToList();, does this involve calling the database twice or once (I mean because of db.clients.AsQueryable() and then in next line FilterClients(db, items))?
• Flater: @W.M. As long as it stays an IQueryable object, and you don't access the data (e.g. look at one of the items on the list, or count how many there are in the list), your code should not yet connect to the database. ToList, however, enumerates the data which does involve fetching it from the db. There are a lot of ins and outs as to when data is instantiated, this comment is giving you the simple rundown.