ASP.NET Core LINQ Queries and Raw SQL: An In-Depth Guide for Beginners

Introduction

When developing web applications using ASP.NET Core, developers often require a powerful way to interact with databases. Entity Framework Core (EF Core), which is the current version of Microsoft’s object-relational mapper (ORM), simplifies database operations in .NET applications. One of the key features of EF Core is the ability to perform database queries using both Language Integrated Query (LINQ) and raw SQL. This guide delves into the intricacies of both LINQ queries and raw SQL execution within ASP.NET Core applications, offering a comprehensive view for beginners.

Step 1: Setting Up EF Core with ASP.NET Core

Before diving into LINQ queries and raw SQL, it's essential to understand how to set up EF Core in an ASP.NET Core application.

• Install Entity Framework Core NuGet Packages: Begin by adding EF Core packages to your project. Open your project in Visual Studio, and then navigate to the NuGet Package Manager, where you can install the Microsoft.EntityFrameworkCore.SqlServer (for SQL Server databases) and Microsoft.EntityFrameworkCore.Tools (for scaffolding and migrations) packages.

  Install-Package Microsoft.EntityFrameworkCore.SqlServer
  Install-Package Microsoft.EntityFrameworkCore.Tools
  

• Configure Database Context: Define a database context class that inherits from DbContext. This class serves as a bridge between your domain models and the database.

  using Microsoft.EntityFrameworkCore;
  
  public class AppDbContext : DbContext
  {
      public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}
  
      public DbSet<Product> Products { get; set; }
      public DbSet<Category> Categories { get; set; }
  }
  

• Configure Services in Startup.cs: Register the database context as a service in your Startup.cs file so that it can be injected wherever needed.

  public void ConfigureServices(IServiceCollection services)
  {
      services.AddDbContext<AppDbContext>(options =>
          options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
      services.AddControllersWithViews();
  }
  

Step 2: Understanding LINQ Queries

Language Integrated Query (LINQ) provides a simple syntax for querying data from various data sources such as SQL databases, XML files, and collections. LINQ to Entities is an extension of LINQ that allows you to query a database using .NET language syntax.

Basic LINQ Query Structure

A typical LINQ query comprises three parts:

• From: Identifies the source sequence and the range variable.
• Where: Filters elements from the data source based on specified conditions.
• Select: Projects each element or a property of each element into a new form.

Consider a Product class mapped to a Products table in the database.

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

Querying with LINQ

• Retrieving All Products:

  var products = _context.Products;
  

• Retrieving Products with a Price Greater Than a Certain Amount:

  var filteredProducts = _context.Products
                                 .Where(p => p.Price > 100)
                                 .ToList();
  

• Selecting Specific Properties:

  var productNames = _context.Products
                              .Select(p => p.Name)
                              .ToList();
  

• Joining Tables:

  var productsWithCategory = _context.Products
                                     .Include(p => p.Category)
                                     .Select(p => new { p.Name, p.Category.Name })
                                     .ToList();
  

Complex Queries

• Ordering:

  To order results by a particular property:

  var sortedProducts = _context.Products
                              .Where(p => p.Price > 100)
                              .OrderBy(p => p.Name)
                              .ToList();
  

• Grouping:

  Grouping products by category:

  var groupedProducts = _context.Products
                                 .GroupBy(p => p.CategoryId)
                                 .Select(g => new { CategoryId = g.Key, Count = g.Count() })
                                 .ToList();
  

Deferred Loading vs. Eager Loading

• Deferred Loading: Queries are not executed until the data is actually enumerated. You can add more Where, Select, or other clauses without executing the query until it is necessary.

  var query = _context.Products.Where(p => p.Price > 100);
  

• Eager Loading: Related data is loaded from the database as part of the initial query execution. This is useful for reducing the number of queries made to the database.

  var productsWithCategories = _context.Products.Include(p => p.Category).ToList();
  

Step 3: Using Raw SQL Queries

While LINQ is convenient and powerful, there are scenarios where executing raw SQL might be more efficient or necessary. EF Core allows you to run raw SQL queries using the FromSqlRaw and ExecuteSqlRaw methods.

Executing Select Queries with Raw SQL

• Retrieving All Products:

  var products = _context.Products.FromSqlRaw("SELECT * FROM Products").ToList();
  

• Filtering Results:

  var filteredProducts = _context.Products
                                .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100)
                                .ToList();
  

