Map Entity Framework properties to XML in database

Question

I have a class like this:

public class GeneralClass
{
    public int Id {get; set;}
    public string Name {get; set;}
}

which is a pretty generic class. I also have a derived class like this:

public class DerivedClass: GeneralClass
{ 
    public int SpecificProperty {get; set;}
    public string AnotherSpecificProperty {get; set;}
    public bool BooleanSpecificProperty {get;set;}
}

The problem is, I can have lots of derived classes (more than 10) in my app, that´s why inheritance in a database IS NOT AN OPTION. The solution I came up with is to have GeneralClass as a table, with an XML column containing the specific properties.

Something like this:

CREATE TABLE 
General(Id int primary key, 
        Name nvarchar(50),  
        SpecificProperties xml);

where the specific properties contain the properties of the derived class.

The question is: How can I SAVE and QUERY this xml column using Entity Framework, and deserialize the xml into the properties?

Answer 1

"...I can have lots of derived classes (more than 10) in my app, that´s why inheritance in a database IS NOT AN OPTION" - why not!?

There are two main inheritance patterns - table per hierarchy (TPH) and table per type (TPT). While the first one might seem quite a waste of SQL resources, the second one perhaps will match your requirements. There is one more pattern - table per concrete type (TPC), which may be considered a variation of TPT. You do not have abstract classes in your hierarchy, or at least the code snippet you've posted does not, so it is quite natural to use TPT and query the way you're very familiar with, say LINQ to entities. More information about EF and inheritance patterns here and here.

Still, if you do insist on using XML you may declare the field as XML type (this is a column data type, which SQL server supports). You can query it like described here, here, and here.