Menu
An exception occurs when the following IQueryable is enumerated:
from record in dataContext.SomeTable
select Convert.ToDecimal(record.nullableDecimalColumn);
The error is an InvalidOperationException:
The null value cannot be assigned to a member with type decimal which is a non-nullable value type
The documentation for Convert.ToDecimal says that it converts null to 0, so it looks like it should work correctly.
270
As you know, a value type cannot be assigned a null value. For example, int i = null will give you a compile time error. C# 2.0 introduced nullable types that allow you to assign null to value type variables.
Nullable variables may either contain a valid value or they may not — in the latter case they are considered to be nil . Non-nullable variables must always contain a value and cannot be nil . In Oxygene (as in C# and Java), the default nullability of a variable is determined by its type.
In C#, you can assign the null value to any reference variable. The null value simply means that the variable does not refer to an object in memory. You can use it like this: Circle c = new Circle(42); Circle copy = null; // Initialized ... if (copy == null) { copy = c; // copy and c refer to the same object ... }
C# provides a special data types, the nullable types, to which you can assign normal range of values as well as null values. For example, you can store any value from -2,147,483,648 to 2,147,483,647 or null in a Nullable<Int32> variable. Similarly, you can assign true, false, or null in a Nullable<bool> variable.
If you wrote Query like this
tot_vat_amt = _pd.Where(x => x.VAT == 4m).Sum(x =>x.amount)
Replace with
tot_vat_amt = _pd.Where(x => x.VAT == 4m).Sum(x =>(double?)x.amount)
Hope this will help you
83
LINQ to SQL wasn't translating the ToDecimal method equivalently; it wasn't tolerating NULL. I fixed this by using GetValueOrDefault instead of Convert.ToDecimal:
from record in dataContext.SomeTable
select record.nullableDecimalColumn.GetValueOrDefault();
This caused the correct SQL to be produced, so the exception no longer occurred.
Here is my understanding of why this happens. LINQ to SQL and Entity Framework provide face-value semantic translation rather than literal translation. In this example, Convert.ToX is translated directly to CONVERT or CAST in SQL. This is the SQL equivalent translation, but not literal because it does not include the fine details of how .NET Convert.ToX operates. Specifically, the .NET one converts nulls to default values.
This is a symptom of the fact that the code didn't express what its author (who wasn't me) was really trying to do, which was convert nulls to default values rather than convert between data types.
In my opinion, this is also a symptom of IQueryable being a leaky and unreliable abstraction. I personally think it would be better not to use it in the first place and instead use something that's designed better such as a normal/fluent API. I think IQueryable completely violates the interface segregation principle because it allows an unlimited range of queries to be used despite only a limited subset being supported.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With