Module 4: Working with Data Using Entity Framework Core
4.1. Introduction to Entity Framework Core
4.1.1. Understanding Object-Relational Mapping (ORM)
Definition of ORM

Concept of mapping between object-oriented programming languages and relational databases.
Benefits of using ORM: productivity, maintainability, and abstraction over database operations.
Challenges Without ORM

Manual SQL queries.
Impedance mismatch between object-oriented code and relational data models.
4.1.2. What is Entity Framework Core?
Overview of EF Core

Cross-platform, lightweight, and extensible version of Entity Framework.
Supports LINQ queries, change tracking, updates, and schema migrations.
EF Core vs. Entity Framework 6

Differences in features and performance.
When to choose EF Core over EF6.
4.1.3. Supported Databases
Database Providers
SQL Server, SQLite, MySQL, PostgreSQL, Oracle, In-Memory Database, etc.
Choosing the Right Provider
Considerations based on application requirements.
4.1.4. Installing Entity Framework Core
Adding EF Core to Your Project

Using NuGet Package Manager.
Installing required packages:
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer (or another provider)
Microsoft.EntityFrameworkCore.Tools
Hands-On Exercise:

Exercise: Install EF Core packages in a new or existing ASP.NET Core MVC 8 project.
4.2. Creating the Database Context
4.2.1. Understanding DbContext
Role of DbContext

Primary class for interacting with the database.
Manages entity objects during runtime.
Creating a DbContext Class

Inheriting from Microsoft.EntityFrameworkCore.DbContext.
4.2.2. Configuring the Database Connection
Using Dependency Injection

Registering DbContext in Program.cs with AddDbContext<TContext>().
Setting Up the Connection String

Adding a connection string in appsettings.json.
Using IConfiguration to access configuration settings.
4.2.3. Overriding OnConfiguring Method
When to Override OnConfiguring
For scenarios where dependency injection isn't used.
Setting up the database provider and connection string within DbContext.
4.2.4. Defining DbSet Properties
What is a DbSet?

Represents a collection of entities of a specific type.
Adding DbSet Properties for Each Entity

Example: public DbSet<Product> Products { get; set; }
Navigational Properties

Understanding how entities relate to each other.
4.2.5. Configuring the Model
Using Fluent API in OnModelCreating

Configuring entities and relationships.
Overriding conventions.
Model Configuration Classes
Organizing configurations using separate classes.

4.2.6. Hands-On Exercises
Exercise 1: Create an ApplicationDbContext class, configure it with a SQL Server provider, and define DbSet properties for Product and Category entities.
Exercise 2: Register the ApplicationDbContext in Program.cs and test the database connection.


4.3. Defining Your Data Models

4.3.1. Creating Entity Classes
Entity Design Principles
Each entity class represents a table in the database.
Properties represent columns.
Conventions vs. Configurations
How EF Core infers database schema from entity classes.
When to use data annotations or Fluent API to override conventions.

4.3.2. Data Annotations
Common Data Annotations
[Key], [Required], [MaxLength], [ForeignKey], [Table], [Column]
Using Data Annotations for Configuration
Setting primary keys, column types, and relationships.

4.3.3. Fluent API Configurations
Fluent API vs. Data Annotations
More powerful and flexible than data annotations.
Used in OnModelCreating method.
Examples of Fluent API Configurations
Setting composite keys.
Configuring relationships and cascade delete behaviors.
Specifying table and column mappings.

4.3.4. Value Conversions
Converting Property Values
Using HasConversion to convert between property types and database types.
Example: Storing enums as strings or integers.

4.3.5. Hands-On Exercises
Exercise 1: Define Product and Category entities with appropriate properties and data annotations.
Exercise 2: Use Fluent API to configure relationships and override default conventions.


4.4. Migrations and Database Schema Management

4.4.1. Understanding Migrations
Purpose of Migrations
Versioning your database schema alongside your code.
Migration Workflow
Add, update, and remove migrations.
Applying migrations to the database.

4.4.2. Creating and Applying Migrations
Adding a Migration
Using Package Manager Console:
Add-Migration InitialCreate
Using .NET CLI:
dotnet ef migrations add InitialCreate
Applying Migrations
Package Manager Console:
Update-Database
.NET CLI:
dotnet ef database update
Understanding Generated Migration Files
The Up() and Down() methods.
How EF Core translates model changes into database operations.

