c# - 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.