how to search record from single table with multiple parameters using LINQ?

Question

I am trying to search record(s) from table by appying multiple search parameters. as per below snap.

here by using various parameters as per above snap i want to filter the records. here user could enter any combination of parameter(s) to search record. i tried something like below code hich works for single condition but fails for combination of any search paramets.

    public List<students> SearchStudents(students search)
            {
     var result = new List<students>();
     var records= from stud in db.students
                                    where stud.enrollmentNumber== search.enrollmentNumber
                                    || stud.enrollmentDate==search.enrollmenttDate
                                    || stud.enrollmentType==search.enrollmentType
                                    || stud.className==search.className
                                    select new Search()
                                    {
                                        enrollmentNumber= stud.enrollmentNumber,
                                        enrollmentDate = stud.enrollmentDate,
                                        enrollmentType = stud.enrollmentType,                                    
                                        Name = stud.Name,
                                        className=stud.className,                                                                      
                                        Description = stud.Description
                                    };
           result = records.ToList();
return result;
    }

but this is not working properly. means it returns same result whatever parameters I pass.

Like in the table i ahve 20 records and the enrollment number is the unique value field in DB so here when i am passing enrollment number thats like "2018-0001" it returns all records when it should return only single reocrd. can someone guide me with this?

How exactly is it not working? Do you get an error and if so what is it? Or do you get incorrect results, and if so please include what is in your table and the values of the `search` object? — juharr, Sep 14 '18 at 11:57
That's still a very vague description. If you're not getting the expected results then we need to know exactly what is in your DB, what are the values in the `search` object sent into your method. What results do you get and what results do you expect. — juharr, Sep 14 '18 at 12:03
Your searching on `enrollmentNo`, but you select `enrollmentNumber`, so what's the difference between those? — juharr, Sep 14 '18 at 12:12
Well you are doing a OR of the `enrollmentNumber` with 3 other columns. So you'll get records that match any one of them. If you want only stuff that matches that exact `enrollmentNumber` just remove the rest of the logic. — juharr, Sep 14 '18 at 12:15
yes, for single condition it will work very fine but here i am trying that multiple where conditions to get data by applying multiple search parameters. — Madhav, Sep 14 '18 at 12:21
http://www.albahari.com/nutshell/predicatebuilder.aspx, Predicate builder can be a great tool too. — Drag and Drop, Sep 17 '18 at 07:17
Possible duplicate of [Add WHERE clauses to SQL dynamically / programmatically](https://stackoverflow.com/questions/11329823/add-where-clauses-to-sql-dynamically-programmatically) — Drag and Drop, Sep 17 '18 at 07:19

score 0 · Answer 1 · answered Sep 14 '18 at 12:25

Without further explanation in your question about how this isn't working, the best we can do is guess. However, one very plausible reason for this is because you're including parameters you don't want to be filtering on.

Because you're using ORs in your statement, if any of those other properties are defaulted in the database, you're going to be returning those records. What you need to be doing is conditionally including your pieces of the WHERE clauses for only the properties that you want to search on. Unfortunately, that is not possible with the "SQL syntax" version of LINQ, so you will need to convert your query to that. (Good news: It's slightly more performant as well as it usually has to convert the SQL to the method syntax.)

Because of deferred execution, your query will not be sent to the database until you call a .ToList() or something to actually start processing the results. This allows you to chain method calls together, even if they are completely different C# statements. This is what you'll want to do:

public List<students> SearchStudents(students search)
{
    var query = db.students;

    if (!string.IsNullOrWhiteSpace(search.enrollmentNumber))
    {
        query = query.Where(s => s.enrollmentNumber == search.enrollmentNumber);
    }

    if (search.enrollmentDate != DateTime.MinValue)
    {
        query = query.Where(s => s.enrollmentDate == search.enrollmentDate);
    }

    if (!string.IsNullOrWhiteSpace(search.enrollmentType))
    {
        query = query.Where(s => s.enrollmentType == search.enrollmentType);
    }

    if (!string.IsNullOrWhiteSpace(search.className))
    {
        query = query.Where(s => s.className == search.className);
    }

    return query.Select(stud => new Search
                                {
                                    enrollmentNumber= stud.enrollmentNumber,
                                    enrollmentDate = stud.enrollmentDate,
                                    enrollmentType = stud.enrollmentType,                                    
                                    Name = stud.Name,
                                    className=stud.className,                                                                      
                                    Description = stud.Description
                                })
                .ToList();
}

You may need to adjust the if statements in there to accommodate different data types than what is intuitive from the names, but this will only add the filter if a value has been provided.

how to search record from single table with multiple parameters using LINQ?

1 Answers1