locked
Linq query won't return records if one field is null RRS feed

  • Question

  • User863290762 posted

    Hi,

    Any idea what would cause this query not to return a record if the employer.UcNumber is null ?

    It completely skips the record if that field is null, if it has a empty string it returns it.

    I tried different forms of the query including 

    employer.FirstName.Contains(term) || employer.LastName.Contains(term) || employer.Ssn.Contains(term) || employer.Ein.Contains(term) || employer.UcNumber.Contains(term)

    With no luck.

    Below is the query;


    public IEnumerable<AutoCompleteItem> GetEmployerNamesForAllStates(string term)
    {
    var repository = this.SharedContextManager.EmployerRepository;

    var allCandidateEmployers = (from employer in repository.GetAll()
    where (employer.FirstName + " " + employer.LastName + " (SSN: " + employer.Ssn + " EIN: " + employer.Ein + " UC#: " + employer.UcNumber + ")").Contains(term)

    select new AutoCompleteItem
    {
    Id = employer.Id,
    Label = (employer.FirstName + " " + employer.LastName + " (SSN: " + employer.Ssn + " EIN: " + employer.Ein + " UC#: " + employer.UcNumber + ")"),
    Value = (employer.FirstName + " " + employer.LastName)
    });
    return allCandidateEmployers;
    }

    Wednesday, July 15, 2015 8:00 AM

Answers

  • User-271186128 posted

    Hi Dave,

    According to your description, I create a sample using the NothWind Database and try to use your code. It seems that I can get the resource, even though some of the field is null.

    You could refer to it:

                using (MyTestDBEntities context = new MyTestDBEntities())
                {
                    var query = from cc in context.Customers
                                where cc.ContactName.Contains("on") || cc.CompanyName.Contains("on")
                                select new
                                {
                                    CustomerID = cc.CustomerID,
                                    CompanyName = cc.CompanyName,
                                    ContactName = cc.ContactName
                                };
    //The following code also worked well. //var query2 = from cc in context.Customers // where (cc.ContactName + " " + cc.CompanyName).Contains("on") // select new // { // CustomerID = cc.CustomerID, // CompanyName = cc.CompanyName, // ContactName = cc.ContactName // }; GridView1.DataSource = query.ToList(); GridView1.DataBind(); }

    The output:

    I suggest you could set a break point to debug your code and make sure the repository contains values. And check the database whether the columns contain the term.

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 23, 2015 5:10 AM

All replies

  • User753101303 posted

    Hi,

    When you add a NULL column with other columns (such as what you are doing currenctly or when computing the Label property) the result is NULL so it can't work. Your first attempt shoudl work as the NULL column is tested separately (but still the Label won't be shown as the final result will be null). Are you sure what you saw is that no rows were returned rather than rows with an empty label?

    Do you make a difference between having a NULL value or an empty string in this column? If not I would suggest to use a non null column and to use an empty string when UcNumber is not known.

    Wednesday, July 15, 2015 10:15 AM
  • User863290762 posted

    Hi,

    As an example, could you write out the statement ?

    Thanks,

    Dave

    Thursday, July 16, 2015 8:00 AM
  • User-271186128 posted

    Hi Dave,

    According to your description, I create a sample using the NothWind Database and try to use your code. It seems that I can get the resource, even though some of the field is null.

    You could refer to it:

                using (MyTestDBEntities context = new MyTestDBEntities())
                {
                    var query = from cc in context.Customers
                                where cc.ContactName.Contains("on") || cc.CompanyName.Contains("on")
                                select new
                                {
                                    CustomerID = cc.CustomerID,
                                    CompanyName = cc.CompanyName,
                                    ContactName = cc.ContactName
                                };
    //The following code also worked well. //var query2 = from cc in context.Customers // where (cc.ContactName + " " + cc.CompanyName).Contains("on") // select new // { // CustomerID = cc.CustomerID, // CompanyName = cc.CompanyName, // ContactName = cc.ContactName // }; GridView1.DataSource = query.ToList(); GridView1.DataBind(); }

    The output:

    I suggest you could set a break point to debug your code and make sure the repository contains values. And check the database whether the columns contain the term.

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 23, 2015 5:10 AM