CODEWITHSUNDEEP
c#.netlinqentity-framework
Mike Christensen
Mike Christensen

Reputation: 91666

Concatenating three lists into one with LINQ throws an exception

Ok, I must be doing something dumb, but shouldn't this work? I have following three lists:

var commonViews = (from v in context.TPM_VIEWS where v.VIEWID < 0 select v); // IQueryable<TPM_VIEWS>
var ownedViews = (from v in context.TPM_VIEWS where v.OWNERID == userId && v.VIEWID > 0 select v); // IQueryable<TPM_VIEWS>
var sharedViews = (from v in context.TPM_USER.Include("TPM_VIEWS2") where v.USERID == userId select v).First().TPM_VIEWS2; // EntityCollection<TPM_VIEWS>

Each list has the proper values and count. I can return any one of these lists:

return commonViews.ToList();

And I can return a any two of these lists:

return commonViews.Concat(ownedViews).ToList();

However, when I try to return all three:

return commonViews.Concat(ownedViews).Concat(sharedViews).ToList();

I get the exception:

Unable to create a constant value of type 'Entity.TPM_VIEWS'. Only primitive types or enumeration types are supported in this context.

What am I doing wrong? All three values are indeed enumerable. Mostly, I'm asking this question because it's the best possible way to guarantee I'll notice the problem 30 seconds after posting.

UPDATE:

I'm 93% sure the problem is here:

var sharedViews = (from v in context.TPM_USER.Include("TPM_VIEWS2") where v.USERID == userId select v).First().TPM_VIEWS2;

This looks like an enumerable list of TPM_VIEWS object, and I can call ToList() on it and get the correct data, but it doesn't play well with the other lists.

UPDATE 2:

This actually works. Points to the person who can tell me why!

commonViews.ToList().Concat(ownedViews.ToList()).Concat(sharedViews.ToList()).ToList();

Upvotes: 10

Views: 397

Answers (2)

Thomas Levesque
Thomas Levesque

Reputation: 292555

This actually works. Points to the person who can tell me why!

commonViews.ToList().Concat(ownedViews.ToList()).Concat(sharedViews.ToList()).ToList();

That's because each of the original queries is executed separately; you're only concatenating the results in memory. There seems to be a bug in the Entity Framework query translator when you combine the 3 queries, but when you call ToList on each of them, they're no longer EF queries, they're just lists, so they're concatenated using Linq to Objects.

Upvotes: 1

SLaks
SLaks

Reputation: 887767

The problem is that Concat() on an EF IQueryable<T> will turn the entire concatenation into a single query.

When you call .Concat(sharedViews), you're passing a scalar (pre-loaded) collection of your nested entity class.
EF doesn't know how to convert that into a query, so it complains.

You can make it faster by calling AsEnumerable() instead of ToList().

Upvotes: 5

Related Questions