locked
Searching a EF customer model with null field values doesn't return data RRS feed

  • Question

  • I'm trying to create an Advanced Search on a single table, Customer. I'm using EF and VS Community 2015, and SQL Server 2014 DB. I have a page that user can submit back Company, FirstName values. Only if these paramaters have value, do I want to query the model property.

    The simplest problem is when I use .Contains() on a field which has Null values in db it doesn't give expected records.

    For example list.FirstName.Contains("Frank") will not return the 9 records in the db that I know contain first name of Frank because the [FirstName] field in the db contains 1505 null values. I've tested this by setting all null values to empty strings, and it works. But I can't enforce no nulls in db as a solution because it will break the application in a billion places because it submits null values to DB sometimes.

    public ActionResult SearchResults(string firstName, string company)
    {
    private VNNContext db = new VNNContext();

    IEnumerable<customer>list = db.Customers.ToList();

    if (!string.IsNullOrWhiteSpace(firstName))
    {
    List = list.Where(x => x.FirstName.Contains(firstName));
    }

    if (!string.IsNullOrWhiteSpace(company))
    {
    List = list.Where(x => x.Company.Contains(company));
    }

    return View(list);
    }

    I can't set all values in database not nullable. Its too big, and I figure there has to be a way to handle nulls here in the code.
    Thanks for the help!</customer>
    Wednesday, June 8, 2016 6:15 PM

Answers

All replies

    • Marked as answer by HTHP Wednesday, June 8, 2016 8:56 PM
    Wednesday, June 8, 2016 6:27 PM
  • >IEnumerable<customer>list = db.Customers.ToList();

    You are not searching the database.  You are fetching all the rows into memory and then searching them.

    And then

    >list.Where(x => x.FirstName.Contains(firstName));

    Will throw a NullReferenceException if you attempt to call the .Contains() method on a null string.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, June 8, 2016 7:52 PM
  • Thanks! Found the solution in forum you pointed out DA924x.

    Thank you for pointing that out davidbaxterbrowne. I kinda figured as much, but being new to programming in this technology I'm still not very articulate.

    list = list.Where(x => x.Company != null && x.Company.ToLower().Contains(company));


    • Edited by HTHP Wednesday, June 8, 2016 8:57 PM
    Wednesday, June 8, 2016 8:56 PM