4.4.3. Modifying the Database Schema
Making Changes to Models
Adding or removing properties.
Changing data types.
Creating New Migrations
Reflecting model changes in new migrations.

4.4.4. Seeding the Database
Data Seeding
Populating the database with initial data.
Using HasData Method
Adding seed data in OnModelCreating.

4.4.5. Handling Migrations in Team Environments
Version Control of Migrations
Committing migration files to source control.
Resolving Migration Conflicts
Strategies for handling conflicts when multiple developers are adding migrations.

4.4.6. Rolling Back Migrations
Undoing Applied Migrations
Reverting the database to a previous state.
Removing Migrations
Deleting migrations not yet applied to the database.

4.4.7. Hands-On Exercises
Exercise 1: Create an initial migration and apply it to the database.
Exercise 2: Modify an entity by adding a new property, create a new migration, and update the database.
Exercise 3: Seed the database with initial data for Categories and Products.


4.5. Querying Data with LINQ and Entity Framework Core

4.5.1. Introduction to LINQ (Language Integrated Query)
What is LINQ?

A query syntax in .NET to query collections and databases.
LINQ Query Syntax vs. Method Syntax

Examples of both syntaxes.
When to use each.

4.5.2. Basic Query Operations
Selecting Data

Select, SelectMany.
Filtering Data

Where clause for filtering records.
Ordering Data

OrderBy, OrderByDescending, ThenBy.
Projection

Selecting specific fields into anonymous or typed objects.

4.5.3. Eager Loading, Lazy Loading, and Explicit Loading
Eager Loading

Using Include and ThenInclude methods.
Fetching related data as part of the initial query.
Lazy Loading

Automatically loading related data on-demand.
Enabling lazy loading proxies.
Explicit Loading

Loading related data explicitly using Load method.

4.5.4. Tracking vs. No-Tracking Queries
Understanding Change Tracking

How EF Core tracks entity changes for updates.
AsNoTracking

Improving performance for read-only operations.

4.5.5. Query Execution and Deferred Execution
Deferred Execution

Queries are not executed until iterated over.
Immediate Execution Methods

ToList(), First(), Count(), Any()

4.5.6. Advanced Querying Techniques
Grouping and Aggregation

GroupBy, Sum, Average, Min, Max
Joins

Joining entities using navigational properties.
Performing cross joins, inner joins, left joins.
Raw SQL Queries

Executing raw SQL commands with FromSqlRaw and ExecuteSqlRaw.

4.5.7. Handling Concurrency
Concurrency Tokens
Using [ConcurrencyCheck] attribute.
Handling concurrency exceptions.
Optimistic Concurrency Control

Strategies for resolving concurrency conflicts.

4.5.8. Hands-On Exercises
Exercise 1: Write LINQ queries to retrieve products with specific criteria (e.g., products under a certain price).
Exercise 2: Implement eager loading to include categories when retrieving products.
Exercise 3: Use grouping and aggregation to find the number of products in each category.
Exercise 4: Execute a raw SQL query to retrieve data.


4.6. Modifying Data in Entity Framework Core

4.6.1. Inserting Data
Adding Single Entities

Using Add or AddAsync methods.
Adding Multiple Entities

Using AddRange or AddRangeAsync.

4.6.2. Updating Data
Retrieving and Modifying Entities

Fetching the entity, changing properties, and calling SaveChanges.
Attaching Detached Entities

Handling entities not tracked by the context.
Concurrency Handling

Dealing with DbUpdateConcurrencyException.

4.6.3. Deleting Data
Removing Entities

Using Remove or RemoveRange.
Cascade Delete

Configuring cascade delete behaviors for relationships.
Soft Deletes

Implementing logical deletion by marking records as inactive.

4.6.4. Transactions
Understanding Transactions

Ensuring data integrity.
Using BeginTransaction

Manually controlling transactions.
Automatic Transactions

How EF Core handles transactions by default.

4.6.5. Batch Operations
Bulk Updates and Deletes

Performing batch operations efficiently.
Third-Party Libraries

Using libraries like EFCore.BulkExtensions for bulk operations.

4.6.6. Hands-On Exercises
Exercise 1: Implement Create, Update, and Delete operations for products.
Exercise 2: Handle concurrency exceptions when updating product data.
Exercise 3: Implement soft delete functionality for products.


