Check for Null in Linq where clause [duplicate]

Question

Hi I am working on some old code of ASP.Net MVC controller. There's a Controller Method with 1500 lines of code in it. When I inspected I got to know that there's more than 50+ LINQ queries made to fetch from database according to whether a particular field is null or not.

Just to put it into perspective, here's how this particular method looks like -

public ActionResult MyProductList(string Msg, string ProductName, string CompanyName, string DivisionName, string Type, string Form, string Packing, int page = 1, int pageSize = 20){
if (CompanyName == null)
            {
                CompanyName = "";

            }
            if (ProductName == null)
            {
                ProductName = "";

            }
            if (DivisionName == null)
            {
                DivisionName = "";

            }
            if (Type == null)
            {
                Type = "";

            }
            if (Form == null)
            {
                Form = "";

            }
            if (Packing == null)
            {
                Packing = "";

            }
    if (CompanyName != "")
            {
                var query1 = (from mypdtlist in db.MyProductlist
                              join pdt in db.Product on mypdtlist.Product_ID equals pdt.ID
                              where mypdtlist.User_ID.Equals(UserID)
                              select new ViewModal
                              {
                                  ProductName = pdt.ProductName,
                                  CompanyName = pdt.CompanyName,
                                  DivisionName = pdt.DivisionName,
                                  Type = pdt.Type,
                                  Form = pdt.Form,
                                  Packing = pdt.Packing,
                                  MRP = pdt.MRP,
                                  DrugName = pdt.DrugName,
                                  ID = mypdtlist.ID
                              }).Where(x => x.CompanyName.Contains(CompanyName.ToUpper())).Take(200).ToList();
                ViewBag.CompanyName = CompanyName;
                // list = query.Take(100).Where(x => x.CompanyName.Contains(CompanyName.ToUpper())).ToList();
                PagedList<ViewModal> model1 = new PagedList<ViewModal>(query1, page, pageSize);
                CookieManager.AddToViewBag(HttpContext, ViewBag);
                return View(model1);
            }
            if (ProductName != "")
            {
                ViewBag.ProductName = ProductName;
                var query2 = (from mypdtlist in db.MyProductlist
                              join pdt in db.Product on mypdtlist.Product_ID equals pdt.ID
                              where mypdtlist.User_ID.Equals(UserID)
                              select new ViewModal
                              {
                                  ProductName = pdt.ProductName,
                                  CompanyName = pdt.CompanyName,
                                  DivisionName = pdt.DivisionName,
                                  Type = pdt.Type,
                                  Form = pdt.Form,
                                  Packing = pdt.Packing,
                                  MRP = pdt.MRP,
                                  DrugName = pdt.DrugName,
                                  ID = mypdtlist.ID
                              }).Where(x => x.ProductName.Contains(ProductName.ToUpper())).Take(200).ToList();
                //list = query.Take(100).Where(x => x.ProductName.Contains(ProductName.ToUpper())).ToList();
                PagedList<ViewModal> model1 = new PagedList<ViewModal>(query2, page, pageSize);
                CookieManager.AddToViewBag(HttpContext, ViewBag);
                return View(model1);
            }
    //.... After a lot more similar condition checks and LINQ queries
    else if (ProductName != "" && CompanyName != "" && DivisionName != "" && Type != "" && Form != "" && Packing != "")
            {
                ViewBag.ProductName = ProductName;
                ViewBag.CompanyName = CompanyName;
                ViewBag.DivisionName = DivisionName;
                ViewBag.Type = Type;
                ViewBag.Form = Form;
                ViewBag.Packing = Packing;
                var query51 = (from mypdtlist in db.MyProductlist
                               join pdt in db.Product on mypdtlist.Product_ID equals pdt.ID
                               where mypdtlist.User_ID.Equals(UserID)
                               select new ViewModal
                               {
                                   ProductName = pdt.ProductName,
                                   CompanyName = pdt.CompanyName,
                                   DivisionName = pdt.DivisionName,
                                   Type = pdt.Type,
                                   Form = pdt.Form,
                                   Packing = pdt.Packing,
                                   MRP = pdt.MRP,
                                   DrugName = pdt.DrugName,
                                   ID = mypdtlist.ID
                               }).Where(x => x.ProductName.Contains(ProductName.ToUpper()) && x.CompanyName.Contains(CompanyName.ToUpper()) && x.DivisionName.Contains(DivisionName.ToUpper()) && x.Type.Contains(Type.ToUpper()) && x.Form.Contains(Form.ToUpper()) && x.Packing.Contains(Packing.ToUpper())).Take(300).ToList();
                PagedList<ViewModal> model = new PagedList<ViewModal>(query51, page, pageSize);
                // List<Product> pdt = db.Product.Where(x => x.ProductName.Contains(ProductName.ToUpper()) && x.CompanyName.Contains(CompanyName.ToUpper()) && x.DivisionName.Contains(DivisionName.ToUpper()) && x.Type.Contains(Type.ToUpper()) && x.Form.Contains(Form.ToUpper()) && x.Packing.Contains(Packing.ToUpper())).Take(300).ToList();
                // PagedList<Product> model = new PagedList<Product>(pdt, page, pageSize);
                //var list = db.Product.Take(20).ToList();

                return View(model);
            }
}

My apologies for the bad coding, but I didn't do the code. The code is basically using the same method for filtering the products.
All the parameters to the method are actually optional, thus they all can be null. So the code is basically comparing if the particular parameter is null or not. If it's null, then do not include it into the Where clause.

Now instead of using 50+ conditions for various combinations of the not null parameters, I'd like to convert this code into a single LINQ call.

So my question is, how can I check whether a particular parameter is null in the Where clause and if it's null, then eliminate its condition from the Where clause? What would be the most optimised and least lines of code to do that?

What did I try?
I tried to add the null check in the Where clause itself and tried to do something like this -

Where((ProductName!=null && x.ProductName.Contains(ProductName.ToUpper())) && (CompanyName!=null && x.CompanyName.Contains(CompanyName.ToUpper()))

But I don't think it's a right condition and it's not giving me any result at all.

Answer 1

You could add where conditions to a query after the initial declaration, just don't call ToList() before.

So first part would be:

var query1 = (from mypdtlist in db.MyProductlist
                          join pdt in db.Product on mypdtlist.Product_ID equals pdt.ID
                          where mypdtlist.User_ID.Equals(UserID)
                          select new ViewModal
                          {
                              ProductName = pdt.ProductName,
                              CompanyName = pdt.CompanyName,
                              DivisionName = pdt.DivisionName,
                              Type = pdt.Type,
                              Form = pdt.Form,
                              Packing = pdt.Packing,
                              MRP = pdt.MRP,
                              DrugName = pdt.DrugName,
                              ID = mypdtlist.ID
                          })

Now you can add further conditions like this:

if (!string.IsNullOrWhiteSpace(ProductName))
    query1 = query1.Where(x => x.ProductName.Contains(ProductName.ToUpper()));

And when all conditions are done, just return with ToList():

return query1.ToList();