Querying Entity Framework. Part 24 – Aggregating Nullable Fields

It’s a very simple case for Entity Framework model. I have a nullable column in Clients table.

I want to count values and to sum up them. The LINQ query to EF model can be very simple.

But these queries reveals some magic. Count() returns int type, but Sum() return int?. So you may fall into one of the following cases.

Method Return type Result on all rows Result on not-nullable rows Result on empty dataset
Count() int Count all rows regardless null or not null Count only not-nullable rows Zero
Sum() int? Sum only not-nullable values Sum only not-nullable values Null

The most dangerous is the last case when Sum() returns null, so  you should add a check for null value. If you want to get zero, you need to change a request and add DefaultIfEmpty() method.

Generating Date Sequence With SQL Query

The task is to create SQL query that will produce a date sequence starting from @startDate till @endDate including, i.e. 2017-01-01, 2017-01-02, 2017-01-03 and so on.

There is a couple of obvious solutions.

1. DATEADD() with integer sequence where the integer sequence is an increment

Please take care how many records are in sys.all_objects, it won’t generate a long sequence. If you need more, use a self cross join as shown in Generating Integer Sequence With SQL Query.

2. Recursive query

The last option is very important because SQL Server limits the recursion level to 100 by default.

Casting C# Enums

It’s a short memo how to cast enum to int or string and vice versa.

1. Enum -> int
int value = (int)myEnum;

2. Enum -> string
string name = myEnum.ToString();

3. Int -> Enum
myEnum = (MyEnum)2;

4. String -> Enum
myEnum = (MyEnum) Enum.Parse(typeof(MyEnum), stateName);
bool parseSuccess = Enum.TryParse(stateName, out myEnum);

Querying Entity Framework. Part 23 – Filtering Nullable Field

When you run queries against non-nullable columns (NOT NULL in SQL), everything is going smoothly. You should pay some attention when you deal with nullable fields (one of the cases will be covered at the end of this post).

Now let’s look closer to Entity Framework queries against nullable field. I’ve added a new column LastUpdate with the type DateTime? to my Client entity.

So I have some records with concrete dates, and some with NULL values.

1. Filtering “greater or equal than”

C# understands it perfectly. We have a simple and correct SQL query.

2. Filtering all concrete dates aka WHERE … IS NOT NULL
You can use one of the choices.

Both generate the same correct SQL query.

3. Filtering multiple dates aka WHERE … IN

This case is a bit harder because C# could not compare DateTime[] array and DateTime? field. You need to get a not-nullable value from column.

Looks good, but I’m not satisfied with conversion of C# DateTime array values to SQL Server DateTime2 type. Especially when I set a strong annotation to use Date type in database.

If you don’t want to build a query with every parameter despite whether it is null or not, you might build a dynamic filter like in the following sample.

What will be if both parameters are null? No filters will be added, and you’ll get a query returning all(!) rows regardless of having a concrete value or not. In this case add another check when both parameters are null. One of the simplest implementation is to add third if statement.