Linq error: The cast to value type 'Int32' failed because the materialized value is null

Question

... But it's not null.

FYI - Many threads exist on this error, but none that I've seen using an anonymous type.

I'm Getting an odd InvalidOperationException in a Linq query.

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

The confusing thing is that it's erroring when creating an anonymous type:

var workstepid = 484,449;

var wsData = ion.xWorkSteps
   .Where(w => w.WorkStepId == workstepId)
   .Select(w => new
   {
       w.WorkStepId,
       w.ServiceId,
       w.Service.TitleId,
       w.Service.Title.OrderId,
       w.Service.Title.AltTitleId
   }).SingleOrDefault();

In LinqPad this particular query runs just fine and the workstepId used returns an integer value for each property of the anonymous type. So why a casting error when there is no null value for any property!?

FYI, the last property AltTitleId is a nullable int, and the other properties are ints. Also, this code was written weeks ago and I didn't get this error until today. Is something funky w/ my EF?

Edit: SOLVED

We use soft-delete in our db's, setting deleted records to Acive=0. It turns out that there is a property set in EF on the edmx tables filtering out inactive records (filter for Active=1). Naturally, this wouldn't effect Linqpad which gives me the expected result, but since this was an older record I was testing, the title record (Service.Title) had been marked Active=0 and was therefore returning null.

Thanks to everyone for the help.

Is there a null in the table for w.workstepid? Or is this a nullable type in the model? — Christian Phillips, Nov 05 '13 at 06:56
This can't run in linqpad. The first line is illegal syntax. — recursive, Nov 05 '13 at 07:11
[Good discussion](http://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null) here. — Bishnu Rawal, Nov 05 '13 at 07:56
Are you lazy-loading the `Service` class? Can you show us your `WorkStep` class? — Colin, Nov 05 '13 at 11:56
@Jens - correct, but I remove "ion." the dbcontext object whenn running in LinqPad. — JM., Nov 05 '13 at 18:34
So, my partner determined the cause, I'll edit OP to reflect the reason. Thanks for the help everyone. — JM., Nov 05 '13 at 18:35

score 0 · Answer 1 · answered Nov 05 '13 at 07:01

0

var workstepid = 484,449;

var wsData = ion.xWorkSteps
   .Where(w => w.WorkStepId == workstepId)
   .Select(w => new
   {
      w.WorkStepId.Value,
      w.ServiceId.Value,
      w.Service.TitleId.Value,
      w.Service.Title.OrderId.Value,
      w.Service.Title.AltTitleId.Value
   }).SingleOrDefault();

Hi you need to add .Value in your int data type to accept null value. hope this helps

answered Nov 05 '13 at 07:01

Goi

46
9

Was able to add ".Value" for AltTitleId, but not the other (AltTitleId is the only nullable FYI). New compile error: Error 231 'AnonymousType#1' does not contain a definition for 'AltTitleId' and no extension method 'AltTitleId' accepting a first argument of type 'AnonymousType#1' could be found (are you missing a using directive or an assembly reference?) – JM. Nov 05 '13 at 07:26

Christian Phillips · Answer 2 · 2013-11-05T07:13:17.763

0

If w.WorkStepId is nullable, try setting your variable as...

int? workstepid = 484449;

edited Nov 05 '13 at 07:13

answered Nov 05 '13 at 07:05

Christian Phillips

18,399
8
53
82

Can you show the code where your using wsdata, this is probably where the error is. – Christian Phillips Nov 05 '13 at 07:29

score 0 · Answer 3 · answered Nov 05 '13 at 07:22

I suppose, the problem is in your SingleOrDefault().

I suppose that you get 0 records:

var workstepid = 484,449;

var len = ion.xWorkSteps
   .Where(w => w.WorkStepId == workstepId)
   .Length();

And then the anonymouse type must be replace by Default (this is what SingleOrDefault do), but there is no default for your anonymous type.

Try to change it to:

    var wsData = ion.xWorkSteps
   .Where(w => w.WorkStepId == workstepId)
   .Select(w => new
   {
       w.WorkStepId,
       w.ServiceId,
       w.Service.TitleId,
       w.Service.Title.OrderId,
       w.Service.Title.AltTitleId
   })
   .Take(1)
   .ToArray();

score 0 · Answer 4 · answered Nov 05 '13 at 11:48

May be there are no Service/Title/Order id somewhere, but if it declared as int anonymous type expect int (not nullable), but from database it returns null. Try to rewrite as :

var workstepid = 484,449;

var wsData = ion.xWorkSteps
   .Where(w => w.WorkStepId == workstepId)
   .Select(w => new
   {
     WorkStepId = (int?)w.WorkStepId,
     ServiceId = (int?)w.ServiceId,
     TitleId = (int?)w.Service.TitleId,
     OrderId = (int?)w.Service.Title.OrderId,
     w.Service.Title.AltTitleId
   }).SingleOrDefault();

Linq error: The cast to value type 'Int32' failed because the materialized value is null

4 Answers4