CODEWITHSUNDEEP
c#databaselinqsearchfilter
Dr.Denis McCracleJizz
Dr.Denis McCracleJizz

Reputation: 870

Using linq to filter a search

I am trying to use Linq to query a database from a search user control.

I have this Linq query that searches the database with a user's input.

This is the code I'm currently using:

var query = from invite in db.invites
            where invite.Division == userInput.Division.Text &&
                  invite.Status == userInput.Status.Text
            select invite;

The problem I have is that if either one is empty I need to not take it into account for the search filter. Is there a way to do this properly, taking in consideration that I have many filters and not just status and division?

Upvotes: 0

Views: 2777

Answers (4)

Todd Skelton
Todd Skelton

Reputation: 7239

If you want to use some extensions, I've built a library to make this a little more readable. https://www.nuget.org/packages/LinqConditionalExtensions

var hasDivision = !string.IsNullOrEmpty(userInput.Division.Text);
var hasStatus = !string.IsNullOrEmpty(userInput.Status.Text);

var query = db.Invites
    .WhereIf(hasDivision, invite => invite.Division == userInput.Division.Text)
    .WhereIf(hasStatus, invite => invite.Status == userInput.Status.Text);

You can read more about it here: https://github.com/xKloc/LinqConditionalExtensions

Upvotes: 0

Zdeslav Vojkovic
Zdeslav Vojkovic

Reputation: 14591

This should work:

var query = from invite in db.invites
        where invite.Division.Matches(userInput.Division.Text) &&
              invite.Status.Matches(userInput.Status.Text)
        select invite;

static class Extensions
{
    public static bool Matches(this string text, string value)
    {
      if(string.IsNullOrEmpty(value)) return true;
      return text == value; // or same safer comparison
    }
}

there are better ways to build queries, but this is simple (and Matches needs a better name).

As @willem notes, I missed the point of query being executed on the database which seems likely (db.invites) - however, I am leaving the answer here as it works for Linq to Objects queries.

Upvotes: 1

ikwillem
ikwillem

Reputation: 1064

var query = from invite in db.invites
        where invite.Division.Contains(userInput.Division.Text) &&
              invite.Status.Contains(userInput.Status.Text)
        select invite;

or

var query = from invite in db.invites select invite;
if (!string.IsNullOrEmpty(userInput.Division.Text)) query = query.Where(invite => invite.Division == userInput.Division.Text);
if (!string.IsNullOrEmpty(userInput.Division.Text)) query = query.Where(invite => invite.Status == userInput.Status.Text);

Upvotes: 0

Servy
Servy

Reputation: 203815

Thanks to deferred execution you can conditionally build the query in pieces.

var query = db.invites.AsQueryable();

if(!string.IsNullOrEmpty(userInput.Division.Text))
    query = query.Where(invite => invite.Division == userInput.Division.Text);

if(!string.IsNullOrEmpty(userInput.Status.Text))
    query = query.Where(invite => invite.Status== userInput.Status.Text);

Upvotes: 5

Related Questions