CODEWITHSUNDEEP
c#linq
Andrew Kilburn
Andrew Kilburn

Reputation: 2251

The cast to value type 'System.Int32' failed because the materialized value is null.

I'm trying to return a list of results, however. Whenever there are no results, I receive the error message which I have posted above. However, it's strange because whenever I add the variable q instead of the return, it just returns no results instead and is fine with this. I would prefer to do it the way which I am currently doing it right now, does anybody know what is wrong with the query? Whenever I run it in LINQPad it works completely fine. 

  public IQueryable<ClaimNumberReport> GetClaimsByClaimNumber(int ClientID, int ClaimID) {
                /*var q = */ return (from d in camOnlineDb.Details
                        join a in camOnlineDb.Areas
                            on new { a = d.ClientID, b = d.AreaID ?? 0 }
                        equals new { a = a.ClientID, b = a.AreaID }
                        where d.ClientID == ClientID

                        join r in camOnlineDb.Reasons
                            on new { a = d.ClientID, b = d.ReasonID ?? 0 }
                        equals new { a = r.ClientID, b = r.ReasonID }

                        join sd in camOnlineDb.SuppDepts
                          on new { a = d.ClientID, b = d.CategoryID ?? 0 }
                      equals new { a = sd.ClientID, b = sd.CategoryID } into sdd
                      from sd in sdd.DefaultIfEmpty()

                        join h in camOnlineDb.Headers
                            on new { d.ClientID, d.ClaimID}
                        equals new { h.ClientID, h.ClaimID }
                        where h.ClaimID == ClaimID

                        join su in camOnlineDb.Suppliers
                            on new { h.ClientID, h.SupplierID }
                        equals new {su.ClientID, su.SupplierID }

                        join cp in camOnlineDb.ClaimPacks
                            on new { h.ClientID, h.ClaimID }
                        equals new { cp.ClientID, cp.ClaimID }

                        join rev in camOnlineDb.Reviews
                            on new { h.ClientID, h.ReviewID }
                        equals new { rev.ClientID, rev.ReviewID }

                        join revp in camOnlineDb.ReviewPeriods
                            on new { a = rev.ClientID, b = rev.ReviewPeriodID ?? 0 }
                        equals new { a = revp.ClientID, b = revp.ReviewPeriodID }

                        join st in camOnlineDb.Statuses
                            on new { a = d.ClientID, b = d.StatusID ?? 0 }
                        equals new { a = st.ClientID, b = st.StatusID }

                        join stcm in camOnlineDb.StatusCategoryMappings
                            on new { st.ClientID, st.StatusID }
                        equals new { stcm.ClientID, stcm.StatusID }

                        join stc in camOnlineDb.StatusCategories
                            on new { stcm.StatusCategoryID }
                        equals new { stc.StatusCategoryID }
                        where stc.StatusCategoryTypeID == 1

                        select new ClaimNumberReport {
                            TypeID = d.ClaimTypeID,
                            CPAttached = cp.FileName,
                            ReviewPeriodName = revp.ReviewPeriodName,
                            ClaimID = d.ClaimID,
                            Line = d.ClaimLine,
                            AccountNo = su.AccountNo,
                            SupplierName = su.SupplierName,
                            Amount = d.Amount,
                            Status = st.StatusDesc,
                            DateSent = d.DateSent,
                            DayOS = d.DaysOS,
                            NominalPeriod = d.NominalPeriod,
                            SLInvoiceNo = d.SLInvoiceNo,
                            Area = a.AreaDesc,                   
                            DebitRef = d.DebitFile,
                            DebitDate = d.JournalDate,
                            DeductDate = d.DeductDate,
                            StatusCategoryID = stc.StatusCategoryID,
                            StatusCategoryDesc = stc.StatusCategoryDesc,
                            APLReason = r.ReasonDesc,
                            ClientID = d.ClientID,
                            DeptNo = sd.DepartmentID,
                            DeptName = sd.DepartmentName,
                            Agreed = d.Agreed
                        });
                /*return q;*/
            }

Upvotes: 1

Views: 823

Answers (1)

usr
usr

Reputation: 171178

This error is caused by a situation where the query result type has a column/property of non-nullable type but the generated query results in a NULL value.

This could be considered a bug or not. It is hard to see what the L2S team should have done differently here. I think they should have added a better error message. This bug is insidious because it sometimes only strikes in production under unusual data...

Your left join (sd) seem not to match and one of the sd.* properties that you select must be an int. Solve that like this:

 DeptNo = (int?)sd.DepartmentID, //Cast to nullable

d.CategoryID ?? 0

What are you doing here? This seems to be a way to make the join compile. It's better to use:

                    join r in camOnlineDb.Reasons
                        on new { a = d.ClientID, b = (int?)d.ReasonID }
                    equals new { a = r.ClientID, b = (int?)r.ReasonID }

This cast makes the anonymous type signatures compatible. The generated SQL should now be faster. If you say x ?? 0 that converts to COALESCE(x, 0) which can prevent index use and such.

Upvotes: 4

Related Questions