XML data type

How do I set EF to use an SQL datatype of XML for an object? Moreover, how does one create SQL computed columns.

We store alot of dynamic data that some folks cause meta data per row/record, it isn't standard and 开发者_StackOverflow中文版therefore we rely upon the xml data structure and then use computed columns to create so keys that we may use for faster SQL searches.

Quite frankly - an expando object mapping to an xml column would really float our boat.

Thanks in advance.

The original question was:

How do I set EF to use an SQL datatype of XML for an object?

In code first, you can do it like this:

[Column(TypeName="xml")]
public string Foo {get; set;}

Or through the fluent api:

modelBuilder.Entity<MyEntity>()
            .Property(x => x.Foo)
            .HasColumnType("xml");

You still need to interpret the property as a string and you're on your own for converting it to xml within C#. But this way it will get generated as an xml column and you can still perform direct sql queries against it using sql's xml functions.

All you can do in EF is to access/read an XML column as a string - from there on out, you're on your own. There's no "built-in" mechanism to turn that XML into a serialized object or something like that.

As for computed columns:

• you can define a simple expression in your SQL

  ALTER TABLE dbo.YourTable
    ADD PriceWithTax AS PriceWithoutTax * (1 + TaxRate)
  

  This would give you a new column PriceWithTax based on your column PriceWithoutTax and another column called TaxRate (0.15 for 15% tax)

• or you can create a stored function that returns a single value, and call that

  ALTER TABLE dbo.YourTable
    ADD PriceWithTax as dbo.AddTaxToPrice(PriceWithoutTax, TaxRate)
  

  We also use this method of a stored scalar function to parse into the XML stored in an XML column, and extract certain bits and pieces into a computed column.

If your computation is deemed "deterministic" by SQL Server, you can also add a PERSISTED keyword to your column definition. In this case, your values are computed once and actually persisted / stored on that table, just like any other column. You can now even index those columns! This works great with e.g. scalar functions that grab bits from within an XML and expose it on the "parent" table.

While not the answer you're looking for, NHibernate fully supports the kind of scenarios you're dealing with. Two approaches:

• Using an XDocument or XmlDocument property in your domain model. It works out of the box and you can map it to an xml sql type, nvarchar(max), etc.
• Using a custom type to do whatever magic you want with the xml column (including mpping to a dynamic object, a value type, a dictionary, or anything you can dream of)

Also, for computed properties in general, you can take a look at formula and readonly.

If you are early enough in your project and require this kind of flexibility, you might consider switching.