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?

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.. — mikalai
– mikalai, Commented Oct 5, 2012 at 12:52
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. — zmbq
– zmbq, Commented Oct 5, 2012 at 21:56
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". — mikalai
– mikalai, Commented Oct 6, 2012 at 11:37
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
– zmbq, Commented Oct 6, 2012 at 22:08

Community · Accepted Answer · 2018-08-23 17:18:08Z

44

+100

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);
    }
}

edited Aug 23, 2018 at 17:18

CommunityBot

11 silver badge

answered Oct 1, 2012 at 14:20

TDaver

7,2745 gold badges50 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

zmbq Over a year ago

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 Over a year ago

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 Over a year ago

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

TDaver Over a year ago

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

Simon_Weaver Over a year ago

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?

|

Huzaifa Tapal · Accepted Answer · 2016-04-12 03:38:48Z

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());

Andy Cox · Accepted Answer · 2020-06-18 10:30:37Z

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());
    }
}

Cybul · Accepted Answer · 2014-03-17 21:42:08Z

0

But what if XmlContent is null ??

Maybe :

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

answered Mar 17, 2014 at 21:42

Cybul

712 bronze badges

Collectives™ on Stack Overflow

XML columns in a Code-First application

4 Answers 4

7 Comments

Solution

Define Attribute

Register Attribute in Context

Custom Sql Generator

Register Custom Sql Generator

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Solution

Define Attribute

Register Attribute in Context

Custom Sql Generator

Register Custom Sql Generator

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related