Cast value for "System.Single" value not executed in C # linq

Question

I execute this query on my data:

newObj.TotalSizeDone = _ctx.TestPackages.Where( i => i.LineCheckState && i.TestState && i.Flushing && i.Drying && i.ReInstatement && i.CleaningState).Sum(i=>i.Size); 

But I get this error:

Casting to a value of type "System.Single" failed because the materialized value is null. Either the general parameter of the result type or the query should use a type with a null value.

My class:

 public int Id { set; get; } public string PackageNumber { set; get; } public float Size { set; get; } public string Descrption { set; get; } public DateTime SubmitDateTime { set; get; } public string TestPackageLocation { set; get; } public string TestPackageOrder { set; get; } public string LineCheckReportNumber { set; get; } public bool LineCheckState { set; get; } public DateTime? LineCheckSubmitDateTime { set; get; } public string CleanReportNumber { set; get; } public bool CleaningState { set; get; }//ndt test public DateTime? CleanSubmitDateTime { set; get; } public string TestReportNumber { set; get; } public bool TestState { set; get; } public DateTime? TestSubmitDateTime { set; get; } public string DryingReportNumber { set; get; } public bool Drying { set; get; } public DateTime? DryingSubmitDateTime { set; get; } public string FlushingReportNumber { set; get; } public bool Flushing { set; get; } public DateTime? FlushingSubmitDateTime { set; get; } public string ReInstatementReportNumber { set; get; } public DateTime? ReInstatementSubmitDateTime { set; get; } public bool ReInstatement { set; get; } 

Data:

 INSERT [dbo].[TestPackages] ([Id], [PackageNumber], [Size], [Descrption], [SubmitDateTime], [TestPackageLocation], [TestPackageOrder], [LineCheckReportNumber], [LineCheckState], [LineCheckSubmitDateTime], [CleanReportNumber], [CleaningState], [CleanSubmitDateTime], [TestReportNumber], [TestState], [TestSubmitDateTime], [DryingReportNumber], [Drying], [DryingSubmitDateTime], [FlushingReportNumber], [Flushing], [FlushingSubmitDateTime], [ReInstatementReportNumber], [ReInstatementSubmitDateTime], [ReInstatement]) VALUES (1, N'TestPackage-5185', 0, N'323', CAST(0x0000A658016AB9A6 AS DateTime), N'1220', N'1', N'256', 1, CAST(0x0000A66300000000 AS DateTime), N'15', 1, NULL, N'2626', 1, CAST(0x0000A66A00000000 AS DateTime), N'150', 1, CAST(0x0000A65800000000 AS DateTime), N'21', 1, CAST(0x0000A66300000000 AS DateTime), N'212', CAST(0x0000A66300000000 AS DateTime), 0) GO 

Answer 1

The problem is because you do not have data that matches the where logic (in this case, ReInstatement is false ), this means that zero results are returned.

When executing Sum without any results, it will want to use the result null , but the Sum return type will match your float type (therefore, it does not work).

To be able to Sum when no results exist, you can use the DefaultIfEmpty function as follows: this means that there will never be any null values ​​for Sum :

 var results = _ctx.TestPackages.Where( i => i.LineCheckState && i.TestState && i.Flushing && i.Drying && i.ReInstatement && i.CleaningState); // Sum the results, and in the event of a null value, assign 0f instead. newObj.TotalSizeDone = results.Select(i => i.Size) .DefaultIfEmpty(0f) .Sum(); 

Alternatively, you can use the following so that Sum knows to return a type with a null value (which can then be combined with ?? ):

 newObj.TotalSizeDone = results.Sum(i => (float?)i.Size) ?? 0f;