IQueryable to List

Question

I'm using System.Linq.Dynamic to allow me to dynamically select a list of fields from a query like this:

finalQuery = query.Select(string.Format("new({0})", string.Join(",", selectors)));

Where selectors is just a List<string> with all the fields I want. This works great, but this version of the extension method Select returns an IQueryable. Not this is not IQueryable<T>. If I have an IQueryable<T> I can simply do a .ToList() to convert it into a list and force the query to actually run on the database, but with the non-generic IQueryable, that method doesn't exists.

This is because ToList is inherited from IEnumerable<T> which IQueryable<T> inherits and IQueryable, obviously, doesn't.

So what's the most efficient way to get an IQueryable to execute the query and give me back a list? I can do this:

List<object> rtn = new List<object>();
foreach (var o in finalQuery)
{
    rtn.Add(o);
}

But it seems like their ought to be an easier way.

Edit: In response to suggestions, I tried both:

finalQuery.Cast<object>().ToList();

and:

finalQuery.Cast<dynamic>().ToList();

Which both give NotSupportedExceptions with the message:

Unable to cast the type 'DynamicClass1' to type 'System.Object'. LINQ to Entities 
only supports casting EDM primitive or enumeration types.

possible duplicate of [Where is the ToList() method? (IQueryable)](http://stackoverflow.com/questions/12254855/where-is-the-tolist-method-iqueryable) — Eugene Podskal, Aug 22 '14 at 18:16
did you try Cast? http://msdn.microsoft.com/en-us/library/vstudio/bb301460(v=vs.100).aspx — Mike Cheel, Aug 22 '14 at 18:16
@MikeCheel: I tried casting to `object` and it didn't like that at all. `NotSupportedException`, "unable to cast type 'DynamicClass1' to type type 'System.Object'" — Matt Burland, Aug 22 '14 at 18:19
@EugenePodskal: It's clearly related (so thanks for bring it up), but doesn't answer my question. Reed's answer is close, by the `ArrayList` he posts doesn't actually work either. `IQueryable` is not `ICollection`. — Matt Burland, Aug 22 '14 at 18:34

Tim S. · Accepted Answer · 2014-08-22T19:05:00.050

20

This appears to be a limitation in the way LINQ to Entities translates IQueryable.Cast with anonymous types. You can work around this by using it as an IEnumerable (your working example does this). This causes the code to do the cast in the .NET runtime after it's retrieved from the DB, instead of trying to handle it in the DB engine. E.g.

IEnumerable finalQuery = query.Select(string.Format("new({0})",
                                                   string.Join(",", selectors)));
var result = finalQuery.Cast<dynamic>().ToList();

Or

public static IList<T> CastToList<T>(this IEnumerable source)
{
    return new List<T>(source.Cast<T>());
}

var finalQuery = query.Select(string.Format("new({0})",
                                            string.Join(",", selectors)));
var result = finalQuery.CastToList<dynamic>();

edited Aug 22 '14 at 19:05

answered Aug 22 '14 at 18:59

Tim S.

55,448
7
96
122

I'm sure your analysis of this being a problem with Linq to entities and anonymous types is spot on, but I tried `Cast()` already and that gives me a `NotSupportedException`. Doing a `foreach` seems to be the only thing that works, which is kind of odd. – Matt Burland Aug 22 '14 at 19:18
1

@MattBurland when the compile-time type is `IQueryable` and you do a `Cast<>`, that calls a different method than when it's an `IEnumerable`. When it's `IQueryable`, it tells LINQ to Entities that it wants to cast. When it's an `IEnumerable`, it does something similar to your `foreach`. – Tim S. Aug 22 '14 at 19:37
Ah! I missed that you had `IEnumerable` rather than `IQueryable`. Switching does actually work. Thank you. – Matt Burland Aug 22 '14 at 19:42
That may work but dodging Linq to Entities and just returning the full result set instead is not a solution. If this was a Join that came before another join and many other operations, you'd end up with those subsequent operations running in the app instead of the database, which defeats the purpose of using Linq to Entities. The point is to translate the code to a database query that can run in the database server so the results are narrowed down before sending them to the app, instead of sending all the data up front. – Triynko Sep 25 '15 at 18:59

IQueryable to List

1 Answers1

Linked