• Using Parameters to Prevent SQL Injection:

  Raw SQL queries can be parameterized to prevent SQL injection. The {0}, {1}, etc., in the query string correspond to the parameters provided as additional arguments to FromSqlRaw.

  var filteredProducts = _context.Products
                                .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", priceParam)
                                .ToList();
  

Executing Non-Select Queries with Raw SQL

For queries that modify the database (inserts, updates, and deletes), use the ExecuteSqlRaw method.

• Inserting a New Product:

  var productName = "Sample Product";
  var productPrice = 250m;
  
  var rowsAffected = _context.Database.ExecuteSqlRaw(
      "INSERT INTO Products (Name, Price) VALUES ({0}, {1})",
      productName, productPrice);
  

• Updating an Existing Product:

  var productId = 1;
  var newPrice = 300m;
  
  var rowsAffected = _context.Database.ExecuteSqlRaw(
      "UPDATE Products SET Price = {0} WHERE ProductId = {1}",
      newPrice, productId);
  

Executing Stored Procedures

Stored procedures can also be executed using the same ExecuteSqlRaw method.

• Calling a Stored Procedure:

  var rowsAffected = _context.Database.ExecuteSqlRaw(
      "EXEC UpdateProductPrice @ProductId = {0}, @NewPrice = {1}",
      productId, newPrice);
  

Important Aspects of Raw SQL

• Concurrency Control: Ensure that your application handles concurrency correctly when executing raw SQL queries that modify data.

• Transaction Support: EF Core supports transactions which can be used to manage the execution of multiple raw SQL commands within a single unit of work.

  using (var transaction = _context.Database.BeginTransaction())
  {
      try
      {
          _context.Database.ExecuteSqlRaw("...");
          _context.Database.ExecuteSqlRaw("...");
          transaction.Commit();
      }
      catch (Exception)
      {
          transaction.Rollback();
          throw;
      }
  }
  

Step 4: Best Practices for LINQ and Raw SQL in ASP.NET Core

• Use LINQ for Common Queries: LINQ offers a strong-typed, type-safe, and maintainable way to query data that integrates seamlessly with C#.

• Fallback to Raw SQL for Complex Queries: In certain cases, raw SQL provides more control and optimization opportunities. However, use it judiciously to avoid complex and error-prone code.

• Avoid SQL Injection: Always use parameterized queries whether you are using LINQ or raw SQL. EF Core handles this for LINQ queries by default, but you must be cautious with raw SQL.

• Optimize Queries: Analyze execution plans and indexes to ensure that your queries perform efficiently. EF Core provides tools for generating these plans.

• Handle Exceptions Gracefully: Implement proper exception handling to catch and log database errors, improving the robustness of your application.

• Consider Readability: Write code that is easy to read and understand, which enhances maintainability and collaboration among team members.

Conclusion

Mastering LINQ queries and raw SQL execution in ASP.NET Core opens doors to efficient and powerful database interactions. By leveraging LINQ's strong typing and type safety, you can write maintainable code that is easy to understand and modify. However, when faced with more complex queries or scenarios, raw SQL provides the flexibility and control needed to maximize performance. With a solid understanding of both approaches, ASP.NET Core developers can craft robust applications that seamlessly integrate database interactions.

By following the setup, understanding of basic and complex LINQ queries, executing raw SQL queries, and adhering to best practices, beginners can confidently handle database operations within their .NET applications.