Advanced Search in LINQ Query

Question

I want to make advanced search (filtering) using different criteria. For example, if first name is available then query should return all the available matches. IF first name and last name both are available then it should match first name or last name and similar way many criteria (gender, profession, education etc.)

Here is my controller Method in which I am taking all the ajax data as parameters.

 [HttpPost]
    public ActionResult Search(string cno, string fname, string lname, string male, string female, string stateid, string cityid, string professionid, string educationid)
    {
        if (Request.IsAjaxRequest())
        {
            var db = new clubDataContext();

            /*----------Advanced Search Query-------------*/

            return PartialView("SerachResult");
        }
        else
            return RedirectToAction("Index", "home");
    }

It may possible that some parameters have null value. So please suggest LINQ Query that is most suitable in this situation.

It's not *write code for me* website. It's *I have that code and it does not work/I don't know how to make that part of it* website. Show your effort, and come back when you're stack on something. — MarcinJuraszek, Apr 22 '14 at 06:25

RollerCosta · Accepted Answer · 2014-04-22T06:46:36.647

4

You have an option to use c# null-coalescing operator

Users.Where(x => x.firstName == (fname??x.firstName) || x.lastName == (lname?? x.lastName)).ToList();

further

Users.Where(x => x.firstName.Contains(fname??x.firstName) || x.lastName.Contains(lname?? x.lastName)).ToList();

Equivalent SQL :

SELECT * FROM User u 
     WHERE u.FirstName = ISNULL(@fname,u.FirstName)
           OR u.LastName = ISNULL(@lname,u.LastName)

edited Apr 22 '14 at 06:46

answered Apr 22 '14 at 06:35

RollerCosta

5,020
9
53
71

what is f??x.firstName? – Saurabh Gadariya Apr 22 '14 at 06:39
1

is says if fname is null then user x.firstName. I have updated the answer with its equivalent SQL. – RollerCosta Apr 22 '14 at 06:44
So, how to store this search result? Should I make list and assign it with this query? – Saurabh Gadariya Apr 22 '14 at 06:47
1

That depends how long you want to access the result. above query will return the list of users with matched name. You can store the output by creating a list List users = AboveQuery; , session variable, ViewBag/ViewData or in cache . – RollerCosta Apr 22 '14 at 06:47
what is IQueryable<> ?? Is it useful here? – Saurabh Gadariya Apr 22 '14 at 06:54
1

Again that depends IQueryable support to lazy loading/deferred execution. Say if User Entity has Address as Navigation property then with IQueryable we can avoid address collection to get loaded while referring User Entity. – RollerCosta Apr 22 '14 at 07:00
Getting this error: Only arguments that can be evaluated on the client are supported for the String.Contains method. List query = db.M_Registarions.Where(x => x.M_Fname.Contains(fname??x.M_Fname) || x.M_Lname.Contains(lname??x.M_Lname)).ToList(); – Saurabh Gadariya Apr 22 '14 at 08:20
What are the values for fname and lname? it seems that you're passing something in the string that Contains is unable to evaluate – clifford.duke Apr 22 '14 at 09:36
@RollerCosta If I want to add if condition Where function then how to write? – Saurabh Gadariya Apr 22 '14 at 09:40
Nicely explained http://stackoverflow.com/questions/19791350/linq-cant-use-string-contains. To use && and || will work as if condition. Tell me the scenario. – RollerCosta Apr 22 '14 at 10:00

Advanced Search in LINQ Query

1 Answers1