How to differentiate between SQL Null ints in c# linq

Question

In MVC we can declare nullable ints in our database context models as

public int? SomeField { get; set; }

But how do you check if a record for SomeField is Null in Linq. For example if I want to get a list of rows with value not zero I use the following Linq statement.

var k = db.TableSet.Where( u => u.Somefield != 0 )

Is there a Linq equivalent to include/exclude either/both Zeros and Null?

Edit: I am currently testing this but I am sure it will either be Null exception or Null fields return as zero.

Are you talking about null in SQL - the DBNull - or are you talking about a .NET null? — Kirk Broadhurst
– Kirk Broadhurst, Commented Aug 27, 2013 at 21:34
@KirkBroadhurst I need to check for DBNulls and then translate that into my class accordingly to prevent Null exceptions. — Flood Gravemind
– Flood Gravemind, Commented Aug 27, 2013 at 21:36

Oliver · Accepted Answer · 2013-08-27 21:31:51Z

3

Personally I would prefer to filter out the null values in my data layer to prevent this kind of null checking in code.

BUT, you could use:

var k = db.TableSet.Where(u => u.Somefield.HasValue && u.SomeField != 0)

//Or

var k = db.TableSet.Where(u => u.Somefield != null && u.SomeField != 0)

//Or

var k = db.TableSet.Where(u => u.Somefield.GetValueOrDefault() != 0)

//Or

var k = db.TableSet.Where(u => (u.Somefield ?? 0) != 0)

edited Aug 27, 2013 at 21:31

answered Aug 27, 2013 at 21:25

Oliver

9,0322 gold badges45 silver badges63 bronze badges

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

Comments

D Stanley · Accepted Answer · 2013-08-27 21:23:59Z

2

Sure:

var k = db.TableSet.Where( u => u.SomeField != null && u.Somefield != 0 )

or for a nullable type:

var k = db.TableSet.Where( u => u.SomeField.HasValue && u.Somefield != 0 )

answered Aug 27, 2013 at 21:23

D Stanley

153k12 gold badges190 silver badges257 bronze badges

Comments

p.s.w.g · Accepted Answer · 2013-08-27 21:23:48Z

1

You can do this:

var k = db.TableSet.Where( u => u.Somefield.HasValue && u.Somefield.Value != 0 )

Or this:

var k = db.TableSet.Where( u => u.Somefield != null && u.Somefield != 0 )

Or possibly this:

var k = db.TableSet.Where( u => (u.Somefield ?? 0) != 0 )

answered Aug 27, 2013 at 21:23

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

2 Comments

SLaks Over a year ago

LINQ providers may choke on the second option.

Oliver Over a year ago

Unbelievable, almost the same answer!

Gabe · Accepted Answer · 2013-08-27 21:29:34Z

1

Yes; just compare to null.

If you want to exclude both, you'll need an and clause.

edited Aug 27, 2013 at 21:29

Gabe

87.1k13 gold badges144 silver badges239 bronze badges

answered Aug 27, 2013 at 21:22

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

2 Comments

Flood Gravemind Over a year ago

The problem with this Int types are not nullable in C#

Jeff Mercado Over a year ago

@Flood: Your model should closely match your DB model. If it's possible that a column can be null, you need to make sure your model's corresponding property can be null as well.

Collectives™ on Stack Overflow

How to differentiate between SQL Null ints in c# linq

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related