[LINQ to SQL] System.Linq.Enumerable.ToArray -> System.InvalidOperationException

[LINQ to SQL] System.Linq.Enumerable.ToArray -> System.InvalidOperationException

bamccaig

Member #7,536

July 2006

Essentially, I'm querying a database using LINQ, joining a bunch of tables together, and generating an IQueryable of anonymous types from the result. Then, I use the IQueryable.Distinct method to remove duplicate records and call IEnumerable.AsEnumerable to force it out of SQL and into C#^[1]. Finally, I call the IEnumerable.ToArray method to convert the collection into an array, which will be serialized and sent to the Web client.

Type: System.InvalidOperationException
Message: The null value cannot be assigned to a member with type System.Int32
   which is a non-nullable value type.
StackTrace: at Read_<>f__AnonymousType86`10(ObjectMaterializer`1 )
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at *snip* in *snip*
   at *snip* in *snip*

(The *snips* were to remove code that can be identifying or is otherwise NDA applicable. It was all my own code, the first one being a wrapper that handles exception logging, the second one being the body of a Web method, passed as a delegate, that is the source of this problem. Hopefully the remaining anonymous type names aren't useful to anyone. :-X )

I can't figure out what's wrong... I'll make up a query that resembles what I'm doing to give you an idea...

#SelectExpand
  1using(MyDataContext db = new MyDataContext())
  2{
  var q = (from a in db.A
           join b in db.B on a.b_id equals b.id
           join c in db.C on a.c_id equals c.id into cg
           from c in cg.DefaultIfEmpty()
           from d in db.D on a.d_id equals d.id
           from e in db.E on a.e_id equals e.id into eg
           from e in eg.DefaultIfEmtpy()
           from f in db.F on e.f_id equals f.id into fg
           from f in fg.DefaultIfEmpty()
           where a.id = aid
           select new
           {
               b_id = b.id,
               g = b.g,
               gd = b.gd(),      // The reason I need to get into application
               ed = e.d(),       //     code is to call application methods.
               hd = b.hd(),
               dn = d.n(),
               f = f,
               a_id = a.id,
               i = c.i,
               j = "A literal needed to workaround colleague design. :P"
           }).Distinct().AsEnumerable().ToArray();
 26
  /*
   * Work with q a little bit. In my particular example, I fetch another similar
   * array, and attach both of them to a new anonymous type which is ultimately
   * returned to the client (serialized, etc.).
   */
 32
  return q;
 34}

Judging from the stack trace above, it seems to be failing in the IEnumerable.ToArray method, but I don't know why it would be. It further suggests that there is conflicting type information, but the anonymous type's properties come directly from fields in the LINQ entities. The data model was automatically generated by Visual Studio, and to be sure it hadn't been outdated I just generated it again and still get the same error. All of the fields are nullable with the exception of the record identifier for the main table (in the above example, b), which I think should always have a value and can't be null anyway.

I'm not really sure how to go about debugging this. Unfortunately, the Visual Studio debugger doesn't seem capable of debugging LINQ and even the extension methods that I'm calling don't seem to be available in a debug-able form. The code used to work and I don't know what I did to break it.

So the question is, why is this exception being thrown and what do I have to do to fix it?

** EDIT **

Even if I cast b.id to int?^[2] above, making every field in the anonymous type nullable, it still fails the same way.

** EDIT **

After swapping IEnumerable.ToArray with IEnumerable.ToList, I get the exact same result (the stack trace has a couple of extra methods in there to handle the list, but that's all).

References

I'm still a little if-y when using LINQ about when I'm in SQL and when I'm not so when I'm having problems getting it to work I usually throw something like this in to force it; feel free to correct its usage.
A short form for System.Nullable<int>.

--
I mean the best with what I say. It doesn't always sound that way.

Kibiz0r

Member #6,203

September 2005

bamccaig said:

call IEnumerable.AsEnumerable to force it out of SQL and into C#[1]. Finally, I call the IEnumerable.ToArray method to convert the collection into an array

Incorrect. AsEnumerable is only a cast, it does not force evaluation of the query. The ToFoo methods (such as ToArray) do that.

The fact that it blows up in ToArray just means that something in your query is fucked up. There is no beautiful way to debug it that I know of. You might try turning on .NET source stepping and source server to see if you get a better stack from that, but I bet you already have.

My suggestion is to split up the query until you figure out where the problem is, making sure to force evaluation at every step of the way.

---
https://www.allegro.cc/account/agreement <-- Read it, newbies.

bamccaig

Member #7,536

July 2006

Kibiz0r said:

Incorrect. AsEnumerable is only a cast, it does not force evaluation of the query. The ToFoo methods (such as ToArray) do that.

According to the reference document for Enumerable.AsEnumerable, the cast hides custom methods that would otherwise be used.

Enumerable.AsEnumerable(TSource) Method (System.Linq) said:

AsEnumerable<(Of <(TSource>)>)(IEnumerable<(Of <(TSource>)>)) can be used to choose between query implementations when a sequence implements IEnumerable<(Of <(T>)>) but also has a different set of public query methods available. For example, given a generic class Table that implements IEnumerable<(Of <(T>)>) and has its own methods such as Where, Select, and SelectMany, a call to Where would invoke the public Where method of Table. A Table type that represents a database table could have a Where method that takes the predicate argument as an expression tree and converts the tree to SQL for remote execution. If remote execution is not desired, for example because the predicate invokes a local method, the AsEnumerable<(Of <(TSource>)>) method can be used to hide the custom methods and instead make the standard query operators available.

(Formatting theirs)

It seems to be saying that in the case of LINQ to SQL, the Where method would normally take the resulting expression tree and convert it to SQL, whereas if you call AsEnumerable first then it uses the standard implementation.

I don't at all claim to be an expert on this. I'm just trying to make sense of it. :-X I don't think it accomplishes anything in my above example (because the ToArray should force it to evaluate in application code anyway), but I think just AsEnumerable would work as well.

Kibiz0r said:

You might try turning on .NET source stepping and source server to see if you get a better stack from that, but I bet you already have.

Actually, I hadn't... :-[ The source server thing is prompting me to execute an untrusted command so I'm trying to make sense of it to make sure it isn't pulling from an untrusted source... :-/

Kibiz0r said:

My suggestion is to split up the query until you figure out where the problem is, making sure to force evaluation at every step of the way.

This is why I dislike LINQ. It's a great thing having it integrated directly into the application code, but I find the actual structure of it unintuitive (moreso than SQL) and the tools haven't advanced to a point where you can debug it. :'(

** EDIT **

I figured it out. It doesn't really make sense, but at least I figured it out. After letting the overwhelming confusion subside (I spent the better part of yesterday afternoon struggling with Subversion, which was a nice break), I started this morning with a clear head. I started by commenting out columns that were 'complex' (i.e., based on the result of methods or property getters). Once I did that, and commented out corresponding OrderBy and ThenBy calls, the query ran fine. I then started adding them back in the order of least likely to break things to most likely to break things and eventually narrowed it down to a single property.

The odd thing is, upon investigating the property's getter, I saw nowhere that null could be assigned to an int. The property itself was an int? and it either returned null or returned an int (both completely valid operations). Confused, I lined both the getter and setter with breakpoints and started the debugger. It didn't get to the breakpoints. I looked again at the query and in the back of my mind remembered that I was left outer joining to that particular table (i.e., join x on y into g from x in g.DefaultIfEmpty()), but I wasn't checking for null before attempting to access properties of the entity (in my mock code above, this was c.i). I wrapped it into a ternary operator...

...
i = c == null ? null : c.i,
...

...and sure enough the query ran fine with everything uncommented. I would have expected a System.NullReferenceException (and I've gotten them before). I really can't explain why it's throwing System.InvalidOperationException based on assigning null to an System.Int32. :-/ Anyone?

--
I mean the best with what I say. It doesn't always sound that way.