Entity Framework Core

Question 1

Entity Framework Core

Answer 1

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.

Question 2

EF Core Development Approaches

Answer 2

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.

Question 3

EF Core Database Providers

Answer 3

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

Question 4

Install Entity Framework Core

Answer 4

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

Question 5

for sql server

Answer 5

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

Question 6

Install EF Core Tools

Answer 6

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.

Question 7

to execute EF Core commands from .NET Core’s CLI

Answer 7

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

Question 8

Creating a Model for an Existing Database in Entity Framework Core

Answer 8

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.

Question 9

Scaffold-DbContext Command

Answer 9

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.

Question 10

get-help scaffold-dbcontext –detailed

Answer 10

Use the following command to get the detailed help on Scaffold-DbContext command:

PM> get-help scaffold-dbcontext –detailed

Question 11

The following is the generated Student entity class for the Student table.

Answer 11

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");
        });
    }
} }

Question 12

EF Core creates entity classes only for tables and not for StoredProcedures or Views.

Answer 12

EF Core creates entity classes only for tables and not for StoredProcedures or Views.

Question 13

dotnet ef dbcontext scaffold

Answer 13

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

Question 14

use the Migration commands whenever you change the model

Answer 14

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.

Question 15

Configuring a DbContext

Answer 15

https://docs.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext

Question 16

Entity Framework Core: DbContext

Answer 16

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.

Question 17

DbContext in EF Core allows us to perform following tasks

Answer 17

Manage database connection
Configure model & relationship
Querying database
Saving data to the database
Configure change tracking
Caching
Transaction managemen

Question 18

To use DbContext in our application

Answer 18

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.

Question 19

OnConfiguring() method

Answer 19

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.

Question 20

OnModelCreating() method

Answer 20

The OnModelCreating() method allows us to configure the model using ModelBuilder Fluent API.

Question 21

DbContext Methods

Answer 21

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.

Question 22

DbContext Properties

Answer 22

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.

Question 23

Creating the Model

Answer 23

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.

Question 24

Code first appraoch -create entity classes and context classes first

Answer 24

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.

Question 25

"Server=.\SQLEXPRESS;Database=SchoolDB; | Trusted_Connection=True;"

Answer 25

Trusted_Connection=True specifies the Windows authentication mode

Question 26

After creating the context and entity classes, it's time to add the migration to create a database.

Answer 26

After creating the context and entity classes, it's time to add the migration to create a database.

Question 27

Adding a Migration = to create database from the code first approach

Answer 27

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.

Question 28

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.

Answer 28

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.

Question 29

Reading or Writing Data

Answer 29

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(); } } } }

Question 30

Querying in Entity Framework Core

Answer 30

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.

Question 31

C#/VB.NET Functions in Queries

Answer 31

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

Question 32

Eager Loading with Include

Answer 32

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'

Question 33

Include() method, same as in EF 6 - Not recommended using lambda instead

Answer 33

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.

Question 34

Include from sql

Answer 34

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();

Question 35

Include() extension method cannot be used after the DbSet.Find()

Answer 35

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.

Question 36

Multiple Include

Answer 36

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

Question 37

ThenInclude

Answer 37

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

Question 38

Projection Query

Answer 38

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.

Question 39

Lazy Loading

Answer 39

Lazy loading is not supported in Entity Framework Core 2.0. Track lazy loading issue on github.

Question 40

Explicit Loading

Answer 40

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.

Question 41

Query()

Answer 41

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.

Question 42

Entity Framework Core: Saving Data in Connected Scenario

Answer 42

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.

Question 43

Insert Data

Answer 43

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

Question 44

Updating Data

Answer 44

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.

Question 45

Deleting Data

Answer 45

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. ```

Question 46

Repository pattern

Answer 46

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

Question 47

EFcore packages to be installed

Answer 47

Microsoft.EntityFrameworkCore.sqlserver Microsoft.entityFrameworkCore.Design Microsoft.EntityFrameworkcore.Tools

Question 48

EF core need ID/Primary key

Answer 48

we need add attribute [key]

Question 49

EF Core attributes

Answer 49

[Required] ; [Column(TypeName="decimal(18,2)")]

Question 50

Navigation properties - 1 to many relationships on model

Answer 50

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

Question 51

DbContext - this typically goes into folder called Data

Answer 51

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

Question 52

Migrations

Answer 52

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

Question 53

using HandHygineContecxt = new HandHygieneContext()

Answer 53

this using makes that it ensure that the context is cleared when we finished using it

Question 54

adding data to the tables

Answer 54

context.products.Add(object); or context.Add(object); //EF knows it is product object context.SaveChanges();

Question 55

Fluent api

Answer 55

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;

Question 56

update data

Answer 56

``` 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()

Question 57

delete record from database

Answer 57

