DataConnector | ComponentOne
ADO.NET provider for JSON / Entity Framework
In This Topic
    Entity Framework
    In This Topic

    The ADO.NET Provider for JSON supports Entity Framework which requires the C1.EntityFrameworkCore.Json package to be installed. This article demonstrates the model-first approach to building an Entity Framework model that maps data tables to classes for simpler access to JSON.

    The following code defines the Books class to map the Books datatable.

    C#
    Copy Code 
    public partial class Books
    {
        public string Id { get; set; }
        public string Genre { get; set; }
        public DateTime? Publicationdate { get; set; }
        public string Isbn { get; set; }
        public string Title { get; set; }
        public string AuthorFirstName { get; set; }
        public string AuthorLastName { get; set; }
        public double? Price { get; set; }
        public string Readers { get; set; }
    }

    The next code example defines DocumentContext class which is used to access the Books datatable and establish a connection to the JSON service by overriding the OnConfiguring method. This method invokes the UseJson method of the DbContextOptionsBuilder class to configure the context and establish a connection with the JSON service.

    C#
    Copy Code 
    public partial class DocumentContext : DbContext
{
    public DocumentContext()
    {
        Database.AutoTransactionsEnabled = false;
    }

    public DocumentContext(DbContextOptions<DocumentContext> options)
        : base(options)
    {
        Database.AutoTransactionsEnabled = false;
    }

    public virtual DbSet<Books> Books { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseJson("Data Model=Document;Uri='json_bookstore.json';Json Path='$.bookstore.books';");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Books>(entity =>
        {
            entity.HasNoKey();
            entity.ToTable("books");
            entity.Property(e => e.AuthorFirstName).HasColumnName("author.first-name");
            entity.Property(e => e.AuthorLastName).HasColumnName("author.last-name");
            entity.Property(e => e.Genre).HasColumnName("genre");
            entity.Property(e => e.Id).HasColumnName("_id");
            entity.Property(e => e.Isbn).HasColumnName("ISBN");
            entity.Property(e => e.Price).HasColumnName("price");
            entity.Property(e => e.Publicationdate).HasColumnName("publicationdate");
            entity.Property(e => e.Readers).HasColumnName("readers");
            entity.Property(e => e.Title).HasColumnName("title");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

    You can now use the LINQ queries to perform different data operations to the mapped classes as demonstrated in the code below. Note that "using System.Linq" must be declared in your code.

    C#
    Copy Code 
    static void SelectDocument() 
{
    Console.WriteLine("Query all Books...");
    
    using(var db = new DocumentContext()) 
    {
        var histories = from p in db.Books select p;
        foreach(var h in histories) 
        {
        Console.WriteLine($"{h.AuthorFirstName} - {h.AuthorLastName} - {h.Isbn} - {h.Price} - {h.Title} - {h.Readers} -");
        }
    }
}

static void CRUDJsonFile() 
{
    Console.WriteLine("\nCUD Json file...");
    
    using(var context = new RelationalContext()) 
    {
        var book = new Books();
        book.Id = "1";
        book.Title = "Test Insert EFCore";
        book.Price = 400;
        book.Isbn = "1";
        book.Publicationdate = new DateTime(2021, 10, 15);
        context.Books.Add(book);
        int result = context.SaveChanges();
        Console.WriteLine("Number of row inserted: " + result);
    }

    using(var context = new RelationalContext()) 
    {
        var book = context.Books.Where(x => x.Title.Equals("Test Insert EFCore")).FirstOrDefault();
        if (book != null) 
        {
            book.Title = "Test Update EFCore";
            int result = context.SaveChanges();
            Console.WriteLine("Number of row updated: " + result);
        }
    }

    using(var context = new RelationalContext()) 
    {
        context.Books.Remove(context.Books.Where(x => x.Title.Equals("Test Update EFCore")).FirstOrDefault());
        var result = context.SaveChanges();
        Console.WriteLine("Number of row deleted: " + result);
    }
}
    Note: The Scaffolding feature supports the user to create the model and dbcontext when you create a model in the Entity Framework for all dataconnectors.