CODEWITHSUNDEEP
c#entity-frameworklinqt-sql
Jeremy
Jeremy

Reputation: 1

C# linq-sql checking for null string

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

Upvotes: 0

Views: 4009

Answers (1)

Aydin
Aydin

Reputation: 15294

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

Upvotes: 1

Related Questions