Поиск  
Always will be ready notify the world about expectations as easy as possible: job change page
Jan 10

How to query JSON in C# using SQL, LINQ, ADO.NET

Автор:
Источник:
Просмотров:
4921

Charts

In today’s connected world, data is piling up very fast. We are generating more data than ever in human history, and a lot of this data is getting stored in non-relational formats like JSON documents. JSON has become a ubiquitous format for modern-day applications — being used for config files, data transfer between APIs, client & server, data storage, etc. Given its simplicity, working with JSON data cannot be anything but simple.

In this blog, we will look at ComponentOne DataConnector for JSON, which offers an effortless way to connect to JSON data using ADO.NET or Entity Framework Core. We’ll discuss using the C1 ADO. NET provider for JSON to query JSON data using SQL and LINQ.

How to Query JSON Using SQL

In this process, we will be following the same approach that we generally use while working with databases, i.e.:

  1. Creating a Connection String
  2. Creating a Connection Object
  3. Querying data using SQL

But first, let’s start by installing the required NuGet package.

  • Open the Project menu and select Manage NuGet Packages.
  • Browse for ‘C1. AdoNet. Json’ NuGet package and click install.

Creating a Connection String

In order to connect with our data source, we would need a connection string. We will be using C1JsonConnectionStringBuilder to create our connection string. Creating a connection string requires three items:

  1. JSON DataModel: It specifies the access mechanism that is used for reading the JSON data. Specifying the DataModel, let’s you configure how object arrays are modeled as tables. The different data models available are — the Top Level Document Model, Flattened Model, and Relational Model. You can read more about them here.
  2. Uri: Specifies the URI for the JSON resource location. You can connect to local files or HTTP streams.
  3. JSON Path: Specifies which node to read from the JSON content

The following shows an example of creating a connection string for flat data:

public static class Utils
{
    public static string JsonDataPath => "Data\\EmployeesData.json";
    public static C1JsonConnectionStringBuilder JsonConnectionBuilder { get; }

    static Utils()
    {
        JsonConnectionBuilder = new C1JsonConnectionStringBuilder()
        {
            DataModel = "Document",
            Uri = JsonDataPath,
            JsonPath = "$.employees"
        };
    }
}

Similarly, the following shows creating a connection string for relational data:

public static class Utils
{
    public static string JsonCustomerDataPath => "Data\\CustomersData.json";
    public static C1JsonConnectionStringBuilder CustomerDataConnectionStringBuilder { get; }

    static Utils()
    {
        CustomerDataConnectionStringBuilder = new C1JsonConnectionStringBuilder()
        {
            DataModel = "Relational",
            Uri = JsonCustomerDataPath,
            JsonPath = "$.customers;$.customers.Transactions"
        };
    }
}

To learn more about the connection string format, please refer here.

How to Create a Connection Object

Now, we’ve got our connection string, so the next step is to initialize a connection instance using C1JsonConnection class as follows:

private void CreateJsonConnection()
{
    _jsonConnection = new C1JsonConnection(_connBuilder.ConnectionString);
}

Querying Data Using SQL

Now that the connection is ready, we can query data using SQL by creating an object of the C1JsonDataAdapter as follows:

C1JsonDataAdapter adapter = new C1JsonDataAdapter(_jsonConnection, sql);
var table = new DataTable();
adapter.Fill(table);

The following shows a sample flat data and the corresponding query for the same:

JSON data

C1JsonDataAdapter adapter = new C1JsonDataAdapter(_jsonConnection, “select * from employees”);
var table = new DataTable();
adapter.Fill(table);

With relational data it may look something like this:

JSON data

C1JsonDataAdapter adapter = new C1JsonDataAdapter(_jsonConnection, (“Select FirstName, LastName, Amount from customers INNER JOIN Transactions ON customers._id = Transactions.customers_id where Transactions.IsSuccess=false”);
var table = new DataTable();
adapter.Fill(table);

Once the DataTable gets populated, the data can be displayed in a grid as shown below:

DataTable

Querying JSON using LINQ in Entity Framework Core

In this section, we’ll discuss how we can query JSON data using LINQ in Entity Framework Core. Let’s begin by installing the required NuGet package i.e. C1.EntityFrameworkCore.Json.

Once the NuGet package is installed, next we need to set the DbContext and provide the connection string to the JSON data source. We’ll use C1JsonConnectionStringBuilder for this purpose, as shown below:

public abstract partial class DocumentContext : DbContext
{
    private C1JsonConnectionStringBuilder _builder;

    public DocumentContext(C1JsonConnectionStringBuilder builder)
    {
        _builder = builder;
        Database.AutoTransactionsEnabled = false;
    }

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseJson(_builder.ConnectionString);
        }
    }
}

How to Create the DbContext

The above DocumentContext class can serve as the base class for DbContext of our JSON documents. The model entities can be defined in the child classes.

The following shows an example of DbContext for a flat JSON data source:

public class EmployeesContext : DocumentContext
{
    public virtual DbSet<Employee> Employees { get; set; }

