Asp.Net Core Linq Queries And Raw Sql Complete Guide

Overview:

• LINQ (Language Integrated Query): Allows you to query data in a concise and readable syntax.
• Raw SQL: Useful when you need to write queries manually or when LINQ doesn't satisfy specific performance or functional requirements.

Setup:

For both examples, we'll need an ASP.NET Core Web API project set up with Entity Framework Core.

Step 1 - Create an ASP.NET Core Web API Project:

dotnet new webapi -n AspNetCoreLinqRawSqlDemo
cd AspNetCoreLinqRawSqlDemo

Step 2 - Install EF Core Packages:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

Step 3 - Create a Model and DbContext:

Model.cs

namespace AspNetCoreLinqRawSqlDemo.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public int CategoryId { get; set; }
        public Category Category { get; set; } // Navigation Property
    }

    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Product> Products { get; set; } // Navigation Property
    }
}

AppDbContext.cs

using Microsoft.EntityFrameworkCore;
using AspNetCoreLinqRawSqlDemo.Models;

namespace AspNetCoreLinqRawSqlDemo.Data
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}

        public DbSet<Product> Products { get; set; }
        public DbSet<Category> Categories { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Category>()
                .HasData(new Category { Id = 1, Name = "Electronics" },
                        new Category { Id = 2, Name = "Books" });

            modelBuilder.Entity<Product>()
                .HasData(new Product { Id = 1, Name = "Laptop", Price = 999.99m, CategoryId = 1 },
                        new Product { Id = 2, Name = "Smartphone", Price = 499.99m, CategoryId = 1 },
                        new Product { Id = 3, Name = "Book1", Price = 12.99m, CategoryId = 2 },
                        new Product { Id = 4, Name = "Book2", Price = 15.99m, CategoryId = 2 });
        }
    }
}

Step 4 - Configure Your Database Connection String:

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\LocalDB;Database=AspnetCoreLinqRawSqlDemoDb;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Program.cs

Make sure your Program.cs sets up the DbContext:

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

string connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();

Example 1: Using LINQ Queries

Let's create a simple Controller that will use LINQ to fetch products from a database.

ProductsController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using AspNetCoreLinqRawSqlDemo.Data;
using AspNetCoreLinqRawSqlDemo.Models;

[ApiController]
[Route("[controller]")]
public class ProductsController : ControllerBase
{
    private readonly AppDbContext _context;

    public ProductsController(AppDbContext context)
    {
        _context = context;
    }

    [HttpGet("all")]
    public async Task<ActionResult<IEnumerable<Product>>> GetAllProducts()
    {
        var products = await _context.Products.Include(p => p.Category).ToListAsync();
        return Ok(products);
    }

    [HttpGet("Cheap")]
    public async Task<ActionResult<IEnumerable<Product>>> GetCheapProducts()
    {
        var cheapProducts = await _context.Products
                                         .Where(p => p.Price < 100)
                                         .Include(p => p.Category)
                                         .ToListAsync();
        return Ok(cheapProducts);
    }

    [HttpGet("bycategory/{categoryId}")]
    public async Task<ActionResult<IEnumerable<Product>>> GetProductsByCategory(int categoryId)
    {
        var productsInCategory = await _context.Products
                                               .Include(p => p.Category)
                                               .Where(p => p.CategoryId == categoryId)
                                               .ToListAsync();
        return Ok(productsInCategory);
    }
}

Example 2: Using Raw SQL

Next, let’s demonstrate fetching products using raw SQL queries.

ProductsController.cs

Add raw SQL methods to the existing ProductsController.

[HttpGet("rawall")]
public async Task<ActionResult<IEnumerable<Product>>> GetAllProductsRaw()
{
    var products = await _context.Products
                                 .FromSqlRaw("SELECT * FROM Products")
                                 .Include(p => p.Category)
                                 .ToListAsync();
    return Ok(products);
}

[HttpGet("rawcheap")]
public async Task<ActionResult<IEnumerable<Product>>> GetCheapProductsRaw()
{
    var cheapProducts = await _context.Products
                                       .FromSqlInterpolated($"SELECT * FROM Products WHERE Price < 100")
                                       .Include(p => p.Category)
                                       .ToListAsync();
    return Ok(cheapProducts);
}

[HttpGet("rawbycategory/{categoryId}")]
public async Task<ActionResult<IEnumerable<Product>>> GetProductsByCategoryRaw(int categoryId)
{
    var productsInCategory = await _context.Products
                                           .FromSqlInterpolated($"SELECT * FROM Products WHERE CategoryId = {categoryId}")
                                           .Include(p => p.Category)
                                           .ToListAsync();
    return Ok(productsInCategory);
}

Summary of Steps:

1. Create the model classes (Product and Category).
2. Create a DbContext (AppDbContext).
3. Configure the connection string in appsettings.json.
4. Register the DbContext with DI in Program.cs.
5. Create a controller (ProductsController) with actions using LINQ and raw SQL.

Testing:

After running the application, you can use tools like Postman or Swagger UI to test these endpoints:

• GET /products/all: Fetches all products including their categories using LINQ.
• GET /products/cheap: Fetches all products below a certain price using LINQ.
• GET /products/bycategory/{categoryId}: Fetches all products in a specific category using LINQ.
• GET /products/rawall: Fetches all products using raw SQL.
• GET /products/rawcheap: Fetches all products below a certain price using raw SQL.
• GET /products/rawbycategory/{categoryId}: Fetches all products in a specific category using raw SQL.