Entity Framework Core Flashcards
Entity Framework Core
Entity Framework Core is the new version of Entity Framework after EF 6.x. It is open-source, lightweight, extensible and a cross-platform version of Entity Framework data access technology.
Entity Framework is an Object/Relational Mapping (O/RM) framework. It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in the database.
EF Core is intended to be used with .NET Core applications. However, it can also be used with standard .NET 4.5+ framework based applications.
EF Core Development Approaches
EF Core supports two development approaches 1) Code-First 2) Database-First. EF Core mainly targets the code-first approach and provides little support for the database-first approach because the visual designer or wizard for DB model is not supported as of EF Core 2.0.
In the code-first approach, EF Core API creates the database and tables using migration based on the conventions and configuration provided in your domain classes. This approach is useful in Domain Driven Design (DDD).
In the database-first approach, EF Core API creates the domain and context classes based on your existing database using EF Core commands. This has limited support in EF Core as it does not support visual designer or wizard.
EF Core Database Providers
Entity Framework Core uses a provider model to access many different databases. EF Core includes providers as NuGet packages which you need to install.
The following table lists database providers and NuGet packages for EF Core.
Database NuGet Package
SQL Server Microsoft.EntityFrameworkCore.SqlServer
MySQL MySql.Data.EntityFrameworkCore
PostgreSQL Npgsql.EntityFrameworkCore.PostgreSQL
SQLite Microsoft.EntityFrameworkCore.SQLite
SQL Compact EntityFrameworkCore.SqlServerCompact40
In-memory Microsoft.EntityFrameworkCore.InMemory
Install Entity Framework Core
Entity Framework Core can be used with .NET Core or .NET 4.6 based applications. Here, you will learn to install and use Entity Framework Core 2.0 in .NET Core applications using Visual Studio 2017.
EF Core is not a part of .NET Core and standard .NET framework. It is available as a NuGet package. You need to install NuGet packages for the following two things to use EF Core in your application:
EF Core DB provider
EF Core tools
for sql server
Microsoft.EntityFrameworkCore.SqlServer(make sure that it has the .NET symbol and the Author is Microsoft)
Notice that the provider NuGet package also installed other dependent packages such as Microsoft.EntityFrameworkCore.Relational and System.Data.SqlClient.
Alternatively, you can also install provider’s NuGet package using Package Manager Console. Go to Tools -> NuGet Package Manager -> Package Manager Console and execute the following command to install SQL Server provider package:
PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install EF Core Tools
Along with the DB provider package, you also need to install EF tools to execute EF Core commands. These make it easier to perform several EF Core-related tasks in your project at design time, such as migrations, scaffolding, etc.
EF Tools are available as NuGet packages. You can install NuGet package for EF tools depending on where you want to execute commands: either using Package Manager Console (PowerShell version of EF Core commands) or using dotnet CLI.
Install EF Core Tools for PMC
In order to execute EF Core commands from Package Manager Console, search for the Microsoft.EntityFrameworkCore.Tools package from NuGet UI
This will allow you to execute EF Core commands for scaffolding, migration etc. directly from Package Manager Console (PMC) within Visual Studio.
to execute EF Core commands from .NET Core’s CLI
Install EF Core Tools for dotnet CLI
If you want to execute EF Core commands from .NET Core’s CLI (Command Line Interface), first install the NuGet package Microsoft.EntityFrameworkCore.Tools.DotNet using NuGet UI.
After installing Microsoft.EntityFrameworkCore.Tools.DotNet package, edit the .csproj file by right clicking on the project in the Solution Explorer and select Edit .csproj. Add node as shown below. This is an extra step you need to perform in order to execute EF Core 2.0 commands from dotnet CLI in VS2017.
Exe netcoreapp2.0
Now, open the command prompt (or terminal) from the root folder of your project and execute EF Core commands from CLI starting with dotnet ef
Creating a Model for an Existing Database in Entity Framework Core
Here you will learn how to create the context and entity classes for an existing database in Entity Framework Core. Creating entity & context classes for an existing database is called Database-First approach.
EF Core does not support visual designer for DB model and wizard to create the entity and context classes similar to EF 6. So, we need to do reverse engineering using the Scaffold-DbContext command. This reverse engineering command creates entity and context classes (by deriving DbContext) based on the schema of the existing database.
Scaffold-DbContext Command
Use Scaffold-DbContext to create a model based on your existing database. The following parameters can be specified with Scaffold-DbContext in Package Manager Console:
Scaffold-DbContext [-Connection] [-Provider] [-OutputDir] [-Context] [-Schemas>] [-Tables>]
[-DataAnnotations] [-Force] [-Project] [-StartupProject] []
In Visual Studio, select menu Tools -> NuGet Package Manger -> Package Manger Console and run the following command:
PM> Scaffold-DbContext “Server=.\SQLExpress;Database=SchoolDB;
Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Model
In the above command, the first parameter is a connection string which includes three parts: DB Server, database name and security info. Here, Server=.\SQLExpress; refers to local SQLEXPRESS database server. Database=SchoolDB; specifies the database name “SchoolDB” for which we are going to create classes. Trusted_Connection=True; specifies the Windows authentication. It will use Windows credentials to connect to the SQL Server. The second parameter is the provider name. We use provider for the SQL Server, so it is Microsoft.EntityFrameworkCore.SqlServer. The -OutputDir parameter specifies the directory where we want to generate all the classes which is the Models folder in this case.
The above Scaffold-DbContext command creates entity classes for each table in the SchoolDB database and context class (by deriving DbContext) with Fluent API configurations for all the entities in the Models folder.
get-help scaffold-dbcontext –detailed
Use the following command to get the detailed help on Scaffold-DbContext command:
PM> get-help scaffold-dbcontext –detailed
The following is the generated Student entity class for the Student table.
using System;
using System.Collections.Generic;
namespace EFCoreTutorials.Models { public partial class Student { public Student() { StudentCourse = new HashSet(); }
public int StudentId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public int? StandardId { get; set; }
public Standard Standard { get; set; } public StudentAddress StudentAddress { get; set; } public ICollection StudentCourse { get; set; } } } The following is the SchoolDBContext class which you can use to save or retrieve data.
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace EFCoreTutorials.Models { public partial class SchoolDBContext : DbContext { public virtual DbSet Course { get; set; } public virtual DbSet Standard { get; set; } public virtual DbSet Student { get; set; } public virtual DbSet StudentAddress { get; set; } public virtual DbSet StudentCourse { get; set; } public virtual DbSet Teacher { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { #warning To protect potentially sensitive information in your connection string, you should move it out of source code
. See http://go.microsoft.com/fwlink/?
LinkId=723263 for guidance on storing connection strings.
optionsBuilder.UseSqlServer(@”Server=.\SQLExpress
;Database=SchoolDB;Trusted_Connection=True;”);
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(entity => { entity.Property(e => e.CourseName) .HasMaxLength(50) .IsUnicode(false); entity.HasOne(d => d.Teacher) .WithMany(p => p.Course) .HasForeignKey(d => d.TeacherId) .OnDelete(DeleteBehavior.Cascade) .HasConstraintName("FK_Course_Teacher"); }); modelBuilder.Entity(entity => { entity.Property(e => e.Description) .HasMaxLength(50) .IsUnicode(false); entity.Property(e => e.StandardName) .HasMaxLength(50) .IsUnicode(false); }); modelBuilder.Entity(entity => { entity.Property(e => e.StudentId).HasColumnName("StudentID"); entity.Property(e => e.FirstName) .HasMaxLength(50) .IsUnicode(false); entity.Property(e => e.LastName) .HasMaxLength(50) .IsUnicode(false); entity.HasOne(d => d.Standard) .WithMany(p => p.Student) .HasForeignKey(d => d.StandardId) .OnDelete(DeleteBehavior.Cascade) .HasConstraintName("FK_Student_Standard"); }); modelBuilder.Entity(entity => { entity.HasKey(e => e.StudentId); entity.Property(e => e.StudentId) .HasColumnName("StudentID") .ValueGeneratedNever(); entity.Property(e => e.Address1) .IsRequired() .HasMaxLength(50) .IsUnicode(false); entity.Property(e => e.Address2) .HasMaxLength(50) .IsUnicode(false); entity.Property(e => e.City) .IsRequired() .HasMaxLength(50) .IsUnicode(false); entity.Property(e => e.State) .IsRequired() .HasMaxLength(50) .IsUnicode(false); entity.HasOne(d => d.Student) .WithOne(p => p.StudentAddress) .HasForeignKey(d => d.StudentId) .HasConstraintName("FK_StudentAddress_Student"); }); modelBuilder.Entity(entity => { entity.HasKey(e => new { e.StudentId, e.CourseId }); entity.HasOne(d => d.Course) .WithMany(p => p.StudentCourse) .HasForeignKey(d => d.CourseId) .OnDelete(DeleteBehavior.ClientSetNull) .HasConstraintName("FK_StudentCourse_Course"); entity.HasOne(d => d.Student) .WithMany(p => p.StudentCourse) .HasForeignKey(d => d.StudentId) .HasConstraintName("FK_StudentCourse_Student"); }); modelBuilder.Entity(entity => { entity.Property(e => e.StandardId).HasDefaultValueSql("((0))"); entity.Property(e => e.TeacherName) .HasMaxLength(50) .IsUnicode(false); entity.HasOne(d => d.Standard) .WithMany(p => p.Teacher) .HasForeignKey(d => d.StandardId) .OnDelete(DeleteBehavior.Cascade) .HasConstraintName("FK_Teacher_Standard"); }); } } }
EF Core creates entity classes only for tables and not for StoredProcedures or Views.
EF Core creates entity classes only for tables and not for StoredProcedures or Views.
dotnet ef dbcontext scaffold
DotNet CLI
If you use dotnet command line interface to execute EF Core commands then open command prompt and navigate to the root folder and execute the following dotnet ef dbcontext scaffold command:
dotnet ef dbcontext scaffold “Server=.\SQLEXPRESS;Database=SchoolDB;
Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -o Models
use the Migration commands whenever you change the model
Once you have created the model, you must use the Migration commands whenever you change the model to keep the database up to date with the model.
Configuring a DbContext
https://docs.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext
Entity Framework Core: DbContext
The DbContext class is an integral part of Entity Framework. An instance of DbContext represents a session with the database which can be used to query and save instances of your entities to a database. DbContext is a combination of the Unit Of Work and Repository patterns.
DbContext in EF Core allows us to perform following tasks
Manage database connection Configure model & relationship Querying database Saving data to the database Configure change tracking Caching Transaction managemen
To use DbContext in our application
To use DbContext in our application, we need to create the class that derives from DbContext, also known as context class. This context class typically includes DbSet properties for each entity in the model. Consider the following example of context class in EF Core.
public class SchoolContext : DbContext { public SchoolContext() {
} protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { }
protected override void OnModelCreating(ModelBuilder modelBuilder) { } //entities public DbSet Students { get; set; } public DbSet Courses { get; set; } } In the example above, the SchoolContext class is derived from the DbContext class and contains the DbSet properties of Student and Course type. It also overrides the OnConfiguring and OnModelCreating methods. We must create an instance of SchoolContext to connect to the database and save or retrieve Student or Course data.
OnConfiguring() method
The OnConfiguring() method allows us to select and configure the data source to be used with a context using DbContextOptionsBuilder. Learn how to configure a DbContext class at here.
OnModelCreating() method
The OnModelCreating() method allows us to configure the model using ModelBuilder Fluent API.
DbContext Methods
Method Usage
Add Adds a new entity to DbContext with Added state and starts tracking it. This new entity data will be inserted into the database when SaveChanges() is called.
AddAsync Asynchronous method for adding a new entity to DbContext with Added state and starts tracking it. This new entity data will be inserted into the database when SaveChangesAsync() is called.
AddRange Adds a collection of new entities to DbContext with Added state and starts tracking it. This new entity data will be inserted into the database when SaveChanges() is called.
AddRangeAsync Asynchronous method for adding a collection of new entities which will be saved on SaveChangesAsync().
Attach Attaches a new or existing entity to DbContext with Unchanged state and starts tracking it.
AttachRange Attaches a collection of new or existing entities to DbContext with Unchanged state and starts tracking it.
Entry Gets an EntityEntry for the given entity. The entry provides access to change tracking information and operations for the entity.
Find Finds an entity with the given primary key values.
FindAsync Asynchronous method for finding an entity with the given primary key values.
Remove Sets Deleted state to the specified entity which will delete the data when SaveChanges() is called.
RemoveRange Sets Deleted state to a collection of entities which will delete the data in a single DB round trip when SaveChanges() is called.
SaveChanges Execute INSERT, UPDATE or DELETE command to the database for the entities with Added, Modified or Deleted state.
SaveChangesAsync Asynchronous method of SaveChanges()
Set Creates a DbSet that can be used to query and save instances of TEntity.
Update Attaches disconnected entity with Modified state and start tracking it. The data will be saved when SaveChagnes() is called.
UpdateRange Attaches a collection of disconnected entities with Modified state and start tracking it. The data will be saved when SaveChagnes() is called.
OnConfiguring Override this method to configure the database (and other options) to be used for this context. This method is called for each instance of the context that is created.
OnModelCreating Override this method to further configure the model that was discovered by convention from the entity types exposed in DbSet properties on your derived context.
DbContext Properties
Method Usage
ChangeTracker Provides access to information and operations for entity instances this context is tracking.
Database Provides access to database related information and operations for this context.
Model Returns the metadata about the shape of entities, the relationships between them, and how they map to the database.
Creating the Model
Entity Framework needs to have a model (Entity Data Model) to communicate with the underlying database. It builds a model based on the shape of your domain classes, the Data Annotations and Fluent API configurations.
The EF model includes three parts: conceptual model, storage model, and mapping between the conceptual and storage models. In the code-first approach, EF builds the conceptual model based on your domain classes (entity classes), the context class and configurations. EF Core builds the storage model and mappings based on the provider you use. For example, the storage model will be different for the SQL Server compared with DB2.
EF uses this model for CRUD (Create, Read, Update, Delete) operations to the underlying database.
Code first appraoch -create entity classes and context classes first
public class Student { public int StudentId { get; set; } public string Name { get; set; } }
public class Course { public int CourseId { get; set; } public string CourseName { get; set; } } Now, we need to create a context class by deriving the DbContext, as shown in the previous chapter. The following SchoolContext class is also called context class.
namespace EFCoreTutorials { public class SchoolContext : DbContext { public DbSet Students { get; set; } public DbSet Courses { get; set; }
protected override void OnConfiguring (DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@" Server=.\SQLEXPRESS ;Database=SchoolDB;Trusted_Connection=True;"); } } }
The above context class includes two DbSet properties, for Student and Course, type which will be mapped to the Students and Courses tables in the underlying database. In the OnConfiguring() method, an instance of DbContextOptionsBuilder is used to specify which database to use. We have installed MS SQL Server provider, which has added the extension method UseSqlServer on DbContextOptionsBuilder.
The connection string “Server=.\SQLEXPRESS;Database=SchoolDB;
Trusted_Connection=True;” in the UseSqlServer method provides database information: Server= specifies the DB Server to use, Database= specifies the name of the database to create and Trusted_Connection=True specifies the Windows authentication mode. EF Core will use this connection string to create a database when we run the migration.
After creating the context and entity classes, it’s time to add the migration to create a database.
“Server=.\SQLEXPRESS;Database=SchoolDB;
Trusted_Connection=True;”
Trusted_Connection=True specifies the Windows authentication mode
After creating the context and entity classes, it’s time to add the migration to create a database.
After creating the context and entity classes, it’s time to add the migration to create a database.
Adding a Migration = to create database from the code first approach
EF Core includes different migration commands to create or update the database based on the model. At this point, there is no SchoolDB database. So, we need to create the database from the model (entities and context) by adding a migration.
We can execute the migration command using NuGet Package Manger Console as well as dotnet CLI (command line interface).
In Visual Studio, open NuGet Package Manager Console from Tools -> NuGet Package Manager -> Package Manager Console and enter the following command:
PM> add-migration CreateSchoolDB
If you use dotnet CLI, enter the following command.
> dotnet ef migrations add CreateSchoolDB
This will create a new folder named Migrations in the project and create the ModelSnapshot files, as shown below.
After creating a migration, we still need to create the database using the update-database command in the Package Manager Console, as below.
PM> update-database –verbose
Enter the following command in dotnet CLI.
> dotnet ef database update
This will create the database with the name and location specified in the connection string in the UseSqlServer() method. It creates a table for each DbSet property (Students and Courses) as shown below.
This was the first migration to create a database. Now, whenever we add or update domain classes or configurations, we need to sync the database with the model using add-migration and update-database commands.
whenever we add or update domain classes or configurations, we need to sync the database with the model using add-migration and update-database commands.
This was the first migration to create a database. Now, whenever we add or update domain classes or configurations, we need to sync the database with the model using add-migration and update-database commands.
Reading or Writing Data
Now, we can use the context class to save and retrieve data, as shown below.
namespace EFCoreTutorials { class Program { static void Main(string[] args) { using (var context = new SchoolContext()) {
var std = new Student() { Name = "Bill" };
context.Students.Add(std); context.SaveChanges(); } } } }
Querying in Entity Framework Core
Querying in Entity Framework Core remains the same as in EF 6.x, with more optimized SQL queries and the ability to include C#/VB.NET functions into LINQ-to-Entities queries.
C#/VB.NET Functions in Queries
EF Core has a new feature in LINQ-to-Entities where we can include C# or VB.NET functions in the query. This was not possible in EF 6.
private static void Main(string[] args)
{
var context = new SchoolContext();
var studentsWithSameName = context.Students
.Where(s => s.FirstName == GetName())
.ToList();
}
public static string GetName() {
return “Bill”;
}
In the above L2E query, we have included the GetName() C# function in the Where clause. This will execute the following query in the database:
exec sp_executesql N’SELECT [s].[StudentId], [s].[DoB], [s].[FirstName],
[s].[GradeId], [s].[LastName], [s].[MiddleName]
FROM [Students] AS [s]
WHERE [s].[FirstName] = @__GetName_0’,N’@__GetName_0 nvarchar(4000)’,
@__GetName_0=N’Bill’
Go
Eager Loading with Include
Entity Framework Core supports eager loading of related entities, same as EF 6, using the Include() extension method and projection query. In addition to this, it also provides the ThenInclude() extension method to load multiple levels of related entities. (EF 6 does not support the ThenInclude() method.)
Unlike EF 6, we can specify a lambda expression as a parameter in the Include() method to specify a navigation property as shown below.
var context = new SchoolContext();
var studentWithGrade = context.Students
.Where(s => s.FirstName == “Bill”)
.Include(s => s.Grade)
.FirstOrDefault();
In the above example, .Include(s => s.Grade) passes the lambda expression s => s.Grade to specify a reference property to be loaded with Student entity data from the database in a single SQL query. The above query executes the following SQL query in the database.
SELECT TOP(1) [s].[StudentId], [s].[DoB], [s].[FirstName], [s].[GradeId],[s].[LastName],
[s].[MiddleName], [s.Grade].[GradeId], [s.Grade].[GradeName], [s.Grade].[Section]
FROM [Students] AS [s]
LEFT JOIN [Grades] AS [s.Grade] ON [s].[GradeId] = [s.Grade].[GradeId]
WHERE [s].[FirstName] = N’Bill’
Include() method, same as in EF 6 - Not recommended using lambda instead
We can also specify property name as a string in the Include() method, same as in EF 6.
var context = new SchoolContext();
var studentWithGrade = context.Students
.Where(s => s.FirstName == “Bill”)
.Include(“Grade”)
.FirstOrDefault();
The example above is not recommended because it will throw a runtime exception if a property name is misspelled or does not exist. Always use the Include() method with a lambda expression, so that the error can be detected during compile time.
Include from sql
The Include() extension method can also be used after the FromSql() method, as shown below.
var context = new SchoolContext();
var studentWithGrade = context.Students
.FromSql(“Select * from Students where FirstName =’Bill’”)
.Include(s => s.Grade)
.FirstOrDefault();
Include() extension method cannot be used after the DbSet.Find()
The Include() extension method cannot be used after the DbSet.Find() method. E.g. context.Students.Find(1).Include() is not possible in EF Core 2.0. This may be possible in future versions.
Multiple Include
Use the Include() method multiple times to load multiple navigation properties of the same entity. For example, the following code loads Grade and StudentCourses related entities of Student.
var context = new SchoolContext();
var studentWithGrade = context.Students.Where(s => s.FirstName == “Bill”)
.Include(s => s.Grade)
.Include(s => s.StudentCourses)
.FirstOrDefault();
The above query will execute two SQL queries in a single database round trip.
SELECT TOP(1) [s].[StudentId], [s].[DoB], [s].[FirstName], [s].[GradeId], [s].[LastName],
[s].[MiddleName], [s.Grade].[GradeId], [s.Grade].[GradeName], [s.Grade].[Section]
FROM [Students] AS [s]
LEFT JOIN [Grades] AS [s.Grade] ON [s].[GradeId] = [s.Grade].[GradeId]
WHERE [s].[FirstName] = N’Bill’
ORDER BY [s].[StudentId]
Go
SELECT [s.StudentCourses].[StudentId], [s.StudentCourses].[CourseId]
FROM [StudentCourses] AS [s.StudentCourses]
INNER JOIN (
SELECT DISTINCT [t].*
FROM (
SELECT TOP(1) [s0].[StudentId]
FROM [Students] AS [s0]
LEFT JOIN [Grades] AS [s.Grade0] ON [s0].[GradeId] = [s.Grade0].[GradeId]
WHERE [s0].[FirstName] = N’Bill’
ORDER BY [s0].[StudentId]
) AS [t]
) AS [t0] ON [s.StudentCourses].[StudentId] = [t0].[StudentId]
ORDER BY [t0].[StudentId]
Go
ThenInclude
EF Core introduced the new ThenInclude() extension method to load multiple levels of related entities. Consider the following example:
var context = new SchoolContext();
var student = context.Students.Where(s => s.FirstName == “Bill”)
.Include(s => s.Grade)
.ThenInclude(g => g.Teachers)
.FirstOrDefault();
In the above example, .Include(s => s.Grade) will load the Grade reference navigation property of the Student entity. .ThenInclude(g => g.Teachers) will load the Teacher collection property of the Grade entity. The ThenInclude method must be called after the Include method. The above will execute the following SQL queries in the database.
SELECT TOP(1) [s].[StudentId], [s].[DoB], [s].[FirstName], [s].[GradeId], [s].[LastName],
[s].[MiddleName], [s.Grade].[GradeId], [s.Grade].[GradeName], [s.Grade].[Section]
FROM [Students] AS [s]
LEFT JOIN [Grades] AS [s.Grade] ON [s].[GradeId] = [s.Grade].[GradeId]
WHERE [s].[FirstName] = N’Bill’
ORDER BY [s.Grade].[GradeId]
Go
SELECT [s.Grade.Teachers].[TeacherId], [s.Grade.Teachers].[GradeId], [s.Grade.Teachers].[Name]
FROM [Teachers] AS [s.Grade.Teachers]
INNER JOIN (
SELECT DISTINCT [t].*
FROM (
SELECT TOP(1) [s.Grade0].[GradeId]
FROM [Students] AS [s0]
LEFT JOIN [Grades] AS [s.Grade0] ON [s0].[GradeId] = [s.Grade0].[GradeId]
WHERE [s0].[FirstName] = N’Bill’
ORDER BY [s.Grade0].[GradeId]
) AS [t]
) AS [t0] ON [s.Grade.Teachers].[GradeId] = [t0].[GradeId]
ORDER BY [t0].[GradeId]
go
Projection Query
We can also load multiple related entities by using the projection query instead of Include() or ThenInclude() methods. The following example demonstrates the projection query to load the Student, Grade, and Teacher entities.
var context = new SchoolContext();
var stud = context.Students.Where(s => s.FirstName == “Bill”)
.Select(s => new
{
Student = s,
Grade = s.Grade,
GradeTeachers = s.Grade.Teachers
})
.FirstOrDefault();
In the above example, the .Select extension method is used to include the Student, Grade and Teacher entities in the result. This will execute the same SQL query as the above ThenInclude() method.
Lazy Loading
Lazy loading is not supported in Entity Framework Core 2.0. Track lazy loading issue on github.
Explicit Loading
Explicit loading works the same way as in EF 6. Learn about it here.
Here you will learn how to load related entities in an entity graph explicitly. Explicit loading is valid in EF 6 and EF Core both.
Even with lazy loading disabled (in EF 6), it is still possible to lazily load related entities, but it must be done with an explicit call. Use the Load() method to load related entities explicitly. Consider the following example.
using (var context = new SchoolContext())
{
var student = context.Students
.Where(s => s.FirstName == “Bill”)
.FirstOrDefault();
context.Entry(student).Reference(s => s.StudentAddress).Load(); // loads StudentAddress context.Entry(student).Collection(s => s.StudentCourses).Load(); // loads Courses collection }
In the above example, context.Entry(student).Reference(s => s.StudentAddress).Load() loads the StudentAddress entity. The Reference() method is used to get an object of the specified reference navigation property and the Load() method loads it explicitly.
In the same way, context.Entry(student).Collection(s => s.Courses).Load() loads the collection navigation property Courses of the Student entity. The Collection() method gets an object that represents the collection navigation property.
The Load() method executes the SQL query in the database to get the data and fill up the specified reference or collection property in the memory, as shown below.
Query()
You can also write LINQ-to-Entities queries to filter the related data before loading. The Query() method enables us to write further LINQ queries for the related entities to filter out related data.
using (var context = new SchoolContext())
{
var student = context.Students
.Where(s => s.FirstName == “Bill”)
.FirstOrDefault();
context.Entry(student) .Collection(s => s.StudentCourses) .Query() .Where(sc => sc.CourseName == "Maths") .FirstOrDefault(); } In the above example, .Collection(s => s.StudentCourses).Query() allows us to write further queries for the StudentCourses entity.
Entity Framework Core: Saving Data in Connected Scenario
Entity Framework Core provides different ways to add, update, or delete data in the underlying database. An entity contains data in its scalar property will be either inserted or updated or deleted based on its EntityState.
There are two scenarios to save an entity data: connected and disconnected. In the connected scenario, the same instance of DbContext is used in retrieving and saving entities, whereas this is different in the disconnected scenario. In this chapter, you will learn about saving data in the connected scenario.
As per the above figure, Entity Framework builds and executes INSERT, UPDATE, or DELETE statements for the entities whose EntityState is Added, Modified, or Deleted when the DbContext.SaveChanges() method is called. In the connected scenario, an instance of DbContext keeps track of all the entities and so it automatically sets an appropriate EntityState of each entity whenever an entity is created, modified, or deleted.
Insert Data
The DbSet.Add and DbContext.Add methods add a new entity to a context (instance of DbContext) which will insert a new record in the database when you call the SaveChanges() method.
using (var context = new SchoolContext()) { var std = new Student() { FirstName = "Bill", LastName = "Gates" }; context.Students.Add(std);
// or // context.Add(std);
context.SaveChanges(); } In the above example, context.Students.Add(std) adds a newly created instance of the Student entity to a context with Added EntityState. EF Core introduced the new DbContext.Add method, which does the same thing as the DbSet.Add method. After this, the SaveChanges() method builds and executes the following INSERT statement to the database.
exec sp_executesql N’SET NOCOUNT ON;
INSERT INTO [Students] ( [FirstName], [LastName])
VALUES (@p0, @p1);
SELECT [StudentId]
FROM [Students]
WHERE @@ROWCOUNT = 1 AND [StudentId] = scope_identity();’,N
‘@p0 nvarchar(4000), @p1 nvarchar(4000) ‘,@p0=N’Bill’,@p1=N’Gates’
go
Updating Data
In the connected scenario, EF Core API keeps track of all the entities retrieved using a context. Therefore, when you edit entity data, EF automatically marks EntityState to Modified, which results in an updated statement in the database when you call the SaveChanges() method.
using (var context = new SchoolContext())
{
var std = context.Students.First();
std.FirstName = “Steve”;
context.SaveChanges();
}
In the above example, we retrieve the first student from the database using context.Students.First(). As soon as we modify the FirstName, the context sets its EntityState to Modified because of the modification performed in the scope of the DbContext instance (context). So, when we call the SaveChanges() method, it builds and executes the following Update statement in the database.
exec sp_executesql N’SET NOCOUNT ON;
UPDATE [Students] SET [FirstName] = @p0
WHERE [StudentId] = @p1;
SELECT @@ROWCOUNT;
‘,N’@p1 int,@p0 nvarchar(4000)’,@p1=1,@p0=N’Steve’
Go
In an update statement, EF Core API includes the properties with modified values, the rest being ignored. In the above example, only the FirstName property was edited, so an update statement includes only the FirstName column.
Deleting Data
Use the DbSet.Remove() or DbContext.Remove methods to delete a record in the database table.
using (var context = new SchoolContext()) { var std = context.Students.First(); context.Students.Remove(std);
// or // context.Remove(std);
context.SaveChanges(); } In the above example, context.Students.Remove(std) or context.Remove(std) marks the std entity object as Deleted. Therefore, EF Core will build and execute the following DELETE statement in the database.
exec sp_executesql N'SET NOCOUNT ON; DELETE FROM [Students] WHERE [StudentId] = @p0; SELECT @@ROWCOUNT; ',N'@p0 int',@p0=1 Go Thus, it is very easy to add, update, or delete data in Entity Framework Core in the connected scenario.
Repository pattern
it is an abstraction of the data access layer
it hides the details of how the data is exactly saved or retrieved from the underlying data souces
EFcore packages to be installed
Microsoft.EntityFrameworkCore.sqlserver
Microsoft.entityFrameworkCore.Design
Microsoft.EntityFrameworkcore.Tools
EF core need ID/Primary key
we need add attribute [key]
EF Core attributes
[Required] ; [Column(TypeName=”decimal(18,2)”)]
Navigation properties - 1 to many relationships on model
public ICollection productOrders {get;set;}
public Customer customer{get;set;} - in this case we might need to create a property of foreign key public int CustomerId {get;set;}. if we do not create it entity framework creates it as a shadow propery
DbContext - this typically goes into folder called Data
it is a session with the database
public Dbset Customer{get;set;} - this is table
we override the onConfiguration method and use extennstion method of use.SqlServer, we pass in the connection string
all this thing is entity model
Migrations
PM>Add-Migration initialCreate(name of migration)
this creates Migrations in the migrations folder; it generates all the code for the tables creation
once you verify it
we could run the migration
PM>Update-Database
it creates _EFMigrationHistory table to keep track of all the migrations run on this database
if we want to add a column like email. update the model
then
PM>Add-Migration AddEmail ; this creates a new migration for adding column
PM>Update-Database; to update database with the migrations
using HandHygineContecxt = new HandHygieneContext()
this using makes that it ensure that the context is cleared when we finished using it
adding data to the tables
context.products.Add(object);
or
context.Add(object); //EF knows it is product object
context.SaveChanges();
Fluent api
use extension method that chain the methods together and lambda expressions to specify the query
context.Products.Where(p => p.Price >= 5.00m)
.OrderBy(P=>P.name);
we could use linq syntax
from products in context.Products
where product.price >5
orderby product.name
select product;
update data
var squekybone= context.products.Where(P=>P.Name=="squeakbone") .FirstOrDefault(); if (squekybone is Product) { squekybone.Price=7.99m; } context.SaveChanges();
or
_context.Attach(Product).State = EntityState.Modified;
await _context.SaveChangesAsync()
delete record from database
var squekybone= context.products.Where(P=>P.Name=="squeakbone") .FirstOrDefault(); if (squekybone is Product) { context.Remove(squekybone); } context.SaveChanges();
we pass reference to the entity
EF Core Database First approach
PM> Scaffold-DbContext “Server=.\SQLExpress;Database=SchoolDB;
Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
-Context ContosoPetsContext
we could specify the output directory and context name
with scaffolding we have the whole entity data model
the attributes are contained in the OnModelCreating method of the database context
Db context and data annotations
PM> Scaffold-DbContext “Server=.\SQLExpress;Database=SchoolDB;
Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
-Context ContosoPetsContext -DataAnnotations
with this onModelCreating() method does not have the data annotaions only the model have them
to use EF core Entity model with asp.net core
we first register context with asp.net core dependency injection container
in startup.cs:
configureServices method
services.AddDbContext(options => options.UseSqlServer(Configuration.
GetConnectionString(“ERStepsConnectionString”)));
do not hard code the connection string
using Razor pages for CRUD - scaffolding
razer pages using Entity Framework (CRUD)
selecte product as the model class and select the context class visual studio create index, create, edit, delete,details methods
we inject the context in the constructor
Private readonly ContosPetsContext _context;
public constructor(ContosPetsContext context) { _context= context; }
asp.net core dependency injection container takes care of this for us as long as we have constuctor with the right signature
saving changes async
await _context.SaveChangesAsync();
firstorDefaultAsync
firstorDefaultAsync
sqllite
open souce, cross platform embeded database technologies
Entity Framework core for Azure Cosmos DB
Azure Cosmos DB - it is cloud based, no sql and distributed database
instead of like relational database system, it works with json documents
package - Microsoft.EntityFrameworkCore.Cosmos
and Desing and tools
services.AddDbContext(options => options.UseCosmos(Configuration.
GetConnectionString(“ERStepsConnectionString”)))
cosmos Db, it does not generate primary key autogenerated int
we change the enity model, to be sting with NewGuid
public string Id {get;set;} = Guid.NewGuid().ToString();
guid; globally unique identifier
cosmos Db:
it also has dbcontext and the models
.Net Core and Ef Core
these are both rewrites to previous ones; they are platform independent
Microsoft.EntityFrameCore.Design
it is used for scaffolding and migrations
Entity framework core
it has ado.net running behind the scenes; it is all plumbing work that EF core does for us
EF Core - ORM - it is to have domain in code; similar to what business needs; with out worrying about the tables and architecture
EF Core - ORM - it is to have domain in code; similar to what business needs; with out worrying about the tables and architecture
EF 5
it is the upcoming one in .Net 5 in Nov 2020
.net core global tools; to install Entity framework tool
dotnet install global
global tools in .net core, they are similar to putting something into the GAC; we could run it from anywhere
dotnet tool install –global dotnet-ef
.net ef command line tool
dotnet ef dbcontext scaffold “server=” Microsoft.EntityFrameworkCore.sqlserver -d -c AWDbcontext –context-dir EfStructures -o Entities
puts models in entities folder
put db context in EFStructures
dbContext
these have DbSet<address> - these are classes per table
this is object relational mapping
Address object maps to Address relational table
we gave dependency injection
public AwDBContext (DbContextOptions options):base() ; we configure the Dbcontext here
we are setting connection string by injecting them into options
</address>
entities created
[Table("Address",schema="person")] public partial class Address { } we could map to different table
we could change the column names
[Column(name:”StateProvinceID”)] - sql server
public int StateProvinceId {get;set;} - .net preference
in sqlserver - it is not case sensitive - unless you specify but c# is case sensitive
in c# we do not like two capitables together
OnConfiguring method; we normally comment it
it is a fall back mechanisim;
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { #warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings. optionsBuilder.UseSqlServer("Server=.\\SQLExpress; Database=db_core_sp_call;Trusted_Connection=True;"); } } if options builder is not configured then we will go ahead and configure for you
but if you forget to configure in prod environment, it connects to dev environment
OnModelCreating method
we could change the table name, schema, table name,column name in the Dbset
this has more mapping details
on Dbset - foreign key or Navigation propery
this is navigation property on the Person table
[ForeignKey(nameof(BusinessEntityId))]
[InverseProperty(“Person”)] - it says Foreign key for Business entity table
public virtual BusinessEntity BusinessEntity {get;set;}
one to Many
[InverseProperty(“Person”)]
public virtual ICollection BusinessEntityContact {get;set;}
we could have plural names for the collections
the Dbset class has bunch of methods like
AddRange
UpdateRange
Dbcontext does not know its connection string, it does not have knowledge on the provider
Dbcontext does not know its connection string, it does not have knowledge on the provider
DbContext is the master control program of EF Core
DbContext is the contract hired to connect to the database
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
namespace EfCore.EfStructures { public class AwDbContextFactory : IDesignTimeDbContextFactory { //This is only used by the design time tooling CLI public AwDbContext CreateDbContext(string[] args) { var optionsBuilder = new DbContextOptionsBuilder(); var connectionString = @"Server=(localdb)\mssqllocaldb;Database=Adventureworks 2016;Trusted_Connection=true;"; optionsBuilder .UseSqlServer(connectionString, options => options.EnableRetryOnFailure()); //Console.WriteLine(connectionString); return new AwDbContext(optionsBuilder.Options); } } }
we define options builder and use sql server and specify the connection string
we are setting to EnableRetryOnFailure for 5 times trying
this is used when using migrations to create the database
Change Tracker
when we pull something from the database and load into dbset; then it gets loaded into changeTracker;
ChangeTracker is going to have a proxy around it; it keeps track of what is the curernt value of the Dbset and what was the initial value of Dbset
when we call saveChagnes; it is the trigger point to the DbContext
change tracker reports to the Dbcontext here is all the changed, here is what i am tracking
DbContext talks to the database provider like sqlserver and generates the sql necessary to persist that data to the database
SaveChanges method is by default transactional
SaveChanges method is by default transactional
if we add 2 records, delete 5, change 6 records;
it could across any table within the dbcontext
when we call SaveChanges, a transaction gets opened
SQL gets executed to get all the things that we asked you to do, it checks to make sure that everything worked, if anything failed, it will rollback the entire transaction
if everything worked it will commit the transaction
then the server side such as row version or id in case of sequence will then get populated on the change tracker in the dbset and now we have an updated set of value; in memory is matching the database tables
EntityEntry -
intial Detatched Unchanged -get Added - add Deleted -delete Modified -update
we could also get the modified property
_context.ChangeTracker.Entries().First();
object in dbContext land referred to
namespace EfCore { public class ChangeTracking { private AwDbContext _context = null;
public ChangeTracking() { }
public void RunSamples() { ResetContext(); Console.WriteLine("*** Create new entity ***"); var person = new Person { AdditionalContactInfo = "Home", FirstName = "Barney", LastName = "Rubble", Title = "Neighbor" }; var newEntityEntry = _context.Entry(person); DisplayEntityStatus(newEntityEntry);
//Don't need to reset context since context is still clean //ResetContext(); DisplayEntityStatus(GetEntity());
ResetContext(); DisplayEntityStatus(AddEntity(person)); ResetContext(); DisplayEntityStatus(DeleteEntity()); ResetContext(); EntityEntry entry = EditEntity(); DisplayEntityStatus(entry); DisplayModifiedPropertyStatus(entry); } internal EntityEntry AddEntity(Person person) { Console.WriteLine("*** Add Entity *** "); _context.Person.Add(person); return _context.ChangeTracker.Entries().First(); } internal EntityEntry DeleteEntity() { Console.WriteLine("*** Delete Entity *** "); var person = _context.Person.Find(1); //This isn't in memory -> retrieved from database _context.Entry(person).State = EntityState.Deleted; //This mus be in memory -> retrieved from database _context.Person.Remove(person); _context.SaveChanges(); return _context.ChangeTracker.Entries().First(); } internal EntityEntry EditEntity() { Console.WriteLine("*** Edit Entity *** "); var person = _context.Person.Find(2); person.LastName = "Flinstone"; _context.Person.Update(person); _context.SaveChanges(); return _context.ChangeTracker.Entries().First(); }
internal EntityEntry GetEntity() { Console.WriteLine("*** Get Entity *** "); var person = _context.Person.Find(1); var person2 = _context.Person.Where(x => x.BusinessEntityId == 5).AsNoTracking(); return _context.ChangeTracker.Entries().First(); }
private void DisplayEntityStatus(EntityEntry entry) { Console.WriteLine($"Entity State => {entry.State}"); } private void DisplayModifiedPropertyStatus(EntityEntry entry) { Console.WriteLine("*** Changed Properties"); foreach (var prop in entry.Properties .Where(x => !x.IsTemporary && x.IsModified)) { Console.WriteLine( $"Property: {prop.Metadata.Name}\r\n\t Orig Value: {prop.OriginalValue}\r\n\t Curr Value: {prop.CurrentValue}"); } }
private void ResetContext() { _context = new AwDbContextFactory().CreateDbContext(null); } } }
change tracker keeps tracker of everything; if we have item has 200 properties or 1000 rows then it might be overhead on inmemory
change tracker keeps tracker of everything; if we have item has 200 properties or 1000 rows then it might be overhead on inmemory
it creates memory pressure because of the change tracking
it might not make much different if small app
removing from change tracker - if it is just for readonly or huge amount of data
For get for huge data set we could have asNoTracking
for Post then we are tracking with change tracker with EF core and save the data
var person2 = _context.Person.Where(x => x.BusinessEntityId == 5).AsNoTracking();
Query types or the items that do not have a primary key
we have a special DbSet, we tell it that it does not have a primary key and we do not have to call no tracking on it; it automatically does not track
this is useful for views, stored procedure or from sql to pull back data where linq may be difficult to write but write joins
Query types or the items that do not have a primary key
we have a special DbSet, we tell it that it does not have a primary key and we do not have to call no tracking on it; it automatically does not track
this is useful for views, stored procedure or from sql to pull back data where linq may be difficult to write but write joins
certain triggers that let query to be executed against the data and they do not happen immediately
when we create a linq query in EF core nothing happens
AsQueryable() - this tells EF Core that at somepoint of time, you are going to run a query against this
query exectues:
when it is iterated over (like foreach)
if you call ToList()
when you bind to a datagrid
Order by, OrderByDescending, Thenby for next order
we could have as many order by as we want
public void SortDataServerSide()
{
IOrderedQueryable query1 =
_context.Person.OrderBy(x => x.PersonType).ThenBy(x => x.EmailPromotion);
IOrderedQueryable query2 =
_context.Person.OrderByDescending(x => x.PersonType).ThenBy(x => x.EmailPromotion);
}
C# functions that translate to SQL; sql translation engine within SQL server provider
IsDate - Translates into IsDate T-sql function
public void UsingCSharpLikeFunction() { List list = new List { 1, 3, 5 }; var query = _context.Person.Where(x => list.Contains(x.BusinessEntityId)); _ = _context.Person.Where(x => x.LastName.Contains("UF")); _ = _context.Person.Where(x => EF.Functions.Like(x.LastName,"%UF%")); //IsDate translates to the TSQL IsDate function _ = _context.Person.Where(x => EF.Functions.IsDate(x.ModifiedDate.ToString())); decimal sum = _context.Product.Sum(x => x.ListPrice); int count = _context.Product.Count(x => x.ListPrice != 0); decimal avg = _context.Product.Average(x=>(decimal?)x.ListPrice)??0; decimal max = _context.Product.Max(x => (decimal?)x.ListPrice) ?? 0; decimal min = _context.Product.Min(x => (decimal?)x.ListPrice) ?? 0; bool any = _context.Product.Any(x => x.ListPrice != 0); bool all = _context.Product.All(x => x.ListPrice != 0); }
firstOrDefault it is linq thing
get me the first one, if they are not any return the default data type for that type like may be null
Find - first checks the change tracker to see if the dbset is already loaded, if not it will go to database; this could be slight memory boost unlike where statment that goes to db first
internal void GetByPrimaryKey() { //Get by primary key with immediate execution _ = _context.Person.Find(1); //Complex PK with immediate execution _ = _context.ProductVendor.Find(2, 1688); }
linq - it is the same linq with built in extension for entity framework core
linq - it is the same linq with built in extension for entity framework core
include - does left outer join
it generates iIncludableQueryable
ThenInclude - for joining on the already joined
include - does left outer join
we do not need to worry about the types returned just use var
internal void GetPersonAndRelatedData() { //Get collections (many of many to one) _ = _context.Person.Include(x => x.EmailAddress); //Get Parent (one of one to one) _ = _context.Person.Include(x => x.BusinessEntity); //Get Chain of related var q = _context.Person .Include(x => x.Employee) .ThenInclude(x => x.SalesPerson); q.ToList(); }
Resharper for more details about the code
Resharper for more details about the code
SelectMany flattens the list and gives all the emails that we need ;
but select gives list of list which might not be what we need
IQueryable> result1 = _context.Person.Select(x => x.EmailAddress); //Select Many flattens the list IQueryable result2 = _context.Person.SelectMany(x => x.EmailAddress);
IQuerable set up by EF Core to setup something that is eventually going to be quieried against database
IQuerable set up by EF Core to setup something that is eventually going to be quieried against database
we could use linq queries against collections and enumerables and list
Explicit transaction - instead of relying on implicit transaction of dbcontext.SaveChanges() method
public void AddAnItem()
{
ShouldExecuteInATransaction(AddNewPerson);
void AddNewPerson() { var person = new Person { AdditionalContactInfo = "Home", FirstName = "Barney", LastName = "Rubble", Title = "Neighbor" }; _context.Person.Add(person); _context.SaveChanges(); } }
public void ShouldExecuteInATransaction(Action actionToExecute) { using (var transaction = _context.Database.BeginTransaction()) { actionToExecute(); transaction.Rollback(); } }
dbContext.SaveChanges()
it creates sql statement like insert into table
and also executes any server side things like row version, computed columns and sequence like primary key
we get back the updated the rows in the table
it keeps the scope dbcontext in sync with the database changes
addRange() - list of items
public void AddItems()
{
ShouldExecuteInATransaction(AddNewPerson);
void AddNewPerson() { var list = new List { new Person { AdditionalContactInfo = "Home", FirstName = "Barney", LastName = "Rubble", Title = "Neighbor" }, new Person { AdditionalContactInfo = "Home", FirstName = "Barney", LastName = "Rubble", Title = "Neighbor" } }; _context.Person.AddRange(list); } }
delete an item with remove or setting delete state
_context.Entry(person).state=EntityState.deleted;
_Context.SaveChanges();
or
_Context.person.Remove(person);
_Context.SaveChanges();
delete an item with remove or setting delete state
_context.Entry(person).state=EntityState.deleted;
_Context.SaveChanges();
or
_Context.person.Remove(person);
_Context.SaveChanges();
update changes
_Context.person.Update(person);
_Context.SaveChanges();
EF 6 is ported to EF Core
EF 6 is ported to EF Core
converting default created DbContext with EF Core scaffolding to using the Dependency Injection container in asp.net core
Here we have single webapi project with EFCore and Controllers
comment the connection string in dbcontext.cs file: protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { // if (!optionsBuilder.IsConfigured) // { //#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings. // optionsBuilder.UseSqlServer ("Server=.\\SQLExpress;Database=db_core_sp_call; Trusted_Connection=True;"); // } }
Register with the Dependency injection container in startup.cs under ConfigureServices method
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext (x => x.UseSqlServer("Server=.\\SQLExpress; Database=db_core_sp_call;Trusted_Connection=True;"));
Inject in webapi controller constructor:
private readonly db_core_sp_callContext dbContext;
public WeatherForecastController
(ILogger logger, db_core_sp_callContext dbcontextobj)
{
_logger = logger;
dbContext = dbcontextobj;
}
using in the webapi method:
return dbContext.MMeasureColumn.Where
(x=>x.DimMeasureIdSk==2).OrderBy
(x=>x.ColumnOrder).ToList();
DbContext pooling
https://neelbhatt.com/2018/02/27/
use-dbcontextpooling-to-improve-the-
performance-net-core-2-1-feature/
Resolving DbContext pooling issue
The DbContext of type ‘db_core_sp_callContext’ cannot be pooled because it does not have a single public constructor accepting a single parameter of type DbContextOptions.
The error message actually states what the issue is. It is because it does not have a single public constructor accepting a single parameter of type “DbContextOptions”.
If you have any public constructors apart from one that accepts “DbContextOptions”, you have to remove them or make them non-public in order to use context pooling.
There are restrictions on what can be done by overriding the OnConfiguring method. This is referenced in the documentation here but it isn’t explicit about what those restrictions are: https://docs.microsoft.com/en-us/ef/core/what-is-new/index#dbcontext-pooling
startup.cs
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContextPool (x => x.UseSqlServer("Server=.\\SQLExpress; Database=db_core_sp_call;Trusted_Connection=True;"));
Making parameter less constructor private
in dbcontext class:
{ private db_core_sp_callContext() { }
public db_core_sp_callContext(DbContextOptions options) : base(options) { }
setting up EF Core models on standard project
install the below packages on .net standard project
Microsoft.EntityFrameworkCore.sqlserver
Microsoft.entityFrameworkCore.Design
Microsoft.EntityFrameworkcore.Tools
PM>Scaffold-DbContext “Server=CEALLP01MS.chmcres.cchmc.org;Database=Datamart_OPPE;User ID=DatamartMainSa;Password=D2martMainS@;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context OPPEContext -ContextDir DbContextClass -DataAnnotations
we need Microsoft.EntityFrameworkCore.Design - this should be installed in webapi project
setting up complete Repository pattern for webapi and .net standard project with efcore
install the below packages on .net standard project
Microsoft.EntityFrameworkCore.sqlserver
Microsoft.entityFrameworkCore.Design
Microsoft.EntityFrameworkcore.Tools
PM>Scaffold-DbContext “Server=CEALLD01MS.chmcres.cchmc.org;Database=Datamart_OPPE;User ID=DatamartMainSa;Password=D2martMainS@;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context OPPEContext -ContextDir DbContextClass -DataAnnotations -Force
-Schema “dbo”,”dsp” - this was not needed
we need Microsoft.EntityFrameworkCore.Design - this should be installed in webapi project
add connection strings class in Core/standard project: namespace org.cchmc.oppe.core.Settings { public class ConnectionStrings { public readonly string ConnectionStringOptions = "ConnectionStrings";
public string OPPEDB { get; set; } } }
in web api start up .cs
private ConnectionStrings _ConnectionStringSettings;
public Startup(IConfiguration configuration) { _Configuration = configuration; _swaggerSettings = new SwaggerSettings(); _ConnectionStringSettings = new ConnectionStrings(); }
// This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) {
//reading connection string settings from Configuration files using options pattern _ConnectionStringSettings = _Configuration.GetSection(_ConnectionStringSettings .ConnectionStringOptions).Get();
//Register DbContextPool services.AddDbContextPool(options => options.UseSqlServer(_ConnectionStringSettings.OPPEDB));
//Register the Repository Interfaces with their implementations services.AddTransient();
}
we need to do the make the constuctor private for dbContext class to use DbContextPool: public partial class OPPEContext : DbContext { //setting the private modifier as there should be a single public constructor accepting a single parameter of type DbContextOptions for DbContextPool (Registering in webapi startup.cs) private OPPEContext() { }
public OPPEContext(DbContextOptions options) : base(options) { }
we are defining the AddDbContextPool - this registers with the dependency inject, so now we could use this dbContext class in the controller constructor and use in the action methods if needed but we want to loosely couple the database context with the webapi we just controller to depend on interface and not worry about the implementation
So we define interface and interfaceImplementions under Interface and DataLayerFolders in core/standard project