Using SQL Server json properties with code first EF

Question

I want to store details about companies and the cars manufactured by them. Ideally this would include 2 tables: Companies and Cars with a foreign key reference.

But instead I want to use the JSON and NoSQL features of SQL Server 2016. The table structure would be a table Companies and a JSON column in the table with all the information about cars.

To achieve this through code first EF, here are the classes I have created:

public class Company
{
    public int ID { get; set; }
    public string Name { get; set; }
    public DateTime dtEstablished { get; set; }
    public List<Car> CarsManufactured { get; set; }
}

and

public class Car
{
    [Key]
    public string Name { get; set; }
    public string Model { get; set; }
    public DateTime MfgDate { get; set; }
    public string Type { get; set; }
}

and added this property to the ApplicationDbContext class

public DbSet<Company> Companies { get; set; }

Then after applying migration, instead of what I wanted, there are 2 tables in the database with foreign key reference just as discussed.

David Browne - Microsoft · Accepted Answer · 2017-07-22 21:16:04Z

This is the same as using XML columns with EF. You have to serialize/deserialize objects from the column yourself.

see

The Entity Framework does not support a native-XML data type. This means that when an entity is mapped to a table with an XML column, the equivalent entity property for the XML column is a string. Objects can be disconnected and serialized as XML. For more information, see Serializing Objects.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/migration-considerations

There are lots of examples out there of using XML columns with EF, but you'll basically need something like:

using Newtonsoft.Json;
using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.IO;
using System.Linq;

namespace ConsoleApp6
{

    public class Company
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public DateTime dtEstablished { get; set; }

        [NotMapped]
        public Car[] CarsManufactured
        {
            get
            {
                var ser = new JsonSerializer();
                var jr = new JsonTextReader(new StringReader(CarsManufacturedJSON));

                return ser.Deserialize<Car[]>(jr);
            }
            set
            {
                var ser = new JsonSerializer();
                var sw = new StringWriter();
                ser.Serialize(sw,value);
                CarsManufacturedJSON = sw.ToString();
            }
        }

        [Column("CarsManufactured")]
        public string CarsManufacturedJSON { get; set; }


    }

    public class Car
    {

        public string Name { get; set; }
        public string Model { get; set; }
        public DateTime MfgDate { get; set; }
        public string Type { get; set; }
    }
    class Db: DbContext
    {
        public DbSet<Company> Companies { get; set; }

    }
    class Program
    {

        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {
                db.Database.Log = m => Console.WriteLine(m);

                var company = db.Companies.Create();
                company.Name = "Acme";
                company.dtEstablished = new DateTime(2000, 2, 2);

                var cars = new Car[5];
                for (int i = 0; i<cars.Length; i++)
                {
                    var c = new Car()
                    {
                        MfgDate = new DateTime(2010 + i, 1, 1),
                        Model = $"Model{i}",
                        Name = $"ModelName{i}",
                        Type = $"Type{i}"

                    };
                    cars[i] = c;
                }

                company.CarsManufactured = cars;

                db.Companies.Add(company);
                db.SaveChanges();

            }
            using (var db = new Db())
            {
                var company = db.Companies.First();
                Console.WriteLine(company.CarsManufacturedJSON);

            }
            Console.ReadKey();

        }
    }
}

Collectives™ on Stack Overflow

Using SQL Server json properties with code first EF

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related