    public EmployeesContext() : base(Utils.JsonConnectionBuilder) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>(entity =>
        {
            entity.ToTable("employees");
            entity.HasKey(x => x.Id);
            entity.Property(e => e.Id).HasColumnName("Id");
            entity.Property(e => e.FirstName).HasColumnName("FirstName");
            entity.Property(e => e.LastName).HasColumnName("LastName");
            entity.Property(e => e.Email).HasColumnName("Email");
            entity.Property(e => e.DOB).HasColumnName("DOB");
            entity.Property(e => e.Address).HasColumnName("Address");
            entity.Property(e => e.State).HasColumnName("State");
            entity.Property(e => e.Company).HasColumnName("Company");
            entity.Property(e => e.Gender).HasColumnName("Gender");
            entity.Property(e => e.JobTitle).HasColumnName("JobTitle");
            entity.Property(e => e.Skill).HasColumnName("Skill");
            entity.Property(e => e.Salary).HasColumnName("Salary");
        });
    }
}

The following shows an example of DbContext for a relational JSON data source:

public class CustomersContext : DocumentContext
{
    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Transaction> Transactions { get; set; }

    public CustomersContext() : base(Utils.CustomerDataConnectionStringBuilder) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Transaction>(entity =>
        {
            entity.ToTable("Transactions");
            entity.HasOne(e => e.Customer).WithMany(x => x.Transactions).HasForeignKey("customers_id");
            entity.Property(e => e.Id).HasColumnName("_id");
            entity.Property(e => e.Amount).HasColumnName("Amount");
            entity.Property(e => e.Credited).HasColumnName("Credited");
            entity.Property(e => e.IsSuccess).HasColumnName("IsSuccess");
            entity.Property(e => e.Date).HasColumnName("Date");
        });

        modelBuilder.Entity<Customer>(entity =>
        {
            entity.ToTable("customers");
            entity.Property(e => e.Id).HasColumnName("_id");
            entity.Property(e => e.FirstName).HasColumnName("FirstName");
            entity.Property(e => e.LastName).HasColumnName("LastName");
            entity.Property(e => e.DOB).HasColumnName("DOB");
            entity.Property(e => e.State).HasColumnName("State");
            entity.Property(e => e.Gender).HasColumnName("Gender");
        });
    }
}

With the ComponentOne DataConnectors scaffolding feature, these classes can be generated automatically. It’s generated by running the Scaffold-DbContext command. See the scaffolding topic in our documentation for details.

Querying the DbContext Using LINQ

Now that the DbContext are set, the JSON datasource can be queried using LINQ. We can use both Query syntax or the Method syntax:

_context = new EmployeesContext();
var employees = await _context.Employees.ToListAsync();

// Employee count of companies starting with 'A'
var employeeCountData = from employee in employees
                        where employee.Company.StartsWith("A", StringComparison.OrdinalIgnoreCase)
                        group employee by employee.Company into grp
                        select new { Company = grp.Key, NumberOfEmployees = grp.Count() };

// Salary growth of people working in Dynabox
var salaryGrowthData = employees
         .Where(x => x.Company == "Dynabox").OrderBy(x => x.DOB)
         .Select(x => new { Age = DateTime.Now.Year - x.DOB.Year, Salary = x.Salary })
         .Where(x => x.Age >= 18)
         .GroupBy(x => x.Age)
         .Select(x => new { Age = x.Key, AverageSalary = x.Select(o => o.Salary).Average() });

The data can then be used in a grid/chart as shown below:

Grid and chart

Conclusion

In this blog, we saw querying flat/relational JSON data using the C1 ADO. NET provider for JSON. While this blog shows the basic usage of the ADO. NET provider for JSON, this library also provides advanced features like as Authorization, Caching, Connection Pooling, etc. Read more from the product documentation here.

Please feel free to ask if you have any questions.

Read Documentation | Download Sample

• • •

Originally published at https://developer.mescius.com on October 19, 2023.

Похожее
17 апреля
Рассмотрим интересную задачу по разработке игры «Крестики Нолики» на языке C#. Наш проект будет запускаться в консоли и потребует креативное мышление для решения задачи. Ваша задача — реализовать консольную игру "крестики-нолики" с использованием языка программирования C#. Вам нужно создать игровое поле,...
Jan 13
Author: Weerayut Teja
In the world of web development, building robust and efficient REST APIs is a crucial skill. Whether you are a C# beginner or have some programming experience, creating RESTful APIs using .NET Core and Entity Framework with PostgreSQL is an...
Mar 22
Author: Dayanand Thombare
LINQ (Language Integrated Query) has revolutionized the way we interact with data in C#. It offers a consistent, readable, and concise way to manipulate collections, databases, XML, and more. However, the beauty and ease of LINQ can sometimes mask performance...
Apr 24, 2022
Author: HungryWolf
What is MediatR? Why do we need it? And How to use it?Mediator Pattern - The mediator pattern ensures that objects do not interact directly instead of through a mediator. It reduces coupling between objects which makes it easy to...
Написать сообщение
Почта
Имя
*Сообщение


© 1999–2024 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET Framework | .NET Core | C# | ASP.NET | Windows Forms | WPF | HTML5 | CSS3 | jQuery | AJAX | Angular | React | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban