Requirement: Need to save an object collection in Sql Server Xml data type column.
Note: In Sql Server 2016 JSON support was added.
Steps:
1. Employee and Address classes declared.
2. Created EmployeeMapping of type EntityTypeConfiguration which is used by DbContext.
Note: Addresses collection should be ignored in mapping to avoid key required error on Address Entity.
Note: it’s not full proof code.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| namespace EntityFrameworkXmlDemo | |
| { | |
| internal class Program | |
| { | |
| public static void Main(string[] args) | |
| { | |
| Console.WriteLine("Welcome"); | |
| bool retrieve = true; | |
| var context = new SampleContext("connection string here"); | |
| if (retrieve) | |
| { | |
| var employee = context.Employees.FirstOrDefault(e => e.Id == 1); | |
| var addresses = employee.Addresses; | |
| } | |
| else | |
| { | |
| var employee = new Employee() | |
| { | |
| Name = "Fanstastic" | |
| }; | |
| employee.Addresses.Add(new Address() | |
| { | |
| Name = "Newyork", | |
| Zip = 1234 | |
| }); | |
| employee.Addresses.Add(new Address() | |
| { | |
| Name = "California", | |
| Zip = 7658 | |
| }); | |
| employee.SerializeAddress(); | |
| context.Employees.Add(employee); | |
| context.SaveChanges(); | |
| } | |
| Console.ReadKey(); | |
| } | |
| } | |
| public class SampleContext : DbContext | |
| { | |
| public DbSet<Employee> Employees { get; set; } | |
| public SampleContext(string connectionString) : base(connectionString) | |
| { | |
| } | |
| protected override void OnModelCreating(DbModelBuilder modelBuilder) | |
| { | |
| modelBuilder.Configurations.Add(new EmployeeMapping()); | |
| base.OnModelCreating(modelBuilder); | |
| } | |
| } | |
| public class EmployeeMapping : EntityTypeConfiguration<Employee> | |
| { | |
| public EmployeeMapping() | |
| { | |
| this.ToTable("Employees"); | |
| this.HasKey(d => d.Id); | |
| this.Property(d => d.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); | |
| this.Property(d => d.Name); | |
| this.Property(d => d.Address).HasColumnType("xml"); | |
| this.Ignore(d => d.Addresses); | |
| } | |
| } | |
| public class Employee | |
| { | |
| private IList<Address> _addresses; | |
| public int Id { get; set; } | |
| public string Name { get; set; } | |
| public string Address { get; set; } | |
| public IList<Address> Addresses | |
| { | |
| get | |
| { | |
| _addresses = DeSerializeAddress(); | |
| return _addresses; | |
| } | |
| set => _addresses = value; | |
| } | |
| public void SerializeAddress() | |
| { | |
| var serializer = new XmlSerializer(typeof(List<Address>)); | |
| var stringWriter = new StringWriter(); | |
| serializer.Serialize(stringWriter, _addresses); | |
| Address = stringWriter.ToString(); | |
| } | |
| public IList<Address> DeSerializeAddress() | |
| { | |
| if (!string.IsNullOrEmpty(Address)) | |
| { | |
| var serializer = new XmlSerializer(typeof(List<Address>)); | |
| var stringReader = new StringReader(Address); | |
| return (IList<Address>)serializer.Deserialize(stringReader); | |
| } | |
| return _addresses; | |
| } | |
| public Employee() | |
| { | |
| _addresses = new List<Address>(); | |
| } | |
| } | |
| public class Address | |
| { | |
| public string Name { get; set; } | |
| public int Zip { get; set; } | |
| } | |
| } |
giridharprakash.me 