``` 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

Question 58

EF Core Database First approach

Answer 58

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

Question 59

Db context and data annotations

Answer 59

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

Question 60

to use EF core Entity model with asp.net core

Answer 60

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

Question 61

using Razor pages for CRUD - scaffolding

Answer 61

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

Question 62

saving changes async

Answer 62

await _context.SaveChangesAsync();

Question 63

firstorDefaultAsync

Answer 63

firstorDefaultAsync

Question 64

sqllite

Answer 64

open souce, cross platform embeded database technologies

Question 65

Entity Framework core for Azure Cosmos DB

Answer 65

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

Question 66

.Net Core and Ef Core

Answer 66

these are both rewrites to previous ones; they are platform independent

Question 67

Microsoft.EntityFrameCore.Design

Answer 67

it is used for scaffolding and migrations

Question 68

Entity framework core

Answer 68

it has ado.net running behind the scenes; it is all plumbing work that EF core does for us

Question 69

EF Core - ORM - it is to have domain in code; similar to what business needs; with out worrying about the tables and architecture

Answer 69

EF Core - ORM - it is to have domain in code; similar to what business needs; with out worrying about the tables and architecture

Question 70

EF 5

Answer 70

it is the upcoming one in .Net 5 in Nov 2020

Question 71

.net core global tools; to install Entity framework tool

Answer 71

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

Question 72

.net ef command line tool

Answer 72

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

Question 73

dbContext

Answer 73

these have DbSet

- 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

Question 74

entities created

Answer 74

``` [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

Question 75

OnConfiguring method; we normally comment it

Answer 75

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

Question 76

OnModelCreating method

Answer 76

we could change the table name, schema, table name,column name in the Dbset this has more mapping details

Question 77

on Dbset - foreign key or Navigation propery

Answer 77

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

Question 78

the Dbset class has bunch of methods like

Answer 78

AddRange | UpdateRange

Question 79

Dbcontext does not know its connection string, it does not have knowledge on the provider

Answer 79

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

Question 80

Change Tracker

Answer 80

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

Question 81

SaveChanges method is by default transactional

Answer 81

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

Question 82

EntityEntry - ``` intial Detatched Unchanged -get Added - add Deleted -delete Modified -update ``` we could also get the modified property _context.ChangeTracker.Entries().First();

Answer 82

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); } } } ```

Question 83

change tracker keeps tracker of everything; if we have item has 200 properties or 1000 rows then it might be overhead on inmemory

Answer 83

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

Question 84

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

Answer 84

var person2 = _context.Person.Where(x => x.BusinessEntityId == 5).AsNoTracking();

Question 85

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

Answer 85

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

Question 86

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

Answer 86

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

Question 87

Order by, OrderByDescending, Thenby for next order we could have as many order by as we want

Answer 87

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); }

Question 88

C# functions that translate to SQL; sql translation engine within SQL server provider

Answer 88

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); } ```

Question 89

firstOrDefault it is linq thing

Answer 89

get me the first one, if they are not any return the default data type for that type like may be null

Question 90

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

Answer 90

``` internal void GetByPrimaryKey() { //Get by primary key with immediate execution _ = _context.Person.Find(1); //Complex PK with immediate execution _ = _context.ProductVendor.Find(2, 1688); } ```

Question 91

linq - it is the same linq with built in extension for entity framework core

Answer 91

linq - it is the same linq with built in extension for entity framework core

Question 92

include - does left outer join it generates iIncludableQueryable ThenInclude - for joining on the already joined

Answer 92

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(); } ```

Question 93

Resharper for more details about the code

Answer 93

Resharper for more details about the code

Question 94

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

Answer 94

``` IQueryable> result1 = _context.Person.Select(x => x.EmailAddress); //Select Many flattens the list IQueryable result2 = _context.Person.SelectMany(x => x.EmailAddress); ```

Question 95

IQuerable set up by EF Core to setup something that is eventually going to be quieried against database

Answer 95

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

Question 96

Explicit transaction - instead of relying on implicit transaction of dbcontext.SaveChanges() method

Answer 96

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(); } } ```

Question 97

dbContext.SaveChanges()

Answer 97

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

Question 98

addRange() - list of items

Answer 98

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); } } ```

Question 99

delete an item with remove or setting delete state _context.Entry(person).state=EntityState.deleted; _Context.SaveChanges(); or _Context.person.Remove(person); _Context.SaveChanges();

Answer 99

delete an item with remove or setting delete state _context.Entry(person).state=EntityState.deleted; _Context.SaveChanges(); or _Context.person.Remove(person); _Context.SaveChanges();

Question 100

update changes

Answer 100

_Context.person.Update(person); | _Context.SaveChanges();

Question 101

EF 6 is ported to EF Core

Answer 101

EF 6 is ported to EF Core

Question 102

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

Answer 102

``` 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();

Question 103

DbContext pooling

Answer 103

https://neelbhatt.com/2018/02/27/ use-dbcontextpooling-to-improve-the- performance-net-core-2-1-feature/

Question 104

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.

Answer 104

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) { } ```

Question 105

setting up EF Core models on standard project

Answer 105

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

Question 106

setting up complete Repository pattern for webapi and .net standard project with efcore

Answer 106

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