Entity Framework 4 TPH inheritance, how to change one type into another?

Question

I have found some information regarding this but not enough for me to understand what the best practice for is for this scenario. I have your typicaly TPH setup with an abstract base class "Firm". I have several children "Small Firm", "Big Firm" etc inheriting from Firm. In reality I actually have different realistic classifications for firms but I am trying to keep it simple in this example. In the database as per TPH I have a single Firm table with a FirmTypeId column (int) that differentiates between all these types. Everything works great except I have a requirement to allow a user to change one type of firm into another. For example a user might have made a mistake when adding the firm, and would like to change it from Big Firm to Small Firm. Because entity framework does not allow exposing the discriminating database column to be exposed as a property, I don't believe there is a way to change one type into another via EF. Please correct me if I am wrong. The way I see it I have two options:

1. Don't use TPH. Simply have a Firm Entity and go back to using .Where(FirmTypeId == something) to differentiate between the types.
2. Execute SQL directly using context.ExecuteStoreCommand to update the FirmTypeId column of the database.

I've seen a post where people suggest that One of the tenets of OOP is that instances cannot change their type. Although that makes perfect sense to me, I just don't seem to be able to connect the dots. If we were to follow this rule, then the only time to use any kind of inheritance (TPH/TPT) is when one is sure that one type would never be converted into another. So a Small Firm will never become a Big Firm. I see suggestions that composition should be used instead. Even though it doesn't make sense to me (meaning I don't see how a Firm has a Big Firm, to me a Big Firm is a Firm), I can see how composition can be modeled in EF if the data is in multiple tables. However in a situation where I have a single table in the database it seems it's TPH or what I've described in #1 and #2 above.

Answer 1

I've ran into this problem in our project, where we have core DBContext and some "pluggable" modules with their own DBContexts, in which "module user" inherits "core (base) user". Hope that's understandable.

We also needed the ability to change (let's call it) User to Customer (and if needed also to another "inherited" Users at the same time, so that user can use all those modules.

Because of that we tried using TPT inheritance, instead of TPH - but TPH would work somehow too.

One way is to use custom stored procedure as suggested by many people...

Another way that came to my mind is to send custom insert/update query to DB. In TPT it would be:

private static bool UserToCustomer(User u, Customer c)
    {
        try
        {
            string sqlcommand = "INSERT INTO [dbo].[Customers] ([Id], [Email]) VALUES (" + u.Id + ", '" + c.Email + "')";
            var sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString);
            sqlconn.Open();
            var sql = new SqlCommand(sqlcommand, sqlconn);
            var rows = sql.ExecuteNonQuery();
            sqlconn.Close();

            return rows == 1;
        }
        catch (Exception)
        {
            return false;
        }
    }

In this scenario Customer inherits User and has only string Email.

When using TPH the query would only change from INSERT ... VALUES ... to UPDATE ... SET ... WHERE [Id] = .... Dont forget to change Discriminator column too.

After next call dbcontext.Users.OfType<Customer> there is our original user, "converted" to customer.

---

Bottomline: I also tried solution from another question here, which included detaching original entity (user) from ObjectStateManager and making new entity (customer) state modified, then saving dbcontext.SaveChanges(). That didn't work for me (neither TPH nor TPT). Either because using separate DBContexts per module, or because EntityFramework 6(.1) ignores this. It can be found here.

Answer 2

Yes, you got it all right. EF inheritance does not support this scenario. The best way to change a Firm type for an existing Firm is to use a stored procedure.

Please take a look at this post for more info:
Changing Inherited Types in Entity Framework

Answer 3

Unless you explicitly want to use the polymorphic functionality of the relational inheritance, then why not look at a splitting strategy?

http://msdn.microsoft.com/en-us/data/ff657841.aspx

Answer 4

EDIT: APOLOGIES, THIS IS AN EF 6.x ANSWER

I'm posting example code for completeness. In this scenario, I have a base Thing class. Then, sub-classes: ActiveThing and DeletedThing

My OData ThingsController, has a main GetThings which I intend to only expose ActiveThings, but, it's GetThing(ThingId) can still return either type of object. The Delete action performs a conversion from ActiveThing to DeletedThing much in the way requested by the OP, and much in the manner described in other answers. I'm using inline SQL (parameterized)

public class myDbModel:DbContext
{
    public myDbModel(): base("name=ThingDb"){}

    public DbSet<Thing> Things { get; set; }  //db table

    public DbSet<ActiveThing> ActiveThings { get; set; } // now my ThingsController 'GetThings' pulls from this

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       //TPH (table-per-hierarchy):
      modelBuilder.Entity<Ross.Biz.ThingStatusLocation.Thing>()
        .Map<Ross.Biz.ThingStatusLocation.ActiveThing>(thg => thg.Requires("Discriminator").HasValue("A"))
        .Map<Ross.Biz.ThingStatusLocation.DeletedThing>(thg => thg.Requires("Discriminator").HasValue("D"));
    }

}

Here's my updated ThingsController.cs

public class ThingsController : ODataController
{
    private myDbModel db = new myDbModel();

    /// <summary>
    /// Only exposes ActiveThings (not DeletedThings)
    /// </summary>
    /// <returns></returns>
    [EnableQuery]
    public IQueryable<Thing> GetThings()
    {
        return db.ActiveThings;
    }

    public async Task<IHttpActionResult> Delete([FromODataUri] long key)
    {
        using (var context = new myDbModel())
        {
            using (var transaction = context.Database.BeginTransaction())
            {
                Thing thing = await db.Things.FindAsync(key);
                if (thing == null || thing is DeletedThing) // love the simple expressiveness here
                {
                    return NotFound();//was already deleted previously, so return NotFound status code
                }

                //soft delete: converts ActiveThing to DeletedThing via direct query to DB
                context.Database.ExecuteSqlCommand(
                    "UPDATE Things SET Discriminator='D', DeletedOn=@NowDate WHERE Id=@ThingId", 
                    new SqlParameter("@ThingId", key), 
                    new SqlParameter("@NowDate", DateTimeOffset.Now)
                    );

                context.ThingTransactionHistory.Add(new Ross.Biz.ThingStatusLocation.ThingTransactionHistory
                {
                    ThingId = thing.Id,
                    TransactionTime = DateTimeOffset.Now,
                    TransactionCode = "DEL",
                    UpdateUser = User.Identity.Name,
                    UpdateValue = "MARKED DELETED"
                });
                context.SaveChanges();
                transaction.Commit();
            }
        }

        return StatusCode(HttpStatusCode.NoContent);
    }
}