none
Problem in querying records with null values column(s) RRS feed

  • Question

  • Hi,

    If all the values for column enteredBy are not null in the data source table  then the following statement works fine. But if I set any of the enteredBy column value to null it generates an error "Object reference not set to an instance of an object".

    context.EntityName.Where(e => e.enteredBy.ToLower().Contains("somevalue"));

    It's obvious that while executing .ToLowwer and .Contains function when program execution finds null value, it generates the error. But what is the workaround then?

    Thanks.


    Paradise lies at the feet of thy mother. - Prophet Mohammed (PBUH) (skype: ali.net.pk)




    • Edited by Khadim Ali Wednesday, April 25, 2012 7:41 AM
    • Changed type Khadim Ali Wednesday, April 25, 2012 8:57 AM
    Wednesday, April 25, 2012 7:38 AM

Answers

  • Hi Ali.NET,

    Are you using Entity Framework or LINQ to SQL? What would appear to be happening is that the predicate is being evaluated on the entities once they've been retrieved from the database, which could be bad performance-wise. I'd have a look at getting the predicate to be evaluated at the database instead. To answer your question the way to avoid that exception would be simply adding a condition ruling out null values first:

    context.EntityName.Where(e => e.enteredBy != null && e.enteredBy.ToLower().Contains("somevalue"));

    Regards,

    Tyler

    • Marked as answer by Khadim Ali Thursday, April 26, 2012 1:31 PM
    Wednesday, April 25, 2012 11:24 AM
  •  It may be something like that, but there are plenty of articles on Bing
    and Google about Linq queries against a null value in the query.
     
    context.EntityName.Where(e =>    e.enteredBy != null &&
    e.enteredBy.ToLower().Contains("somevalue"));
     
    • Marked as answer by Khadim Ali Thursday, April 26, 2012 1:31 PM
    Wednesday, April 25, 2012 12:43 PM

All replies

  • Hi Ali.NET,

    Are you using Entity Framework or LINQ to SQL? What would appear to be happening is that the predicate is being evaluated on the entities once they've been retrieved from the database, which could be bad performance-wise. I'd have a look at getting the predicate to be evaluated at the database instead. To answer your question the way to avoid that exception would be simply adding a condition ruling out null values first:

    context.EntityName.Where(e => e.enteredBy != null && e.enteredBy.ToLower().Contains("somevalue"));

    Regards,

    Tyler

    • Marked as answer by Khadim Ali Thursday, April 26, 2012 1:31 PM
    Wednesday, April 25, 2012 11:24 AM
  •  It may be something like that, but there are plenty of articles on Bing
    and Google about Linq queries against a null value in the query.
     
    context.EntityName.Where(e =>    e.enteredBy != null &&
    e.enteredBy.ToLower().Contains("somevalue"));
     
    • Marked as answer by Khadim Ali Thursday, April 26, 2012 1:31 PM
    Wednesday, April 25, 2012 12:43 PM
  • Hi Ali .NET;

    The query sent to the SQL server should be something like this:

    SELECT 
    [Extent1].[enteredBy] AS [enteredBy]
    FROM [dbo].[EntityName] AS [Extent1]
    WHERE LOWER([Extent1].[enteredBy]) LIKE N'%somevalue%'

    Note that Linq to Entities will translate your query's ToLower to SQL LOWER function and your Contains to SQL LIKE and should not have an issue with a field that has a null value in it. Seeming that the query does not get executed until you enumerate over the results can you post that code.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, April 25, 2012 1:30 PM