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

Question

I'm getting the following error when executing a LINQ query on my database:

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

I believe it's because one of the columns is returning a null. Here is my LINQ command:

var facts = (from b in Program.db.ProductBrands
             join bm in Program.db.BrandManufacturers on b.ProductBrandID equals bm.ProductBrandID
             join c in Program.db.Companies on bm.ProductManufacturerID equals c.CompanyID
             join s in Program.db.AnimalSources on b.AnimalCode equals s.AnimalCode
             join ba in Program.db.BrandAccreditations on b.ProductBrandID equals ba.ProductBrandID into bax
             from credJoins in bax.DefaultIfEmpty()
             join a in Program.db.Accreditations on credJoins.AccreditationID equals a.AccreditationID into ax
             from accreds in ax.DefaultIfEmpty()
             join bt in Program.db.BrandTypes on b.ProductBrandID equals bt.BrandTypeID into btx
             from brandJoins in btx.DefaultIfEmpty()
             join t in Program.db.ProductTypes on brandJoins.ProductTypeID equals t.ProductTypeID into tx
             from types in tx.DefaultIfEmpty()
             select new { c.CompanyID, types.ProductTypeID, b.ProductBrandID, accreds.AccreditationID, s.AnimalName }).Distinct();

Which is my attempt to implement the following T-SQL query:

SELECT DISTINCT c.CompanyID, b.ProductBrandID, s.AnimalName, a.AccreditationID, t.ProductTypeID
FROM dbo.ProductBrand b
INNER JOIN dbo.BrandManufacturer bm ON b.ProductBrandID = bm.ProductBrandID
INNER JOIN dbo.Company c ON bm.ProductManufacturerID = c.CompanyID
INNER JOIN dbo.AnimalSource s ON b.AnimalCode = s.AnimalCode
LEFT OUTER JOIN dbo.BrandAccreditation ba ON b.ProductBrandID = ba.ProductBrandID
LEFT OUTER JOIN dbo.Accreditation a ON ba.AccreditationID = a.AccreditationID
LEFT OUTER JOIN dbo.BrandType bt ON b.ProductBrandID = bt.ProductBrandID
LEFT OUTER JOIN dbo.ProductType t ON bt.ProductTypeID = t.ProductTypeID;

The nulls are the in the AccreditationID column. Here are the relationships:

My question is really in two parts:

1. Have I correctly translated my T-SQL query into LINQ?
2. How to I solve the problem related to nulls when I in facts expect nulls (hence the left outer joins)?

Thanks.

Answer 1

(1) Have I correctly translated my T-SQL query into LINQ?

Yes, you did it correctly.

A side note (unrelated to the main issue): There is no need to use different names when doing left outer join. Once you use into clause, the name used to access the entity record is out of scope and can be reused, which makes the rest of the query look similar regardless of the join type (inner or left outer). For instance

join ba in Program.db.BrandAccreditations on b.ProductBrandID equals ba.ProductBrandID into bax
from credJoins in bax.DefaultIfEmpty()

could be

join ba in Program.db.BrandAccreditations on b.ProductBrandID equals ba.ProductBrandID into baJoin
from ba in baJoin.DefaultIfEmpty()

If you remove into and the following from line, it will become inner join and vice versa.

(2) How to I solve the problem related to nulls when I in facts expect nulls (hence the left outer joins)?

This is a typical error with left join and value type fields. Actually the full error message contains the solution:

Either the result type's generic parameter or the query must use a nullable type.

In other words, locate the non nullable value type fields that come from the right side of a left outer join and just convert them to their nullable equivalent.

In your case, here:

select new { c.CompanyID, types.ProductTypeID, b.ProductBrandID, accreds.AccreditationID, s.AnimalName }

such fields are types.ProductTypeID and accreds.AccreditationID, so the fix is (assuming they are of type int):

select new { c.CompanyID, (int?)types.ProductTypeID, b.ProductBrandID, (int?)accreds.AccreditationID, s.AnimalName }