Entity Framework

Entity Framework (EF)

Entity Framework (EF) is a powerful, open-source Object-Relational Mapper (ORM) from Microsoft that enables .NET developers to work with a database using C# objects (domain-specific objects) instead of writing most of the traditional data-access code or raw SQL queries.

Key Concepts and Benefits

Abstraction: EF abstracts the underlying database tables and columns, allowing developers to focus on the business logic and entity relationships within their C# code.

LINQ Integration: It primarily uses Language-Integrated Query (LINQ) to query data using strongly typed C# syntax. EF Core translates these LINQ queries into database-specific SQL queries (e.g., for SQL Server, MySQL, PostgreSQL, SQLite).

Change Tracking: EF automatically tracks changes made to your C# objects and generates the appropriate INSERT, UPDATE, and DELETE commands when you call the SaveChanges() method on the database context.

Migrations: EF provides a migrations feature that allows developers to evolve the database schema over time as their C# model changes, using commands like Add-Migration and Update-Database.

EF Core vs. EF 6

The modern, cross-platform version is Entity Framework Core (EF Core), which is recommended for all new applications. The older Entity Framework 6 (EF6) is the classic version and is still supported for security fixes but is no longer actively developed with new features.

Approaches to Data Modeling

EF primarily supports two main development approaches:

• Code-First: This is the most common approach in modern .NET development.

  • You define your data models (C# classes) and the database context class in C# code.

  • EF Core then uses this code to generate the database and its schema (or update an existing one using migrations). This approach aligns well with Domain-Driven Design principles.

• Database-First: In this approach, you start with an existing database.

• You use EF Core tools (like the dotnet ef CLI or NuGet Package Manager Console commands) to reverse engineer the database schema and generate the corresponding C# entity and context classes automatically.

Basic Usage Example (Code-First)

Here is a simple example of how EF Core allows you to interact with a database using C# code:

1. Define a Model (C# class)

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } // EF Core knows this is non-nullable from context
    public decimal Price { get; set; }
}

2. Define a Database Context (inherits from DbContext)

using Microsoft.EntityFrameworkCore;

public class YouTubeDBContext : DbContext
{
    public YouTubeDBContext(DbContextOptions<YouTubeDBContext> options) : base(options) { }

    // DbSet represents the collection of all entities in the context
    public DbSet<Product> Products { get; set; }
}

3. Query Data using LINQ

using (var db = new YouTubeDBContext(options))
{
    // Example LINQ query to find a product
    var product = db.Products
                    .Where(p => p.Name == "Laptop")
                    .FirstOrDefault();

    if (product != null)
    {
        Console.WriteLine($"Found: {product.Name} at {product.Price:C}");
    }
}

This C# LINQ expression is translated into an efficient SQL query behind the scenes.

implement entity framework in web api

Implementing Entity Framework (EF) in a Web API involves several key steps, primarily using the Code First approach with modern ASP.NET Core for robust data access.

Here is a step-by-step guide:

Prerequisites

• Visual Studio 2022 or newer.
• .NET SDK (e.g., .NET 8 or 9).
• A database server (e.g., SQL Server, SQLite, PostgreSQL).

Steps

• 1. Create a New ASP.NET Core Web API Project

  • Open Visual Studio and select Create a new project.
  • Choose the ASP.NET Core Web API template and follow the prompts.

• 2. Install Entity Framework Core NuGet Packages Use the NuGet Package Manager Console in Visual Studio to install the necessary packages for your chosen database provider:

# For SQL Server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
# Microsoft.EntityFrameworkCore.Tools (for migrations)
# To use EF Core tools for migrations (install globally if not already)

dotnet tool install --global dotnet-ef

• 3. Define Your Data Model (Entity Class) Create a Models folder and add a C# class representing your database table (e.g., Employee.cs).

// Models/Employee.cs
using System.ComponentModel.DataAnnotations;

namespace WebApiCoreWithEF.Models
{
    public class Employee
    {
        [Key]
        public int EmployeeId { get; set; }
        public string EmployeeName { get; set; }
        public string Gender { get; set; }
        // ... other properties
    }
}

• 4. Create a Database Context Create a Context folder and add a class that inherits from DbContext and exposes DbSet properties for your entities. This class coordinates EF functionality for the model.

// Context/CompanyContext.cs
using Microsoft.EntityFrameworkCore;
using WebApiCoreWithEF.Models;

namespace WebApiCoreWithEF.Context
{
    public class CompanyContext : DbContext
    {
        public CompanyContext(DbContextOptions<CompanyContext> options) : base(options) { }

        public DbSet<Employee> Employees { get; set; }
    }
}

• 5. Configure the Connection String

Add your database connection string to the appsettings.json file.

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=CompanyDB;Integrated Security=True;TrustServerCertificate=True;"
  }
}

• 6. Register the DbContext with Dependency Injection In modern ASP.NET Core (.NET 6+), register your context in the Program.cs file using the built-in dependency injection container.

// Program.cs
// ... other code ...

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

// ... other code ...

• 7. Create the Database using Migrations Use the Package Manager Console or terminal to create a migration and update your database schema based on your models.

dotnet ef migrations add InitialCreate
dotnet ef database update

• 8. Add a Web API Controller Right-click the Controllers folder and select Add > Controller. Choose the API Controller with actions, using Entity Framework option and select your Model and Data context classes to automatically generate basic CRUD action methods. The generated controller will inject the CompanyContext via its constructor and use it to perform data operations asynchronously.

// Controllers/EmployeesController.cs (Generated Code Snippet)
[Route("api/[controller]")]
[ApiController]
public class EmployeesController : ControllerBase
{
    private readonly CompanyContext _context;

    public EmployeesController(CompanyContext context)
    {
        _context = context;
    }

    // GET: api/Employees
    [HttpGet]
    public async Task<ActionResult<IEnumerable<Employee>>> GetEmployees()
    {
        return await _context.Employees.ToListAsync();
    }
    
    // ... Post, Put, Delete methods are also generated ...
}

Your Web API is now integrated with Entity Framework and ready to perform data operations. You can test the endpoints using tools like Postman or Swagger UI.

Q) should you define model metadata using Data Anotation or Fluent API

Data Annotations

Data Annotations use attributes directly on the model's properties. This approach is generally preferred for its conciseness and readability, as the validation and metadata are defined right where the property is declared

Pros:

Concise: Metadata is co-located with the property definition.

Readable: Easy to quickly understand the constraints on a property.

Convention-based: Often integrates seamlessly with ASP.NET Core MVC/API validation and Entity Framework Core (EF Core) conventions

Cons:

Tight Coupling: The model class becomes coupled with validation and mapping concerns, which can violate the Single Responsibility Principle .

Limited Power: Less flexible for complex, conditional, or configuration-intensive mapping scenarios compared to the Fluent API

using System.ComponentModel.DataAnnotations;

namespace EFLearning.Models
{
    internal class Employee
    {
        public int Id { get; set; }
        [StringLength(50)]
        [Required]
        public string FirstName { get; set; }

        [Required]
        public string LastName { get; set; }
    }
}

Fluent API

The Fluent API allows developers to configure model metadata using a series of method calls, typically within the OnModelCreating method in the DbContext class for EF Core.

Pros:

Separation of Concerns: Keeps the model classes clean and free from configuration or validation logic .

Flexibility and Power: Capable of handling complex mapping scenarios, conditional logic, and configuration that Data Annotations cannot (e.g., configuring composite keys, specific table splitting, complex relationships) .

Clean Models: Model classes remain focused solely on the data structure [2].

Cons:

Less Discoverable: Configuration is separate from the property definition, requiring a developer to look in the DbContext to understand the full configuration of a property .

Verbosity: Can be more verbose for simple configuration compared to a single Data Annotation attribute.

Recommendation

A common approach is to use both:

• Use Data Annotations for the most common, simple validation rules and metadata (like [Required], [StringLength], or [Range]) that are naturally part of the model's definition .

• Use the Fluent API for complex configurations, data constraints that do not belong in the domain model itself, or when trying to keep your entity models completely clean of infrastructure concerns

Explore DbContext with Fluent API

1 way - we can define configuration of Entities in DbContext

using EFLearning.Models;
using Microsoft.EntityFrameworkCore;

namespace EFLearning.Data
{
    public class ApplicationDbContext: DbContext
    {
        // Declares a public class that inherits from DbContext
        // DbContext is the main class that coordinates EF Core functionality
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        { }

