Generic method to retrieve DbSet<T> from DbContext

Question

I'm using the Entity Framework with a large database (made up of more than 200 tables).

Trying to create a generic method that returns the DbSet<T> of a specific table T (i.e. class, which can be TableA).

The entity class that was (automatically) created using the entity data model looks like so:

public partial class sqlEntities : DbContext
{

    public virtual DbSet<TableA> TableA { get; set; }
    public virtual DbSet<TableB> TableB { get; set; }
    public virtual DbSet<TableC> TableC { get; set; }
    ... // other methods

}

My main class is like this

public class TableModifier
{
   // Should return first 10 elements from a table of that type T
   public IQueryable<T> GetFromDatabase<T>() where T : EntityObject
   {
       try
       {
           using (sqlEntities ctx = new sqlEntities())
           {
               // Get the DbSet of the type T from the entities model (i.e. DB)
               DbSet<T> dbSet = ctx.Set<T>();
               return dbSet.Take(10);
           }
       }
       catch (Exception ex)
       {
           // Invalid type was provided (i.e. table does not exist in database)
           throw new ArgumentException("Invalid Entity", ex);
       }
   }
   ... // other methods
}

I have to set a constraint where T : EntityObject on T to be within the EntityObject bounds. If there was no such constraint then the DbSet<T> dbSet would complain (i.e. T must be a reference type) that it might be getting more than it expects in terms of types (based on this).

The problem occurs when I try to actually call the method with a specific type.

 [TestMethod]
 public void Test_GetTest()
 {
     TableModifier t_modifier = new TableModifier();

     // The get method now only accepts types of type EntityObject
     IQueryable<TableA> i_q = t_modifier.GetFromDatabase<TableA>();
 }

It gives an error:

There is no implicit reference conversion from 'TableMod.TableA' to
'System.Data.Entity.Core.Objects.DataClasses.EntityObject'.

How can I (cast?) the TableA type as an EntityObject if I know it exists for that entity model?

Though this is incorrect syntax (and logic) this is what I'm after:

 t_modifier.GetFromDatabase<(EntityObject)TableA>();

How can I define the TableA (along with all the other 200 tables) type to be a part of EntityObject?

---

A potential solution

Turns out my constraint was too specific, all I needed to change was from where T : IEntity to

where T : class

So the T is what the DbSet<T> initially expected, a class type

Saves the trouble of having to add implementations to the 200+ table classes, TableA, TableB, ...

Then of course there's other problems such as changing the return type from IQueryable<T> to List<T> since the IQueryable would otherwise be returned outside of the scope of DbContext (i.e. sqlEntities) rendering it useless.

Answer 1

For any future googlers, my colleague and I just hacked this out in Visual Basic (EF 6 version). It works for our use case of simply getting a list back out but will probably work for the other use cases. No try catch or checking in this one.

Private Class getList(Of T As Class)
    Public Shared Function getList() As List(Of T)
        Using ctx As New MVPBTEntities()
            ' Get the DbSet of the type T from the entities model (i.e. DB)
            Dim dbSet = ctx.Set(Of T)()
            Return dbSet.ToList
        End Using
    End Function
End Class

Answer 2

Why don't you try changing your constrain to class instead of EntityObject

public IQueryable<T> GetFromDatabase<T>() where T : class

Answer 3

I had the same requirement and solved it by using the following:

public static void GetEntitiesGeneric<TEntity>()// where TEntity : System.Data.Entity.Core.Objects.DataClasses.EntityObject  <-- NO LONGER NEEDED
{
    try
    {
        var key = typeof(TEntity).Name;
        var adapter = (IObjectContextAdapter)MyDbContext;
        var objectContext = adapter.ObjectContext;
        // 1. we need the container for the conceptual model
        var container = objectContext.MetadataWorkspace.GetEntityContainer(
            objectContext.DefaultContainerName, System.Data.Entity.Core.Metadata.Edm.DataSpace.CSpace);
        // 2. we need the name given to the element set in that conceptual model
        var name = container.BaseEntitySets.Where((s) => s.ElementType.Name.Equals(key)).FirstOrDefault().Name;
        // 3. finally, we can create a basic query for this set
        var query = objectContext.CreateQuery<TEntity>("[" + name + "]");

        // Work with your query ...
    }
    catch (Exception ex)
    {
        throw new ArgumentException("Invalid Entity Type supplied for Lookup", ex);
    }
}

The code was taken from Using Generics for Lookup Tables in Entity Framework and adapted for EF 6 using the DbContext (first part of the method where the objectcontext is extracted from the dbcontext

Hope it helps

Answer 4

Issue

I suppose your TableA class doesn't implement EntityObject. That's why you're getting this error. To solve this you can have an abstract class/interface which will be base for all context entities (i.e. IContextEntity which will have unique Id definition):

public class TableA : IContextEntity
{
   ...
}

Then same method but with new interface instead of EntityObject and you can mock/test it easily

public IQueryable<T> GetFromDatabase<T>() where T : IContextEntity
{
     ...
}

---

Usage(Tests)

Second important thing is the way you want to use this method. In case of Entity Framework context it is really important to have separation between integration and unit tests. In code you provided you're trying to reach database which means that this test will be integration:

using (sqlEntities ctx = new sqlEntities()) // This will open a DB connection

Connecting to a databases or external sources is usually a bad practice unless you know what you do and this is exactly it. If you just need some fake/dummy data to perform an action on it - use Stubs.

Answer 5

I don't know how you have created your model, and thus how your entities look like. But, if it's Code First, the entity classes don't inherit from a common base class, so you cannot add a type constraint to your generic.

I don't recommend using a base class to be able to specify a constraint. It's much better to do it using an interface. An empty interface will allow you to specify a constraint without having to change your classes at all.

So, what you can do is define an interface like this:

public interface IEntity {};

And then:

• implement it in all classes, which can be done in partial classes files, modifying a T4 template or in some other way depending on how your model looks like
• use it to specify the generic type constrain with where IEntity

This is the cleanest way to do it, without any interference to your classes.