Ignoring null parameter in where clause linq to sql

Question

I need to ignore null parameters in the where clause so that i can fetch the appropriate data with the applied filters from the user side. Inorder to acheive this, I am currently using the if..else nested approach which grows in size as the number of parameters grow. I would like to know if there is any other effecient way of handling this scenario by avoiding the number of lines and complexity and improving readablility

public List<Members> GetMembers(int currentPosition, string memberStatus,
                string package, string packageStatus, string branch)
{
    var members = new List<Members>();
    if (package != null)
    {
        //include package
        if (packageStatus != null)
        {
            //include package, packageStatus
            if (branch != null)
            {
                //include package,packageStatus,branch
                members = db.Members.Where(x => x.PackageName == package && x.PackageStatus == packageStatus && x.Branch == branch).ToList();
            }
            else
            {
                //include package,packageStatus
                members = db.Members.Where(x => x.PackageName == package && x.PackageStatus == packageStatus).ToList();
            }
        }
        else
        {
            if (branch != null)
            {
                //include package,branch
                members = db.Members.Where(x => x.PackageName == package && x.Branch == branch).ToList();
            }
            else
            {
                //include package
                members = db.Members.Where(x => x.PackageName == package).ToList();
            }
        }
    }
    else
    {
        if (packageStatus != null)
        {
            //include packageStatus
            if (branch != null)
            {
                //include packageStatus,branch
                members = db.Members.Where(x => x.PackageStatus == packageStatus && x.Branch == branch).ToList();
            }
            else
            {
                //include packageStatus
                members = db.Members.Where(x => x.PackageStatus == packageStatus).ToList();
            }
        }
        else
        {
            if (branch != null)
            {
                //include packageStatus,branch
                members = db.Members.Where(x => x.PackageStatus == packageStatus && x.Branch == branch).ToList();
            }
            else
            {
                //include nothing
                members = db.Members.ToList();
            }
        }
    }

    return members;
}

It is very strange that you perform `x => x.PackageName == package` when the `package` is null. — Roman Ryzhiy, Sep 10 '20 at 06:38
@RomanRyzhiy See thats the problem. Getting confused with those params. sorry will edit the code now — Thameem, Sep 10 '20 at 06:42

score 1 · Accepted Answer · answered Sep 10 '20 at 06:49

You can add those conditions to the query. It won't make for the most readable SQL, but assuming you find readable code more important and trust SQL Server's optimizer:

members = db.Members.Where(x =>
    (package == null || x.PackageName == package) &&
    (packageStatus == null || x.PackageStatus == packageStatus) &&
    (branch == null || x.Branch == branch)
).ToList();

Alternatively, you could conditionally append Where()s to a variable of type IQueryable<Member>. See for example entity framework: conditional filter.

Roman Ryzhiy · Answer 2 · 2020-09-10T06:51:58.127

1

I would like to suggest a readable version of the method:

public List<Members> GetMembers(int currentPosition, string memberStatus,
    string package, string packageStatus, string branch)
{
    var members = new List<Members>();

    members = db.Members.ToList();

    if (package != null)
    {
        members = members.Where(x => x.PackageName == package);
    }

    if (packageStatus != null)
    {
        members = members.Where(x => x.PackageStatus == packageStatus);
    }

    if (branch != null)
    {
        members = members.Where(x => x.Branch == branch);
    }

    return members.ToList();
}

edited Sep 10 '20 at 06:51

answered Sep 10 '20 at 06:50

Roman Ryzhiy

1,540
8
5

This won't compile, Where doesn't return a List. – CodeCaster Sep 10 '20 at 06:51
I meant your `members = members.Where()`. The `members` variable is of type `List`, you can't assign an `IQueryable` to that. – CodeCaster Sep 10 '20 at 06:56
Ive tried this one. But the problem is since i am using pagination and i return 10 rows each time. I need to select only 10 rows from db and after filtering them the rows will be more decreased. so i cannot select all members from DB. But if take 10 and filter i will be returning less than 10 members. Anyhow thanks for the answer – Thameem Sep 10 '20 at 06:57
@Thameem then add `.Take(10)` like here https://stackoverflow.com/questions/2656576/select-top-5-in-entity-framework/2656612 – Roman Ryzhiy Sep 10 '20 at 06:59

score 1 · Answer 3 · answered Sep 10 '20 at 06:52

I think this works (haven't tried compiling it but it makes sense in my head)

    public List<Members> GetMembers(int currentPosition, string memberStatus,
                string package, string packageStatus, string branch)
    {
        List<Members> ret = db.Members()
        if(memberStatus != null || package != null || packageStatus != null || branch != null)
        {
            ret = db.Members.Where(x => 
                (memberStatus == null) ? (true) : (memberStatus == x.MemberStatus)
                && (package == null) ? (true) : (package == x.PackageName)
                && (packageStatus == null) ? (true) : (packageStatus == x.PackageStatus)
                && (branch == null) ? (true) : (branch == x.Branch)
            ).ToList();
        }
        return ret;
    }

Ignoring null parameter in where clause linq to sql

3 Answers3