        // Override method called by EF Core to configure the data model
        // ModelBuilder provides a fluent API to configure entities and their relationships
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Begin configuring the Customer entity using the fluent API
            modelBuilder.Entity<Customer>(x =>
            {
                x.Property(x => x.FirstName).IsRequired().HasMaxLength(50);

                x.Property(x => x.LastName).IsRequired().HasMaxLength(50);

                x.Property(x => x.Email).IsRequired(false).HasMaxLength(256);
            });
            base.OnModelCreating(modelBuilder); // Call the base class implementation to apply any default configurations
        }
    

        // DbSet represents the collection of all entities in the context
        public DbSet<Employee> Employees { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Customer> Customers { get; set; }
    }
}

2 way - we can use IEntityTypeConfiguration

The IEntityTypeConfiguration<TEntity> interface in EF Core provides a way to encapsulate all Fluent API configuration for a specific entity type into a single, separate class. This promotes cleaner, more organized code by separating model configuration logic from the main DbContext file.

How to Implement and Use IEntityTypeConfiguration

1. Define the Configuration Class: Create a class that implements the IEntityTypeConfiguration<TEntity> interface for the desired entity. You must implement the Configure method, which accepts an EntityTypeBuilder<TEntity> object. This builder is used to apply configuration using the Fluent API.

• (Create Configuration folder -> create configuration class for Models)

using EFLearning.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace EFLearning.Configuration
{
    public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
    {
        public void Configure(EntityTypeBuilder<Customer> builder)
        {
            builder.Property(x=> x.FirstName).IsRequired().HasMaxLength(50);
            builder.Property(x => x.LastName).IsRequired().HasMaxLength(50);
            builder.Property(x => x.Email).IsRequired(false).HasMaxLength(256);

        }
    }
}

2. Apply the Configuration in DbContext: In your DbContext class, override the OnModelCreating method and use the ApplyConfiguration method to apply the configuration.

using EFLearning.Configuration;
using EFLearning.Models;
using Microsoft.EntityFrameworkCore;

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

        // DbSet represents the collection of all entities in the context
        public DbSet<Employee> Employees { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Customer> Customers { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
           
            base.OnModelCreating(modelBuilder); // Call the base class implementation to apply any default configurations

            modelBuilder.ApplyConfiguration( new CustomerConfiguration() );
           modelBuilder.ApplyConfiguration(new DepartmentConfiguration() );
        }
    }
}

3. Apply All Configurations from an Assembly (Optional): For larger projects, you can automatically apply all configuration classes defined in a specific assembly using the ApplyConfigurationsFromAssembly method. This eliminates the need to manually add each configuration.

using EFLearning.Configuration;
using EFLearning.Models;
using Microsoft.EntityFrameworkCore;

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

       
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder); // Call the base class implementation to apply any default configurations

            // Automatically find and apply all IEntityTypeConfiguration implementations in the current assembly
            modelBuilder.ApplyConfigurationsFromAssembly(typeof(ApplicationDbContext).Assembly );
        }
    

        // DbSet represents the collection of all entities in the context
        public DbSet<Employee> Employees { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Customer> Customers { get; set; }
    }
}

This approach enhances separation of concerns, making your EF Core configuration more maintainable, especially for complex models.

navigation property in entity framework core

A navigation property in Entity Framework (EF) Core is a property in an entity class that holds a reference to a related entity or a collection of related entities. They provide an object-oriented way to traverse and manage relationships (like one-to-many or many-to-many) between different parts of your data model, eliminating the need for complex SQL joins in your application code.

one-to-one relationship

a one-to-one relationship links a single instance of one entity to a single instance of another entity. This relationship can be configured using conventions, Data Annotations, or the Fluent API.

Key Concepts

Principal Entity: The primary entity in the relationship (e.g., Student).

Dependent Entity: The entity that depends on the principal entity (e.g., StudentAddress). It contains the foreign key.

Navigation Properties: Reference properties in both entities that allow traversing the relationship in both directions (bidirectional) or just one (unidirectional).

Foreign Key (FK): A property in the dependent entity that references the primary key (PK) of the principal entity. In EF Core, a unique index is automatically created on the foreign key column to enforce the one-to-one constraint in the database.

Configuration Methods

1. Using Conventions

EF Core can often configure the one-to-one relationship automatically if we follow standard naming conventions.

• Example Entities:

//Principal Entity
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public StudentAddress Address { get; set; } // Reference navigation property
}

