1

I have some filter parameters in the Controller of an ASP.NET MVC project and I need to create Where clause dynamically according to these parameters. If isActive parameter is true, it will get the records having the StatusId = 1. There are also userName and labId parameters in the method that should be matched in the Where clause. 

public ActionResult GetStudents(int labId, string userName, bool isAll)
{
    var allRecords = repository.Students;
    //If isAll, get all the records having StatusId = 1
    var result = allRecords.Where(m => (isAll) || m.StatusId == 1); 
    //??? 
}

I use the filter above, but I have no idea what is the most suitable way (conventions) for multiple parameters in order to fetch the result fast. Any idea?

Note: I want to filter for all of three parameters and Where clause should contain all of the combinations according to the parameter's values (also is null or empty).

Improve this question
19
  • 2
    if (isAll) { allRecords = allRecords.Where(m => m.StatusId == 1; } else { ..} Commented Oct 5, 2016 at 7:33
  • 1
    Multiple Where clauses are joined together by ands. So you can add them as you need as @StephenMuecke showed. Commented Oct 5, 2016 at 7:37
  • 1
    @ClintEastwood, You have not explained what combinations you want. Do you also want to filter by userName if that is not null? in which case add if (userName != null) { allRecords = allRecords.Where(...); } Commented Oct 5, 2016 at 7:41
  • 2
    Since userName can be null, then you need the 3 if clauses (othewise .Where(x => x.UserName == userName) would return no records. Commented Oct 5, 2016 at 7:49
  • 2
    @ClintEastwood, You need to tell us what properties of your model you searching. I'm guessing its UserName == userName? And what about labId - does you model have a property for that, and can its value be 0? Commented Oct 5, 2016 at 7:51

4 Answers 4

Reset to default
3

You can concatenate linq-methods as they all return an IEnumerable<T> and are combined using something like an SQL-And (dependinng on what LINQ-provider you use):

IEnumerable<Student> result = allRecords;
if(labelId.HasValue) 
    result = result.Where(x => x.LabelId == labelId);
else
    result = result.Where(x => x.LabelId == 0); // or whatever your default-behaviour is

if(isAll)
    result = result.Where(x => x.StatusId == 1);
else 
    result = result.Where(x => x.StatusId == 0); // or whateever your default-behaviour is when isAll is false

if(!String.IsNullOrEmpty(userName)) 
    result = result.Where(x => x.Name == userName);
else 
    result = result.Where(x => x.Name == "Claus"); // or whatever the default-behaviour is when the param isn´t set
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

AIthough I followed the approach shown by StephenMuecke, vote up your answer as this is also useful. Thanks...
2 
var predicate = PredicateBuilder.False<Record>();

if(isAll)
  predicate = predicate.AND(d => d.StatusId ==1);

 predicate = predicate.AND(d => d.labID == labid && d.username = username);

 return allRecords.Where(predicate);`

You can use a predicate builder

Improve this answer

2 Comments

Thanks for reply, but I do not think to use an external tool as predicate builder.
And if you look at github.com/aspnetboilerplate/aspnetboilerplate/issues/699 you will see predicate builder taken a little bit further.
1

Do like this

public ActionResult GetStudents(int labId, string userName, bool isAll)
{
    var allRecords = repository.Students;
    //If isAll, get all the records having StatusId = 1
    if (isAll) 
    { 
       var result = allRecords.Where(m => m.StatusId == 1  && m.UserName == userName  && m.LabId == labId); 
    } 
    else 
    { 
       // do else things
    }
}
Improve this answer

2 Comments

Could you please post the full clause by adding it to other parameters? Because labId and userName combinations make me confused.
AIthough I followed the approach shown by StephenMuecke, vote up your answer as this is also useful. Thanks...
1

you need something like below

public ActionResult GetStudents(int labId, string userName, bool isAll)
{
    var allRecords = repository.Students;
    //If isAll, get all the records having StatusId = 1
    if (isAll) 
    { 
        var result = allRecords.Where(m => m.StatusId == 1 
                                            && m.LabId == labId 
                                            && m.UserName == username);
        //or
        var result = from record in allRecords
                         where record != null &&
                               record.StatusId == 1
                               && !string.IsNullOrWhiteSpace(record.UserName)
                               && record.UserName.Equals(username)
                               && record.Labid = labId
                         select record;
    } 
    else 
    { 
       // do else things
    }
}
Improve this answer

3 Comments

I want to filter for all of three parameters and Where clause should contain all of the combinations according to the parameter's values (also is null or empty).
you can chain all your Boolean conditions like shown above.
AIthough I followed the approach shown by StephenMuecke, vote up your answer as this is also useful. Thanks...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.