y

Understanding RDBMS and Working with Entity Framework in ASP.NET Core

Relational Database Management Systems (RDBMS) are foundational to modern application development. They provide a structured way to store, retrieve, and manage data. Entity Framework (EF) is an Object-Relational Mapper (ORM) that simplifies data access in ASP.NET Core applications by allowing developers to work with data using .NET objects. In this article, we’ll explore the principles of RDBMS and demonstrate how to use Entity Framework to map objects to database fields in ASP.NET Core.

What is an RDBMS?

A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured format using rows and columns. This structure allows for the efficient querying and manipulation of data using Structured Query Language (SQL). Examples of popular RDBMS include Microsoft SQL Server, MySQL, PostgreSQL, and Oracle Database.

Key Features of RDBMS:

• Tables: Data is organized into tables, where each table consists of rows and columns.
• Relationships: Tables can be related to each other through foreign keys, enabling complex queries and data integrity.
• Transactions: RDBMS support transactions, ensuring that a series of operations either complete successfully or fail together, maintaining data consistency.
• Normalization: The process of organizing data to reduce redundancy and improve data integrity.

Entity Framework Overview

Entity Framework (EF) is an ORM that enables .NET developers to work with a database using .NET objects. EF abstracts the underlying database and allows developers to interact with it using LINQ (Language Integrated Query), eliminating the need for most of the boilerplate SQL code.

Advantages of Using Entity Framework:

• Productivity: Reduces the amount of data access code developers need to write.
• Maintainability: Easier to maintain and evolve applications by working with high-level abstractions.
• Compatibility: Supports multiple database providers, allowing for flexibility in database choice.

Setting Up Entity Framework in ASP.NET Core

Install Entity Framework Core: Start by adding the EF Core package to your ASP.NET Core project for each RDBMS you plan to use. You can do this via the NuGet Package Manager or the .NET CLI:

For SQL Server:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

For PostgreSQL:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

For Oracle:

dotnet add package Oracle.EntityFrameworkCore

Using the Options Pattern for Configuration: The options pattern in ASP.NET Core is a preferred way to manage settings. This pattern can be used to retrieve the connection string.

Add a Configuration Class:

public class DatabaseSettings
{
    public string ConnectionString { get; set; }
}

Configure the Settings in appsettings.json:

{
    "DatabaseSettings": {
        "ConnectionString": "YourConnectionStringHere"
    }
}

Register the Configuration in Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    services.Configure<DatabaseSettings>(Configuration.GetSection("DatabaseSettings"));

    services.AddDbContext<AppDbContext>((serviceProvider, options) =>
    {
        var dbSettings = serviceProvider.GetRequiredService<IOptions<DatabaseSettings>>().Value;
        options.UseSqlServer(dbSettings.ConnectionString);
        // Use Npgsql for PostgreSQL
        // options.UseNpgsql(dbSettings.ConnectionString);
        // Use Oracle for Oracle Database
        // options.UseOracle(dbSettings.ConnectionString);
    });

    services.AddControllersWithViews();
}

Define Your Model Classes with Data Annotations:

Model classes represent the tables in your database. Each property in a model class corresponds to a column in the table. Data Annotations are attributes you can apply to your model classes and properties to configure the mappings.

public class Product
{
    [Key]
    public int Id { get; set; }

    [Required]
    [MaxLength(100)]
    public string Name { get; set; }

    [Column(TypeName = "decimal(18,2)")]
    public decimal Price { get; set; }
}

public class Order
{
    [Key]
    public int Id { get; set; }

    [Required]
    public DateTime OrderDate { get; set; }

    [Required]
    public int Quantity { get; set; }

    [Column(TypeName = "decimal(18,2)")]
    public decimal TotalPrice { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

Create a Database Context:

The DbContext class is the bridge between your application and the database. It manages the entity objects during runtime, including querying, saving, and updating data. For flexibility, the connection string can be passed through configuration.

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name).IsRequired().HasMaxLength(100);
            entity.Property(e => e.Price).HasColumnType("decimal(18,2)");
        });

        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.OrderDate).IsRequired();
            entity.Property(e => e.Quantity).IsRequired();
            entity.Property(e => e.TotalPrice).HasColumnType("decimal(18,2)");

            entity.HasMany(e => e.Products)
                  .WithMany();
        });
    }
}