//Dependent Entity:
public class StudentAddress
{
    public int Id { get; set; } // PK for StudentAddress
    public string Street { get; set; }
    public string City { get; set; }

    public int StudentId { get; set; } // Foreign key property (by convention)
    public Student Student { get; set; } // Reference navigation property
}

In this case, EF Core will detect StudentId as the foreign key in StudentAddress and configure the relationship automatically.

2. Using Data Annotations

we can use attributes to explicitly define the relationship, which is useful when your property names don't follow conventions.

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public EmployeeDetails Details { get; set; }
}

public class EmployeeDetails
{
    [Key, ForeignKey("Employee")] // Marks EmployeeId as both PK and FK referencing the Employee navigation property
    public int EmployeeId { get; set; }
    public string EmailAddress { get; set; }
    public Employee Employee { get; set; }
}

3. Using Fluent API

For full control, especially when conventions aren't met or for complex configurations, the Fluent API in the OnModelCreating method of your DbContext is the most powerful method.

public class MyDbContext : DbContext
{
    public DbSet<Student> Students { get; set; }
    public DbSet<StudentAddress> StudentAddresses { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>()
            .HasOne(s => s.Address)          // Student has one Address
            .WithOne(ad => ad.Student)       // Address has one Student
            .HasForeignKey<StudentAddress>(ad => ad.StudentId); // Specify the foreign key in the dependent entity
    }
}

//or IEntityTypeBuilder (Optional)
public class StudentConfiguration : IEntityTypeConfiguration<Student>
{
    public void Configure(EntityTypeBuilder<Student> builder)
    {
        builder.HasOne(x => x.StudentAddress).WithOne(x => x.Student)
            .HasForeignKey<StudentAddress>(x=> x.StudentId).
            OnDelete(DeleteBehavior.Cascade);

    }
}

This configuration ensures that the relationship is set up as intended, with the unique constraint on the foreign key.

one-to-many relationship

a one-to-many relationship can be established using conventions (automatic configuration based on naming patterns) or Fluent API for explicit control. This relationship is typically implemented using a foreign key in the dependent (many) entity that references the primary key of the principal (one) entity.

Key Concepts

• Principal Entity (the "One" side): Contains the primary key that the other entity references (e.g., Blog). It typically has a collection navigation property (ICollection<T>) for related entities.

• Dependent Entity (the "Many" side): Contains the foreign key property that links back to the principal entity (e.g., Post). It typically has a single reference navigation property.

• Foreign Key (FK): The column in the dependent table that stores the primary key value of the related principal record.

• Navigation Properties: Properties used in the C# model to navigate and manage related entities. They can be single reference properties or collections (ICollection<T>, List<T>, etc.).

Using Conventions

The simplest way is to follow EF Core's naming conventions. This usually means including a primary key property, a foreign key property, and navigation properties in your entity classes.

Consider a Blog (principal) and Post (dependent) relationship:

public class Blog
{
    public int Id { get; set; } // Primary Key
    public string Name { get; set; }
    public ICollection<Post> Posts { get; set; } // Collection navigation property
}

public class Post
{
    public int Id { get; set; } // Primary Key
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; } // Foreign Key property (conventionally named)
    public Blog Blog { get; set; } // Reference navigation property
}

In this scenario, EF Core automatically discovers the relationship, creating a non-nullable BlogId column in the Posts table by default, making it a required relationship. If BlogId was an int? (nullable int), the relationship would be optional.

Using Fluent API

If your entities don't follow conventions or you need more control (e.g., custom foreign key names, specific cascade delete behavior), you can use the Fluent API by overriding the OnModelCreating method in your DbContext class.

The Has/With pattern is used to configure relationships:

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

public class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .HasMany(b => b.Posts) // A Blog has many Posts
            .WithOne(p => p.Blog)  // Each Post has one Blog
            .HasForeignKey(p => p.BlogId) // Specifies the foreign key property
            .OnDelete(DeleteBehavior.Cascade); // Configures cascade delete behavior (optional)
    }
}

You can configure the relationship starting from either end (the principal or the dependent entity).

many-to-many relationship

a many-to-many relationship can be configured with minimal code using just collection navigation properties in both entities, or explicitly using a join entity class for more control (required in EF Core versions prior to 5.0). EF Core automatically manages the creation of a hidden "join" or "junction" table in the database in both scenarios.

Automatic Many-to-Many (EF Core 5.0+)

