How to cast DbSet<T> to List<T>

Question

Given the following simplified Entity Framework 6 context, I am trying to populate a List with the entities but having problems with how to cast (I believe) via reflection.

public class FooContext : DbContext
{
   public virtual IDbSet<FooClass> Foo { get; set; }    
   //...
}

public class FooClass
{
    public int Id{ get; set; }
    public string Name {get; set; }
    //...    
}

public main()
{
     using (var context = new FooContext())
     {
         var sets = typeof(FooContext).GetProperties().Where(pi => pi.PropertyType.IsInterface && pi.PropertyType.GetGenericTypeDefinition().ToString().ToLower().Contains("idbset"));

         foreach (var set in sets)
         {
             //When I debug and enumerate over 'value' the entire results are shown so I believe the reflection part is OK.
             var value = set.GetValue(context, null);

             //Always returns null. How can I cast DbSet<T> to List<object> or List<T>? 
             var list = value as List<object>();

             //...
         }
     }
}

I'm doing this for utility method for some integration testing I am doing. I am trying to do this without using direct inline SQL calls (using SqlConnection and SqlCommand etc) to access the database (as the datastore may change to Oracle etc).

Solution 1

IDBSet inherits from IQueryable<TEntity>, IEnumerable<TEntity>, IQueryable, and IEnumerable, so you can't directly cast it to a list that way. You could get a List<TEntity> of all entities in the DBSet though by using .ToList() or .ToListAsync()

THis creates a copy of all entities in memory though, so you should consider operating with LINQ directly on the DBSet

Solution 2

//dont forget using System.Linq;
using (var context = new FooContext())
{
   IQueryable<FooClass> rtn = from temp  in context.Foo select temp;
   var list = rtn.ToList();
}

Solution 3

Alongside VicYam's answer, here is an easier way with a detailed explanation.

Once you add the System.Linq namespace, you no longer need to get IQueryable and then convert to a List. Instead, you will get the extension methods on the DbSet object.

What does this mean? You can simply return the DbSet by itself as it inherits from IEnumerable. The consumer, possibly a dev, can then do a .ToList or any other list type they may need.

Easiest

This will return the DbSet as is, which already inherits from IEnumerable which is a list type that can be then converted to a List.

List<ObjectType> list = new List<ObjectType>();
using (var context = new FooContext())
{
    list = context.Foo;
}

Alternative

You can immediately convert from IEnumerable to a List.

using (var context = new FooContext())
{
    var list = context.Foo.ToList();
}

Still want VicYam's answer but as a one line?

using (var context = new FooContext())
{
    var list = context.Foo.AsQueryable().ToList();
}

Comments

[DimaSan]

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

[Bassem Akl]

There is no such a ToList() method in the DbSet.

[BorisD]

I had a parsing error trying to retreive all elements in a table. Sending a list instead of a "IQueryable" solved the issue.

[Dissident Rage]

This gets you a SqlNullValueException