CODEWITHSUNDEEP
c#linq-to-sql
user3052918
user3052918

Reputation:

exception when converting to int32

When executing this code, I receive an error on the last line. How should this be rewritten to avoid the exception?

   var q = from i in dbconnect.tblMaterialTenderGroups
                join b in dbconnect.tblMaterials on i.materialId equals b.materialId
                join f in dbconnect.tblFactoryRequests on b.requestId equals f.requestId
                where i.MaterialGroupId == materialGroupId && f.propertyFactoryCenteralId.Contains(facName)
                select b;
        int ab= q.Count();

        int? sum = q.Sum(g => Convert.ToInt32(g.requestAmount));

The execption is

System.InvalidOperationException: The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.

g.requestAmount is of type nvarchar(100).

Upvotes: 0

Views: 163

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236218

Add casting to nullable integer:

 int? sum = q.Sum(g => (int?)Convert.ToInt32(g.requestAmount));

Interesing thing here is that exactly same query will be generated whether you add casting or not. Simplified it looks like:

SELECT SUM([t1].[value]) AS [value]
FROM (
    SELECT CONVERT(Int,[t0].[requestAmount]) AS [value], [t0].[Id]
    FROM [dbo].[People] AS [t0]
    ) AS [t1]
WHERE ...

It returns integer value when you have some rows to sum. But what if there is nothing to sum (i.e. all rows was filtered out)? Database query will return NULL in that case.

Here we should understand what Linq to SQL does with returned value. There is two different Sum extensions exist for IQueryable<T>:

int Sum<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, int>> selector)
Nullable<int> Sum<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, Nullable<int>>> selector)

First one tries to assign query results to integer value (and that gives you exception). Second one tries to assign query results to nullable integer. So, you simply need to call second Sum method, which is called when you cast selector result to nullable type.

Upvotes: 0

Dennis
Dennis

Reputation: 37770

Such exception happens, when EF tries to materialize query result. Some of resulting fields contains null, but the entity property (or anonymous type, when using projections with Select), which is mapped to that field, is int instead of int?.

If you'll look carefully at stack trace, you'll get an entity, which should declare int? property.

Upvotes: 1

Related Questions