CODEWITHSUNDEEP
.netentity-framework
John Rudy
John Rudy

Reputation: 37850

How to query tables in EF by string name of table?

I'm trying to write what seems to me a simple scenario. In most of our applications, we have a system for allowing the user to edit the values of drop-downs -- lookup tables.

The drop-down tables are typically very small, consisting of just a few fields:

They may have additional fields, which obviously would differ from table to table. The id, LastModifiedBy and LastModifiedDate properties are all defined in a base entity class from which all our other entities derive.

We need to be able to query an EF context for an entity which -- aside from three common fields -- is completely unknown (both its name/type and fields) until runtime. This negates generics, which are otherwise wonderful. We are able to solve a lot of the reflection problems on our own -- this will handle the unknown columns, as we can enumerate our properties and go from there. The harder problem we're seeing is trying to query EF, and get a serializable entity back, when we don't know the table until runtime.

So I suppose the simplest way to ask this is: How can I query EF, and get a serializable entity back from it, using a string name for the entity?

What we're looking to implement is a method whose signature looks something like this:

public BaseEntity RetrieveEntity(string entityType, int? id);

Once we have that core in place, we should be able to easily adapt it to our other scenarios (insert, update, retrieve a paged list for the grid, etc).

Just so we have something concrete to work with, we can use the table definition above. We'll call the entity StatusCode, and assume its definition is as follows:

public partial class StatusCode : BaseEntity // BaseEntity gives us id, LastModifiedBy, LastModifiedDate
{
    public string StatusCodeDescription { get; set; }
}

public class StatusCodeMap : EntityTypeConfiguration<StatusCode>
{
    // boilerplate comments removed for brevity
    this.HasKey(t => t.id);

    this.Property(t => t.StatusCodeDescription)
        .IsRequired()
        .HasMaxLength(255);

    this.ToTable("StatusCode");
    this.Property(t => t.id).HasColumnName("StatusCodeId");
    this.Property(t => t.StatusCodeDescription).HasColumnName("StatusCodeDescription");
    this.Property(t => t.LastModifiedBy).HasColumnName("LastModifiedBy");
    this.Property(t => t.LastModifiedDate).HasColumnName("LastModifiedDate");
}

IF I could categorically state that all the drop downs would have a description, and nothing else, then I could just create an abstract class from which the drop-downs derive and deal with that. I'd still have to be able to query dynamically by name and retrieve an instance of that object, though. And truthfully, I cannot guarantee that this is the case. Some of these will have other fields -- a boolean property here or there, maybe a code mapped to the description for use in back-end integration, etc. So just using a base class is not the solution, and won't fully play.

PS: I'm certain this is actually a dupe. I know I can't be the first to have this issue. I'm very happy to close it if we can find the solution elsewhere on SO. However, I've spent the entire afternoon on Google trying to find a holistic solution to this problem, and have failed miserably. That said, my Google-Fu is probably failing me today.

Upvotes: 0

Views: 259

Answers (1)

Colin
Colin

Reputation: 22595

You say you have a BaseEntity class and all these classes inherit from it.

What type of inheritance are you using? TPH?

Have you added a public DbSet<BaseEntity> BaseEntities { get; set; } to the context?

I'm pretty sure if you do that then your method can be implemented like this:

public BaseEntity RetrieveEntity(string entityType, int? id)
{
   var e = context.BaseEntities.Find(id.GetValueOrDefault(-1));
   if(e.GetType() != entityType)
      return null;

   return e;
}

Not sure why you'd want to do that though. I'd have this:

public BaseEntity RetrieveEntity(int id)
{     
   return context.BaseEntities.Find(id);
}

and use it like this

StatusCode sc = RetrieveEntity(int id) as StatusCode;
if(sc != null)
   //etc

or this:

public T RetrieveEntity<T>(int id) where T: BaseEntity
{
   return context.BaseEntities.Find(id) as T
}

and use it like this:

StatusCode sc = RetrieveEntity<StatusCode>(int id)
if(sc != null)
   //etc

edit or you could just use the generic DbSet like this

public T RetrieveEntity<T>(int id) where T: BaseEntity
{
   return context.DbSet<T>().Find(id)
}

Upvotes: 1

Related Questions