LinQ query Simple Where clause and many filter criteria

Question

I need to filter data entity but there is no predefined columns, which I will have to filter.

public class EventDocument
{
    public string ID1 { get; set; }
    public int ID2 { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Number { get; set; }
    public virtual ICollection<Event> Events { get; set; }
}

I created all needed properties: SearchFirstName, SearchLastName, SearchNumber

And now I am trying to filter EventDocument by these parameters entered by user. If user enters value into SearchFirstName, my query will look like this:

var query = from b in DBContext.EventDocuments
                    where b.FirstName.Contains(SearchFirstName)
                    select b;

If user enters values to SearchFirstName and SearchLastName, my query will look like this:

var query = from b in DBContext.EventDocuments
                        where b.FirstName.Contains(SearchFirstName) 
                        && b.LastName.Contains(SearchLastName)
                        select b;

And how to construct query if I don't know - which filed user will fill? Maybe he will enter value to SearchLastName and SearchNumber...

score 7 · Answer 1 · edited May 04 '17 at 11:44

7

You could do something like this

var query = DBContext.EventDocuments;

if(!string.IsNullOrEmpty(SearchFirstName))
{
    query = query.Where(x => x.FirstName.Contains(SearchFirstName));
}
if(!string.IsNullOrEmpty(SearchLastName))
{
    query = query.Where(x => x.LastName.Contains(SearchLastName));
}

var result = query.ToList();

and building your query based on what conditions you need.

edited May 04 '17 at 11:44

Tim Schmelter

450,073
74
686
939

answered May 04 '17 at 11:42

Mighty Badaboom

6,067
5
34
51

Thank You, I didn't finish correctly my question. Now I am doing axactly like You wrote... but also looking how to solve this situation not using lambdas ... – Songaila May 04 '17 at 11:47
Just because I'm interesting. Why do you want to use no lambdas? – Mighty Badaboom May 04 '17 at 11:50
Acctually I am trying to filter a big amount of records and search the fastes way to do it (need to joint two table also... not lucky) And I would be able to define search mechanizm depending on what user will post – Songaila May 04 '17 at 11:53
1

If speed is your doubt there should be no difference I thought. Maybe [Linq query or Lambda expression?](http://stackoverflow.com/questions/16185514/linq-query-or-lambda-expression) will help – Mighty Badaboom May 04 '17 at 11:56
@Songaila: there is no difference in performance whether you use method or query syntax. The query syntax will be translated to method syntax anyway because it's just syntactic sugar. – Tim Schmelter May 04 '17 at 12:04
Actually in my situation Lambda expression works faster! – Songaila May 04 '17 at 12:24
Significant faster? – Mighty Badaboom May 04 '17 at 12:30
DBContext.EventDocuments will return a DBSet<> and Where() will return an IQueryable<>. How do those two match? – Ε Г И І И О Aug 29 '19 at 03:32

score 2 · Accepted Answer · edited Jul 18 '17 at 12:15

2

Predicate Builder is an effective way.

Put following code in static PredicateBuilder class,

 public static Expression<Func<T, bool>> ContainsPredicate<T>(string memberName, string searchValue)
        {
            var parameter = Expression.Parameter(typeof(T), "m");
            var member = Expression.PropertyOrField(parameter, memberName);
            var body = Expression.Call(
                member,
                "Contains",
                Type.EmptyTypes, // no generic type arguments
                Expression.Constant(searchValue)
            );
            return Expression.Lambda<Func<T, bool>>(body, parameter);
        }

In your cs file,

var filterResults = PredicateBuilder.ContainsPredicate<Employee>(columnName, searchName);

And yourLinqQuery.Where(filterResults);

For Reference: Predicate Builder Details

edited Jul 18 '17 at 12:15

Ali

3,373
5
42
54

answered May 31 '17 at 13:15

OnDoubt

129
11

And could You suggest what to write instead of columnName here: var filterResults = PredicateBuilder.ContainsPredicate(columnName, searchName); – Songaila Jun 01 '17 at 13:11
Can you please elaborate. I couldn't get you. As per the code, you can pass Employee properties as columnName. Are you expecting this? – OnDoubt Jun 02 '17 at 04:56
Thank You, I get it at least, I passed columnName as string parameter.. Now I am going to try pass a list of parameters and memberNames – Songaila Jun 05 '17 at 06:56

score 1 · Answer 3 · answered May 04 '17 at 11:40

1

I would recommend you to construct the query dynamically using Dynamic Linq.

https://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library

answered May 04 '17 at 11:40

Espen Tveit

11
1

1

Consider including code snippets in your answer, online resources can be removed. – Gnqz May 04 '17 at 11:46

score 1 · Answer 4 · answered May 04 '17 at 11:48

 var query = from eventDocument in DBContext.EventDocuments
                     where string.IsNullOrEmpty(firstName) || eventDocument.FirstName.Contains(firstName)
                     && string.IsNullOrEmpty(lastName) || eventDocument.LastName.Contains(lastName)
                     && string.IsNullOrEmpty(number) || eventDocument.Number.Contains(number)
                     select eventDocument;

score 1 · Answer 5 · answered May 04 '17 at 11:52

Below worked for me

  var query = from b in DBContext.EventDocuments
                    where (b.FirstName.Contains(SearchFirstName) && (!string.IsNullOrEmpty(SearchFirstName))
                    && (b.LastName.Contains(SearchLastName) && (!string.IsNullOrEmpty(SearchLastName))
                    && (b.SearchNumber.Contains(SearchNumber) && (!string.IsNullOrEmpty(SearchNumber))
                   select b;

I tried with below

List<EventDocument> lsteventDoc = new List<EventDocument>() { 
            new EventDocument{ID1 ="1", ID2=2, FirstName ="", LastName="Test", Number="1"},
            new EventDocument{ID1 ="1", ID2=2, FirstName ="test", LastName="Test", Number="1"},
            new EventDocument{ID1 ="1", ID2=2, FirstName ="", LastName="", Number="1"},
        };
        string SearchFirstName = "test";
        string SearchLastName="Test";
        string SearchNumber="1";
        var query = from b in lsteventDoc
                    where (b.FirstName.Contains(SearchFirstName) && (!string.IsNullOrEmpty(SearchFirstName)))
                    && (b.LastName.Contains(SearchLastName) && (!string.IsNullOrEmpty(SearchLastName)))
                    && (b.Number.Contains(SearchNumber) && (!string.IsNullOrEmpty(SearchNumber)))
                   select b;

Hello. And what about if user enter only SearchFirstName? – Songaila May 05 '17 at 07:18 — Songaila, May 05 '17 at 07:18

score 1 · Answer 6 · answered May 04 '17 at 11:57

var query = from b in DBContext.EventDocuments
    where (b.FirstName.Contains(SearchFirstName) || SearchFirstName == "") 
    && (b.LastName.Contains(SearchLastName) || SearchLastName == "")
    && (b.Number.Contains(SearchNumber) || SearchNumber == "")
select b;

LinQ query Simple Where clause and many filter criteria

6 Answers6