Geek Answers Handbook

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

I get the following error while executing a LINQ query in my database:

An attribute like "System.Int32" failed because the materialized value is null.

I believe it because one of the columns returns zero. 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();

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

Zeros are the AccreditationID column. Here are the ratios:

Entity Relationship Diagram

My question really has two parts:

  • Did I translate my T-SQL query to LINQ correctly?
  • How to solve the problem associated with zeros when I expect zeros in facts (hence, left outer joins)?

Thank.

+4
1

(1) T-SQL LINQ?

, .

( ): left outer join . into, , , , , (inner left outer). ,

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


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

into from, inner join .

(2) , , (, )?

left join . :

, .

, , right left outer join .

, :

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

types.ProductTypeID accreds.AccreditationID, ( int):

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

More articles: