The cast to value type 'System.Single' failed 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 :

The cast to value type 'System.Single' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

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; }

The 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 caused because you do not have any data that matches the where clause logic (in this case ReInstatement is false), this means that zero results are returned.

When performing a Sum on no results, it will want to use a null result but the return type of Sum will be expecting to match your float type (so it fails).

In order to be able to Sum when no results exist, you can make use of the DefaultIfEmpty function as follows, which means there will never be any null values for Sum to worry about:

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 will know to return a nullable type (which can then be coalesced with ??):

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