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
– Roman Ryzhiy, Commented Sep 10, 2020 at 6:38
@RomanRyzhiy See thats the problem. Getting confused with those params. sorry will edit the code now — Thameem
– Thameem, Commented Sep 10, 2020 at 6:42

CodeCaster · Accepted Answer · 2020-09-10 06:49:02Z

2

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.

answered Sep 10, 2020 at 6:49

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roman Ryzhiy · Accepted Answer · 2020-09-10 06:51:58Z

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, 2020 at 6:51

answered Sep 10, 2020 at 6:50

Roman Ryzhiy

1,6669 silver badges5 bronze badges

4 Comments

CodeCaster Over a year ago

This won't compile, Where doesn't return a List.

CodeCaster Over a year ago

I meant your members = members.Where(). The members variable is of type List<Member>, you can't assign an IQueryable<Member> to that.

Thameem Over a year ago

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

Roman Ryzhiy Over a year ago

@Thameem then add .Take(10) like here stackoverflow.com/questions/2656576/…

justjoshin · Accepted Answer · 2020-09-10 06:52:32Z

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;
    }

Collectives™ on Stack Overflow

Ignoring null parameter in where clause linq to sql

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related