Entity FrameWork Core

[tnlthanzeel]

This section is to briefly introduce how to configure entity framework core to and ASP.NET Core project and do few CRUD operations.

Prerequisite: atleast worked with Entity Framework 6

Create a class library project as a Data Access layer project in your solution so that the Web project and the the database can be separated.

Install the following nuget packages from the nuget manager to have entity frame work correctly configured.

1. To get the fully supported version of EFCore with sql, install Microsoft.entityframeworkcore.sqlserver from the nuget. This will pull in all the dependencies.
   To see the diagram of the tables, install https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools (read documentation for mo info).

2. In order to execute migrations, install the following tool and do the migration using the package manager console. EF Core2 doesn’t come with all the EF tools like in EF 6. Not everybody will run migrations. So if u want to run migrations install the following tool.

Using nuget package manager, install: Microsoft.entityframeworkcore.tools

• When running migrations, make sure that the package manager console points to the right project, which is the Data Access project

• To get a list of commands of EFcore, type “get-help entityframeworkcore”

Cmdlet                      Description

• -------------------------- ---------------------------------------------------
• Add-Migration Adds a new migration.
•
• Drop-Database Drops the database.
•
• Get-DbContext Gets information about a DbContext type.
•
• Remove-Migration Removes the last migration.
•
• Scaffold-DbContext Scaffolds a DbContext and entity types for a database.
•
• Script-Migration Generates a SQL script from migrations.

3. Once you run the add-migration command, I will get an error like this:

• This is because the Data project and the Domain project are class library projects. The StartUp project must have the references to the Data and the Domain project. There fore, add the reference to the StartUp project and install the following nuget package to the StartUp Project.

o Microsoft.entityframeworkcore.design

• This is because the design project needs and executable project and not a class library project .
• Set the executable project as the startup project.
• Now run the add-migration command again and it will succeed Inshah Allah

Model SnapShot file
• The model snapshot file is very important because this is where entity framework will keep track of the current state of the model. When a new migration is added the ef core will read the snapshot and compare it with the new version of the model and figures out what needs to be changed.

Migrations
• To create a sql script of the migration, type the command

Runnin this command will generate a sql script like the example below

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
CREATE TABLE [__EFMigrationsHistory] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
END;

GO

CREATE TABLE [Battles] (
[Id] int NOT NULL IDENTITY,
[Name] nvarchar(max) NULL,
[StartDate] datetime2 NOT NULL,
[EndDate] datetime2 NOT NULL,
CONSTRAINT [PK_Battles] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Samurais] (
[Id] int NOT NULL IDENTITY,
[Name] nvarchar(max) NULL,
[BattleId] int NOT NULL,
CONSTRAINT [PK_Samurais] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Samurais_Battles_BattleId] FOREIGN KEY ([BattleId]) REFERENCES [Battles] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [Quotes] (
[Id] int NOT NULL IDENTITY,
[Text] nvarchar(max) NULL,
[SamuraiId] int NOT NULL,
CONSTRAINT [PK_Quotes] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Quotes_Samurais_SamuraiId] FOREIGN KEY ([SamuraiId]) REFERENCES [Samurais] ([Id]) ON DELETE CASCADE
);

GO

CREATE INDEX [IX_Quotes_SamuraiId] ON [Quotes] ([SamuraiId]);

GO

CREATE INDEX [IX_Samurais_BattleId] ON [Samurais] ([BattleId]);

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20181113023641_init', N'2.1.4-rtm-31024');

GO

• Update the databse using the update-database command from the package manager console, this will create a db in your mssql server if the db doesn't exist r update it.

One-to-Many and-Many-to-Many

• One samaurai can fight in many battles and many samurais can fight in one battles. There for many to many relationship.
• Create a new table (many to many relationship) as SamuriBattle.
• Create the many to many relationship relationship using the ef core fluent API in the SamuriContext file.
• Here, we have a composite primary key

Logging sql script created by Ef core

• install the package Microsoft.Extensions.Logging.Console to SamaruiAppData (the class libabry project for the db access) project

This code must be added to the App datacontext class which inherits the DbContext class so we can see the generated sql

Inserting a list of objects to databse using ef core 2

Inserting different types of objects to the database using AddRange() method in ef core 2

Usling Like() in ef core 2

• use the like() method instead of contains which will finally convert into a Like() method taking in a wild card.
• EF is a built in function

selecting the last recored using LastOrDefault()

• Here, the ef core will generate a sql ordered by decending and select the top.

• If orderBy() is not included, the sql will return a list to the memory and then will select the last record which is a performance hit.

Using the Find() method

• The Find method will return an object by taking in the primary key of the entity. If the entity is tracked by the context in will be return from the memory without doing a call to the database. Cool ha!

Disconnected Updates

• Here, there is no tracking of the entity by the dbcontext
• We can use the Update method to update the object without tracking what properties have been change.

• Here all though only the EndDate has been changed, the context object is different when updating the object.
• The Using statement will create a new context object and pass the entity to the Update method.
• All the properties will be updated, not just only the EndDate

Simplified methods

Deleting

• Delete can be done using the Remove() method if u have the entity object to pass into the Remove() function.
• If u have only the primary key, we can use a raw sql command to execute stored procedure.

Eager Loading

• The “ThenInclude()” method drills down into the Quotes which is in the “Include()” and retrieves that data as well

Projection

• The below query brings out only the samarui object without the Quotes. This query will look for any samurai that has the word ”happy ” in any of its quotes. Quotes is not included in the result set

Entry method

• When updating a related object when there is no tracking, use the method Entry() and set the entity state to modified as in the image above. This will tell the ef core to only update the modified related object. Not all the object.
• If we use the update method, it will update all the related objects even if there are no changes.

[Nubelle]

@tnlthanzeel Thanks Bro

[tnlthanzeel]

This post is deleted!

[tnlthanzeel]

This post is deleted!