Performing CRUD Operations

Once your models and DbContext are set up, you can perform CRUD (Create, Read, Update, Delete) operations using EF Core. It’s important to use the using statement to ensure that the DbContext is properly disposed of after use. This ensures that database connections are released and resources are cleaned up, preventing potential memory leaks and other issues.

Create:

using (var context = new AppDbContext(options))
{
    var product1 = new Product { Name = "Laptop", Price = 1200m };
    var product2 = new Product { Name = "Mouse", Price = 20m };
    context.Products.AddRange(product1, product2);
    context.SaveChanges();

    var order = new Order 
    { 
        OrderDate = DateTime.Now, 
        Quantity = 2, 
        TotalPrice = 1220m,
        Products = new List<Product> { product1, product2 }
    };
    context.Orders.Add(order);
    context.SaveChanges();
}

Read:

using (var context = new AppDbContext(options))
{
    var orders = context.Orders
                        .Include(o => o.Products)
                        .ToList();
    foreach (var order in orders)
    {
        Console.WriteLine($"Order {order.Id}:");
        foreach (var product in order.Products)
        {
            Console.WriteLine($"- {product.Name} - {product.Price}");
        }
        Console.WriteLine($"Total: {order.TotalPrice}");
    }
}

Update:

using (var context = new AppDbContext(options))
{
    var order = context.Orders.Include(o => o.Products).FirstOrDefault(o => o.Id == 1);
    if (order != null)
    {
        var additionalProduct = new Product { Name = "Keyboard", Price = 50m };
        context.Products.Add(additionalProduct);
        order.Products.Add(additionalProduct);
        order.Quantity += 1;
        order.TotalPrice += additionalProduct.Price;
        context.SaveChanges();
    }
}

Delete:

using (var context = new AppDbContext(options))
{
    var order = context.Orders.Include(o => o.Products).FirstOrDefault(o => o.Id == 1);
    if (order != null)
    {
        context.Orders.Remove(order);
        context.SaveChanges();
    }
}

Why Use using with DbContext?

The using statement ensures that the DbContext object is disposed of correctly after use. When the code execution leaves the using block, the Dispose method of the DbContext is automatically called. This method releases all the resources used by the DbContext, such as open database connections. Properly disposing of DbContext instances is crucial for resource management and performance, especially in web applications where database connections are limited.

Querying Data with LINQ

Entity Framework allows querying data using LINQ (Language Integrated Query), which provides a more readable and concise syntax compared to raw SQL.

Example Queries:

• Simple Query:

using (var context = new AppDbContext(options))
{
    var products = from p in context.Products
                   where p.Price > 100
                   select p;

    foreach (var product in products)
    {
        Console.WriteLine($"{product.Id} - {product.Name} - {product.Price}");
    }
}

Join Query:

using (var context = new AppDbContext(options))
{
    var orders = from o in context.Orders
                 join p in context.Products on o.Products.FirstOrDefault().Id equals p.Id
                 select new { o.Id, p.Name, o.Quantity, o.TotalPrice };

    foreach (var order in orders)
    {
        Console.WriteLine($"Order {order.Id}: {order.Name} x {order.Quantity} - {order.TotalPrice}");
    }
}

Inspecting and Managing Your Database

To inspect and manage your database, you can use tools like SQL Server Management Studio (SSMS) for SQL Server, pgAdmin for PostgreSQL, or Oracle SQL Developer for Oracle Database. These tools allow you to run SQL queries, view table structures, and manage database objects.

Conclusion

Entity Framework Core provides a powerful and flexible way to interact with relational databases in ASP.NET Core applications. By understanding how to map your objects to database fields and using a well-structured DbContext, you can leverage the full potential of EF Core to build robust and maintainable applications. Whether you’re using SQL Server, PostgreSQL, or Oracle, EF Core simplifies the complexities of data access, allowing you to focus on your application’s business logic. Properly managing DbContext instances and utilizing the using statement ensures efficient resource management and optimal performance in your applications.