EF Core 2.0 Scaffolding existing Database

Hello Readers,

This post will be on using the scaffolding mechanism to create the DB Context and model classes for the existing databases.

Whenever we start projects we come up with requirements to modify existing components, based on the requirement and the expertise we choose our paths.

Most of the time the databases are already there so we go with DB first approach to create the context.
When it comes to EF Core this option is available there as well but will include a bit of command line work.

This article will explain a way to create a DB Context from an existing database by selecting only specific tables in EF Core.

The commands are pretty straight forward you just have to install some Nuget packages and run a command to generate the context and the classes to represent the tables.

The first set of commands will be used to install the packages.

Install-Package Microsoft.EntityFrameworkCore.Design
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.sqlserver.Design
Install-Package Microsoft.EntityFrameworkCore.Tools

Have these things in mind
If you are creating a library (.NETStandard) to do all your data access works and a references is not added to the startup project you will get the below error.  That is if you choose the .NETStandard project as the startup.

Startup project '{project name}' targets framework '.NETStandard'. There is no runtime associated with this framework, and projects targeting it cannot be executed directly. To use the Entity Framework Core Package Manager Console Tools with this project, add an executable project targeting .NET Framework or .NET Core that references this project, and set it as the startup project; or, update this project to cross-target .NET Framework or .NET Core.

If you are using a .NET Core library without adding a reference and run the scaffold command you will get the below error.

Unable to find provider assembly with name Microsoft.EntityFrameworkCore.SqlServer. Ensure the specified name is correct and is referenced by the project. 

Always remember to set your class library as the startup project when running the scaffold command.This will reduce unwanted errors.

The command to scaffold is below.

Scaffold-DbContext "Server=localhost;Database=DEVDB;User Id=user1;Password=test;" Microsoft.EntityFrameworkCore.SqlServer -Context DBContext -Tables "Employee","Address" -Verbose

Tables are a string array that you have to specify
 You can use the -Force command when updating or adding tables

Scaffold-DbContext  "Server=localhost;Database=DEVDB;User Id=user1;Password=test;"  Microsoft.EntityFrameworkCore.SqlServer -Context DBContext -Tables  "Employee","Address","WorkLocation" -Verbose -Force 

 

The verbose flag will provide you additional information and will help you when you have errors.

References
https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/powershell

Happy Coding
-Guru- 

Syncing tables using Merge in SQL Server

Hi Readers,

In this post I'll explain how to use the 'Merge' statement in sql server to Sync two tables.
For this we'll need two tables, one will act as the source and the other one will act as the destination.

First create the required tables

CREATE TABLE SourceTable
(
 ID INT IDENTITY PRIMARY KEY,
 Name VARCHAR(100)
);

CREATE TABLE DestinationTable
(
 ID INT PRIMARY KEY,
 Name VARCHAR(100)
);

Then insert some data to the source table

INSERT INTO SourceTable VALUES('George');
INSERT INTO SourceTable VALUES('Saman');
INSERT INTO SourceTable VALUES('Kamal');
INSERT INTO SourceTable VALUES('Nimal');
INSERT INTO SourceTable VALUES('Sunil');
INSERT INTO SourceTable VALUES('Mark');
INSERT INTO SourceTable VALUES('Bill');
INSERT INTO SourceTable VALUES('Paul');
INSERT INTO SourceTable VALUES('Sean');
INSERT INTO SourceTable VALUES('Shane');
INSERT INTO SourceTable VALUES('Elvis');

Now we'll come to the Merge Statement

MERGE DestinationTable D
USING SourceTable S
ON D.ID=S.ID 
WHEN MATCHED AND D.Name!=S.Name THEN UPDATE SET D.Name=S.Name
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT(ID,Name) VALUES(S.ID,S.Name);

As this is the first time , all the rows will be inserted
So we'll do some changes to the table (insert, update , delete)

DELETE FROM SourceTable WHERE Name='Bill';
UPDATE SourceTable SET Name='Mark2' WHERE Name= 'Mark';
INSERT INTO SourceTable VALUES('Alex');

Now if we run the merge statement again we can see that both the tables are synced.
You can schedule it to a job so that the tables will be in sync.