This is the simplest approach and the recommended default for most scenarios. EF Core conventions automatically handle the creation of the join table when it detects collection navigation properties in both related entities.

1. Define the Entities

Simply add a ICollection navigation property to both classes.

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    // Collection navigation property for the many-to-many relationship
    public ICollection<Category> Categories { get; set; } = new List<Category>();
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    // Collection navigation property for the many-to-many relationship
    public ICollection<Book> Books { get; set; } = new List<Book>();
}

2. Configure in DbContext

No explicit configuration is necessary in the OnModelCreating method if you follow the naming conventions. EF Core will automatically create a join table (e.g., BookCategory) with foreign keys to both Books and Categories tables.

Explicit Join Entity (All EF Core versions)

Use this approach when the join table needs additional properties (e.g., a DateJoined or a Quantity field), or if you need to customize the table and column names using Fluent API.

1. Define the Entities and the Join Entity

The join entity itself is a standard class that includes foreign keys to both main entities.

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    // Navigation property to the join entity
    public ICollection<BookCategory> BookCategories { get; set; } = new List<BookCategory>();
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    // Navigation property to the join entity
    public ICollection<BookCategory> BookCategories { get; set; } = new List<BookCategory>();
}

public class BookCategory
{
    // Foreign key properties forming a composite key
    public int BookId { get; set; }
    public Book Book { get; set; }

    public int CategoryId { get; set; }
    public Category Category { get; set; }

    // Additional properties specific to the relationship, if needed
    // public DateTime DateAdded { get; set; } 
}

2. Configure in DbContext using Fluent API

In your DbContext class, use the OnModelCreating method to define the composite key for the join table and configure the relationships.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BookCategory>()
        .HasKey(bc => new { bc.BookId, bc.CategoryId }); // Define the composite primary key

    modelBuilder.Entity<BookCategory>()
        .HasOne(bc => bc.Book)
        .WithMany(b => b.BookCategories)
        .HasForeignKey(bc => bc.BookId);

    modelBuilder.Entity<BookCategory>()
        .HasOne(bc => bc.Category)
        .WithMany(c => c.BookCategories)
        .HasForeignKey(bc => bc.CategoryId);
}

Key Considerations

Cascading Delete: By default, EF Core configures cascading delete behavior, meaning that deleting an entity on either side of the relationship will automatically remove associated entries in the join table.

Querying: When querying data, you can use the .Include() method to fetch related entities. For example, context.Books.Include(b => b.Categories).ToList() (for the automatic approach) or context.Books.Include(b => b.BookCategories).ThenInclude(bc => bc.Category).ToList() (for the explicit join entity approach).

Database Migrations and Seeding

Database Migration in Prod

- 1. Command Line 
- 2. SQL Script (recommended)
- 3. Migration Bundles

1.Using Command Line

// Create a migration
dotnet ef migrations add InitialCreate

// Apply migrations
dotnet ef database update

// Revert to specific migration
dotnet ef database update PreviousMigration

// Remove last migration (if not applied)
dotnet ef migrations remove

2. Using SQL Script

// Create a entire migration script
dotnet ef migrations script -o InitialSetup.sql

// Create a specific to migration
dotnet ef migrations script MigrationName -o filename.sql

// create script from migration a-to b 
dotnet ef migrations script aMigrationName bMigrationName -o filename.sql

// create Idepemptotent script -for execute mutilple time in database.

dotnet ef migrations script aMigrationName bMigrationName --idempotent -o filename.sql

3. Using Migration Bundles

// Create migration bundle for production deployment
dotnet ef migrations bundle --output ./efbundle.exe

// Run bundle
./efbundle.exe --connection "Server=prod-server;Database=prod-db;..."

Optional- Install Microsoft.EntityFrameworkCore.Tools

Entity Framework Core Tools for the NuGet Package Manager Console in Visual Studio.

Enables these commonly used commands:

• Add-Migration
• Bundle-Migration
• Drop-Database
• Get-DbContext
• Get-Migration
• Optimize-DbContext
• Remove-Migration
• Scaffold-DbContext
• Script-Migration
• Update-Database

Data Seeding into database