4.7. Relationships in Depth

4.7.1. One-to-Many Relationships
Defining One-to-Many

Example: A Category has many Products.
Configuring Relationships

Using navigational properties and foreign keys.
Fluent API configurations.

4.7.2. Many-to-Many Relationships
EF Core 5 and Later Support

Defining many-to-many without join entity classes.
Configuring Many-to-Many

Using collection navigational properties on both sides.
Customizing the Join Table

If additional fields are needed, creating an explicit join entity.

4.7.3. One-to-One Relationships
Defining One-to-One

Example: A Product has one ProductDetail.
Configuring One-to-One

Specifying principal and dependent entities.

4.7.4. Inheritance Mapping
Table-Per-Hierarchy (TPH)

Mapping inheritance hierarchies to a single table.
Table-Per-Type (TPT)

Mapping each entity type to a separate table.
Implementing Inheritance

Using HasBaseType in Fluent API.

4.7.5. Owned Entities (Complex Types)
Defining Owned Types

Value objects that are part of the owner entity.
Configuring Owned Entities

Using OwnsOne and OwnsMany.

4.7.6. Hands-On Exercises
Exercise 1: Configure a many-to-many relationship between Product and Supplier.
Exercise 2: Create an owned entity for Address within Supplier.
Exercise 3: Implement inheritance in your model (e.g., ElectronicProduct inherits from Product).


4.8. Advanced Topics

4.8.1. Interceptors
Definition

Intercepting database operations.
Implementing Interceptors

Creating classes that implement IDbCommandInterceptor, ISaveChangesInterceptor.
Use Cases

Logging, auditing, modifying queries.

4.8.2. Compiled Queries
Improving Performance

Caching query plans for reuse.
Creating Compiled Queries

Using EF.CompileQuery methods.

4.8.3. Handling Stored Procedures
Executing Stored Procedures

Using FromSqlRaw and ExecuteSqlRaw.
Mapping Results to Entities

Handling complex result sets.

4.8.4. Connection Resiliency
Retry Logic

Handling transient faults in database connections.
Configuring Retry Policies

Using EnableRetryOnFailure.

4.8.5. Testing with In-Memory Database
Using In-Memory Provider

For unit testing purposes.
Limitations

Differences from real relational databases.

4.8.6. Performance Tuning
Optimizing Queries

Avoiding N+1 query problems.
Monitoring and Profiling

Using tools to analyze performance.

4.8.7. Hands-On Exercises
Exercise 1: Implement a stored procedure call to retrieve top-selling products.
Exercise 2: Use compiled queries to improve performance in critical areas.
Exercise 3: Set up an interceptor to log all executed SQL commands.
4.9. Summary and Best Practices
4.9.1. Best Practices
Separation of Concerns

Keeping data access logic separate.
Avoiding Common Pitfalls

Overfetching data, not handling exceptions.
Security Considerations

Preventing SQL injection (though EF Core helps here).
4.9.2. Resources for Further Learning
Official Documentation

Microsoft Docs for EF Core.
Community Resources

Blogs, tutorials, forums.
Books and Courses

Advanced topics and case studies.
Module 4 Hands-On Project
Project: Build a comprehensive product catalog application with the following features:

Data Models:

Product, Category, Supplier, ProductDetail, Order, OrderItem
Relationships:

One-to-Many between Category and Product
Many-to-Many between Product and Supplier
One-to-One between Product and ProductDetail
One-to-Many between Order and OrderItem
Many-to-One between OrderItem and Product
Functionalities:

Full CRUD operations for all entities.
Implement soft deletes for products.
Use transactions when creating orders.
Handle concurrency conflicts.
Include data validation and error handling.
Advanced Features:

Use compiled queries in performance-critical areas.
Implement logging of database operations using interceptors.
Execute stored procedures for reporting purposes.
Outcome:

By completing this project, you will apply all the concepts learned in Module 4, gaining hands-on experience in working with data using Entity Framework Core.


INSTRUCTION: - YOU HAVE TO EXPLAIN 4.4.1 AND 4.4.2 IN VERY VERY VERY VERY LONG EXPLANATION WITH COMPREHENSIVE DETAILS AND CODE EXAMPLES. WRITE FILE PATH IF SO