C# linq-sql checking for null string

Question

In my database table I have 54 rows, of those 53 are NULL in the Code column which is defined as a varchar(100). (I confirmed that those fields are null by performing queries directly on the database using my Microsoft SQL Management Studio) I have tried to use the following LINQ code to return all of the rows where the field is null:

public IQueryable<LocationHeader> LocationHeaders
{
    return from p in Context.LocationTypes
    where p.Code == null
    select new LocationHeader
    {
        ID = p.ID,
        Description = p.Description,
        Code = p.Code
    };
 }

I have also found that if Iremove the where clause and then call ToList() on my LocationHeaders property and then query against it, it returns only the 53 rows I expect.

var test = LocationHeaders.ToList().Where(x => x.Code == null);

I've also tried p.Code.Equals(null) and object.Equals(p.Code, null) as suggested in related questions on this website and elsewhere. It always return an empty collection. If I change it to p.Code != null it returns all 54 rows (53 of which have a null Code column, and 1 which does not) but I view those objects the Code property has been set to null.

I also tried to null coalesce my code property into an empty string that I could check later:

Code = p.Code ?? string.Empty

But that changed exactly nothing, when I viewed the items after the query was performed, the Code property of my objects was still set to null.

Does anyone know why this might be and what I can do to fix it? I am using EF 6 Code-First, if that matters.

EDIT: I've permanently changed my code to read this way:

public IQueryable<LocationHeader> LocationHeaders
{
    return from p in Context.LocationTypes
    where object.Equals(p.Code, null)
    select new LocationHeader
    {
        ID = p.ID,
        Description = p.Description,
        Code = p.Code
    };
 }

and I finally thought to check the query using SQL Server profiler. It's STILL writing the query like this:

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Description] AS [Description], 
[Extent1].[Code] AS [Code]
FROM [dbo].[LocationType] AS [Extent1]
WHERE [Extent1].[Code] = @p__linq__0',N'@p__linq__0 nvarchar(4000)',@p__linq__0=NULL

Possible duplicate of Handling null values in where clause using LINQ-to-SQL — DGibbs
– DGibbs, Commented Feb 11, 2016 at 17:23
See henrylu.me/2014/07/05/null-value-handling-in-entity-framework — Steve Greene
– Steve Greene, Commented Feb 11, 2016 at 17:30
DGibbs, as I said in the question I tried using the object.Equals method, and it did not solve my problem so this cannot be a duplicate. — Jeremy
– Jeremy, Commented Feb 11, 2016 at 17:33
Steve Greene, the solution presented there suggests to check directly for null when the variable passed equals null, that's what I am doing since I don't have a variable and am only, always checking for null. — Jeremy
– Jeremy, Commented Feb 11, 2016 at 17:36
Did you try string.IsNullorEmpty()?? I have feeling your p.Code is not null but empty — Viru
– Viru, Commented Feb 11, 2016 at 17:44

Aydin · Accepted Answer · 2016-02-11 18:50:06Z

1

Update your entity models to not allow NULL values in the first place... you will have to set all the fields which are currently NULL to an empty string prior to attempting this, (you can set them through SSMS)...

After you've set all the null values to an empty string.
Add this attribute to the Code property.

[Required(AllowEmptyStrings = true)]
public string Code { get; set; }

And migrate those changes over.

From here on you can just do Foo.Code == string.Empty

answered Feb 11, 2016 at 18:50

Aydin

15.4k4 gold badges35 silver badges42 bronze badges

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

3 Comments

ps_prakash02 Over a year ago

Hi, I'm facing similar issue.. we are using Database First approach and EF 6.1.3.. The query we are trying is employees.where(a => a.employeename == parametervalue || a.employeename == null).ToList().. This does not return records which has null value in employeename column.. can you please help me on this..

Aydin Over a year ago

@ps_prakash02 does it return values when you try a comparison check with string.empty instead of null

ps_prakash02 Over a year ago

when I compare with "" then it returns record which has empty value in the employeename column.. but I want get records which has null in employeename column..

Collectives™ on Stack Overflow

C# linq-sql checking for null string

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related