What is the best way to search data on multiple fields

Question

I am working in ASP.NET MVC, I have a scenario where user can select multiple options to get list of doctors, this is how my action looks like.

public JsonResult DoctorsList(int? specialization , int? city, int? area, int? insurance_company, string doctor_name )

Any of these arguments can have some value and any number of them can be null also all of can be null in that I will return all the records. Now I know a long and complicated way where I can make different combinations of these arguments and check which one is null and which one is not and then write my query based on that. But is there any other shorter more efficient way? Right now I am using OR conditions to get records like this

            var doctors = db.Doctors.Where(e =>
        e.specialization == specialization ||
        e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).Area.city_id == city ||
        e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).area_id == area ||
        e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).ClinicInsuranceCompanies
        .Select(sin=>sin.company_id).ToList().Contains(insurance_company) ||
        e.first_name == doctor_name ||
        e.last_name == doctor_name
        )
        .Select(s => new
        {
            doctor_name = s.first_name + " " + s.last_name
        }).ToList();

But I want it to work in combinations, For Example Selecting Doctors with specialization_id = 1 and city_id=2 , of other combinations like this. But OR condition will be true if only one condition matches

Can you post the query from which you are selecting all the records? — imanshu15, Jan 22 '18 at 07:42

score 0 · Answer 1 · answered Jan 22 '18 at 08:03

For your scenario i think this approach might work rather than going around if else conditions. And i think && should be used to filter out exactly but you could use || operator if thats what you want,

var doctors = db.Doctors.Where(e =>
                (specialization != null && e.specialization == specialization) &&
                (city != null && e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).Area.city_id == city) &&
                (area != null && e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).area_id == area) &&
                (insurance_company != null && e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).ClinicInsuranceCompanies
                .Select(sin => sin.company_id).ToList().Contains(insurance_company)) &&
                (doctor_name != "" && e.first_name == doctor_name || e.last_name == doctor_name)
                )
                .Select(s => new
                {
                    doctor_name = s.first_name + " " + s.last_name
                }).ToList();

This makes the `!= null` part of the SQL query, which may significantly mess up the query plan and kill performance. It's better to add the predicates conditionally. — Gert Arnold, Jan 22 '18 at 08:53
oh i didnt know that, but woudnt it take much time to check conditionally and run different queries for each? — imanshu15, Jan 22 '18 at 08:57
Your Answer gave me a hint and I figured it out, I have posted a reply. I want something just like this , Todoo Todoo — Shair, Jan 22 '18 at 10:56

score 0 · Answer 2 · answered Jan 22 '18 at 10:58

0

This is what I was looking for @imanshu15 answer gave me a hint.

            var doctors = db.Doctors.Where(e =>
        (specialization != null && e.specialization == specialization) || (specialization == null)
        ).Where(e =>
        (city != null && e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).Area.city_id == city) || (city == null)
        ).Where(e =>
        (area != null && e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).area_id == area) || (area == null)
        ).Where(e =>
        (insurance_company != null && e.Clinics.FirstOrDefault(cs => cs.doctor_id == e.doctor_id).ClinicInsuranceCompanies
            .Select(sin => sin.company_id).ToList().Contains(insurance_company)) || (insurance_company == null)
        ).Where(e =>
        (doctor_name != null && e.first_name == doctor_name) || (doctor_name == null)
        )

answered Jan 22 '18 at 10:58

Shair

13
6

@Shahir why woudnt u run multiple Where clause when you can use && Operator? . And if the answer was helpfel please vote it – imanshu15 Jan 22 '18 at 11:23
As I said below the other answer: this is potentially harmful. You should look at the possible duplicate. – Gert Arnold Jan 22 '18 at 12:28
By the way, the predicates contain redundancies: `(specialization != null && e.specialization == specialization) || (specialization == null)` can be reduced to `(specialization == null || e.specialization == specialization)`. – Gert Arnold Jan 22 '18 at 12:33
@imanshu15 && Operators make the result set null if any one of them is false my scenario was that user can either select any search option or none .. and I voted but my rank on stackoverflow makes my votes not visible. – Shair Jan 24 '18 at 10:49
@GertArnold thanks buddy, I will change it to this (y) – Shair Jan 24 '18 at 10:50

What is the best way to search data on multiple fields

2 Answers2