XML columns in a Code-First application

Question

I'm trying to create an XML column in Code First. I'm well aware Entity Framework doesn't fully support XML columns, and that it reads them as a string. That's fine. I would still like the column type to be XML, though. Here's my class:

class Content
{
    public int ContentId { get; set; }

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

    [NotMapped]
    public XElement Xml { get { ... } set { ... } }
 }

Problem is, that Code First Migrations completely ignores the Column attribute and creates the field as an nvarchar(max) . I tried using [DataType("xml")], but that, too, didn't work.

Is this a migration bug?

Solution 1

Have you tried:

public String XmlContent { get; set; }

public XElement XmlValueWrapper
{
    get { return XElement.Parse(XmlContent); }
    set { XmlContent = value.ToString(); }
}

public partial class XmlEntityMap : EntityTypeConfiguration<XmlEntity>
{
    public XmlEntityMap()
    {
        // ...
        this.Property(c => c.XmlContent).HasColumnType("xml");

        this.Ignore(c => c.XmlValueWrapper);
    }
}

Solution 2

I achieved what is needed with an attribute and I decorated my model class xml field with the attribute.

[XmlType]
public string XmlString { get; set; }

[NotMapped]
public XElement Xml
{
    get { return !string.IsNullOrWhiteSpace(XmlString) ? XElement.Parse(XmlString) : null; }
    set {
        XmlString = value == null ? null : value.ToString(SaveOptions.DisableFormatting);
    }
}

Got the help of these 2 articles:

https://entityframework.codeplex.com/wikipage?title=Code%20First%20Annotations

https://andy.mehalick.com/2014/02/06/ef6-adding-a-created-datetime-column-automatically-with-code-first-migrations/

Solution

Define Attribute

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
public class XmlType : Attribute
{
}

Register Attribute in Context

In the "OnModelCreating" of the context

modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<XmlType, string>("XmlType", (p, attributes) => "xml"));

Custom Sql Generator

public class CustomSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(ColumnModel column, IndentedTextWriter writer)
    {
        SetColumnDataType(column);

        base.Generate(column, writer);
    }

    private static void SetColumnDataType(ColumnModel column)
    {
        // xml type
        if (column.Annotations.ContainsKey("XmlType"))
        {
            column.StoreType = "xml";
        }
    }
}

Register Custom Sql Generator

In the Migration Configuration constructor, register the custom SQL generator.

 SetSqlGenerator("System.Data.SqlClient", new CustomSqlGenerator());

Solution 3

This can now be achieved, without the need for an additional property, in Entity Framework Core 2.1+, using a SQL Server XML column type and value conversions.

public class Content
{
    public int ContentId { get; set; }

    public XElement Xml { get; set; }
}

internal class ContentEntityTypeConfiguration : IEntityTypeConfiguration<Content>
{
    public void Configure(EntityTypeBuilder<Content> builder)
    {
        builder.HasKey(e => e.ContentId);

        builder.Property(e => e.ContentId)
            .ValueGeneratedOnAdd();

        builder.Property(e => e.Xml)
            .HasConversion(
                xml => xml.ToString(),
                xml => xml != null ? XElement.Parse(xml) : null)
            .HasColumnType("xml");
    }
}

public class MyDbContext : DbContext
{
    public DbSet<Content> Contents { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new ContentEntityTypeConfiguration());
    }
}

Comments

[mikalai]

With "[Column(TypeName="xml")]" (note TypeName) it generates scripts all right for me: "ALTER TABLE [dbo].[Blogs] ADD [XmlString] [xml]". Probably some other migration actions are wrong..

[zmbq]

Yes, sorry, it's TypeName in the actual code. SO doesn't catch this error... I'm using EF-5, and no - no ALTER TABLE generated at all. Weird.

[mikalai]

Well, now I am afraid to be a captain obvious. My steps are exactly as per sample project (msdn.microsoft.com/en-us/data/jj554735): a) add DBSet<Content > to context class; b) add Content class as you specify; c) run "Add-Migration" in package manager console. d) Run "Update-Database -Script -Verbose".

[zmbq]

Well, migrations work great for everything else, it's just the XML columns that are still varchars. I'll just have to add the ALTER TABLE myself to one of the generated migration codes.

[zmbq]

Since I used the identical Data annotation, I didn't try this. Is there a bug in the data annotations that doesn't exist in Fluent API?

[TDaver]

I don't know, I've only worked with the Annotated API once. Fluent seemed much more adaptable - and very good for clean, tech independent POCOs.

[zmbq]

Well, it does work. I don't know why the DataAnnotation version doesn't. Thanks!

[TDaver]

@zmbq: can I get the bounty too? :)

[zmbq]

Ooops, I thought by accepting the answer I was done with it. Bounty awarded.

[aruno]

I'm not sure what I'm missing but I have no clue how this is supposed to works! The wrapper makes sense but public FIlterMap() has no return type. What is XmlEntityMap and how does XmlEntityMap know anything about my entity?

[Shahryar Saljoughi]

I'm new to C#. What is the type of XmlValueWrapper? it is not a method is it?