Basic Seeding

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Seed data
        modelBuilder.Entity<Category>().HasData(
            new Category { Id = 1, Name = "Electronics", Description = "Electronic devices" },
            new Category { Id = 2, Name = "Books", Description = "Books and publications" },
            new Category { Id = 3, Name = "Clothing", Description = "Clothing and accessories" }
        );

        modelBuilder.Entity<Product>().HasData(
            new Product { Id = 1, Name = "Laptop", Price = 999.99m, CategoryId = 1 },
            new Product { Id = 2, Name = "Smartphone", Price = 699.99m, CategoryId = 1 }
        );
    }
}

Advanced Seeding with IEntityTypeConfiguration

public class CategoryConfiguration : IEntityTypeConfiguration<Category>
{
    public void Configure(EntityTypeBuilder<Category> builder)
    {
        builder.HasData(
            new Category
            {
                Id = 1,
                Name = "Electronics",
                Description = "Electronic devices and accessories",
                CreatedAt = DateTime.UtcNow
            },
            new Category
            {
                Id = 2,
                Name = "Books",
                Description = "Books, magazines, and publications",
                CreatedAt = DateTime.UtcNow
            }
        );
    }
}

// In DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new CategoryConfiguration());
    // Other configurations...
}

Raw SQL Queries and Stored Procedures

In .NET 7 and .NET 8, Entity Framework (EF) Core streamlined how you interact with raw SQL and stored procedures, moving away from specialized "Interpolated" method names toward more intuitive, integrated commands.

1. Fetching Data (Queries)

The most significant change is the ability to query data into types that aren't part of your main entity model (DTOs or simple scalars).

FromSql (.NET 7 & 8): Replaces FromSqlInterpolated. It uses C# interpolated strings to automatically parameterize inputs, protecting you from SQL injection.

// Fetching full entities (must return all columns of the entity)
var products = await context.Products
    .FromSql($"SELECT * FROM Products WHERE CategoryId = {catId}")
    .ToListAsync();

SqlQuery (.NET 7 & 8):

• .NET 7: Introduced SqlQuery<T> primarily for scalar types (e.g., int, string, decimal).

• .NET 8: Expanded to support unmapped types (any plain C# class/DTO). You no longer need to define a DbSet or [Keyless] entity for every report or result set.

// EF Core 8: Querying directly into a DTO (no DbSet required)
var summaries = await context.Database
    .SqlQuery<OrderSummary>($"SELECT Id, Total FROM Orders")
    .ToListAsync();

2. Modifying Data (Commands)

• ExecuteSql (.NET 7 & 8): Replaces ExecuteSqlInterpolated. Use this for raw SQL UPDATE, DELETE, or INSERT commands that use string interpolation.

await context.Database.ExecuteSqlAsync($"UPDATE Products SET Price = {newPrice}");

ExecuteUpdate / ExecuteDelete (.NET 7+): While not raw SQL, these are the "modern" way to perform bulk operations without loading entities into memory. They translate LINQ directly to a single SQL command.

// Bulk delete without raw SQL strings
await context.Products.Where(p => p.Discontinued).ExecuteDeleteAsync();

3. Stored Procedures

Stored procedures in .NET 7/8 are handled using the same FromSql and ExecuteSql methods.

Simple Execution:

// Returning Entities
var results = await context.Products
    .FromSql($"EXEC GetProductsByCategory {categoryName}")
    .ToListAsync();

Complex Procedures (Output Parameters): You must still use SqlParameter objects and FromSqlRaw or ExecuteSqlRaw because string interpolation does not support OUTPUT direction.

var totalParam = new SqlParameter("@Total", SqlDbType.Int) { Direction = ParameterDirection.Output };
await context.Database.ExecuteSqlRawAsync("EXEC GetCount @Total OUTPUT", totalParam);
int total = (int)totalParam.Value;

4. Key Differences Summary

Feature	.NET 7	.NET 8
Interpolated SQL	Use FromSql / ExecuteSql.	Same (Preferred over Interpolated versions).
Scalar Results	Supported via SqlQuery<int>.	Supported.
DTO/Unmapped Results	Required [Keyless] or DbSet mapping.	Supported natively via SqlQuery<MyDTO>.
SQL Composition	Supported on FromSql.	Enhanced support on SqlQuery.

Recommendation: For almost all raw SQL needs in .NET 8, use FromSql (for entities) and SqlQuery<T> (for everything else). Only use Raw versions (FromSqlRaw, ExecuteSqlRaw) when you are building a SQL string dynamically at runtime.

Auditable Entity

Stop using Iconfiguration