EF 2.0 I am a believer
EF Core 2.0 I am a believer
I am not much of a database guy, I enjoy code and am not a big fan of learning new \ old tools SQL Management Studio, SSIS, etc... not my forte.
So I am working on a project and exploring Database evolution. The original project started with EF 6 the team struggled with that as there was no experts on the team and we found that there were some limitations around relationships that were getting lost in the code. So they migrated the EF 6 to a database project and at some point people started updated the Database Directly and doing schema compares to get the diffs back into the code. Not a good flow in my opinion, as if you are a squirrel hunter looking for shiny objects and forget to compare you potentially impact the whole team.
In comes EF Core 2.0, and wow super simple way of taking an existing Database and bringing over at least the structure back to code first. The seeding of the data is currently not included in EF Core 2.0 but it is possible and I will walk you through that in a separate post.
So here are the steps I went through in this example I will use [Northwind Samples for SQL 2016]('https://www.microsoft.com/en-us/download/details.aspx?id=49502')
- Create a new dotnet Core MVC application
- Install the tools for EF Core 2.0
- Scaffold the database
Create a new dotnet core MVC app
Dotnet core command line tools have built in templates
Install the tools for EF Core 2.0
Open the app, here using VSCode but can be Visual Studio whatever. Code or Visual Studio should take care of the initial dotnet cor packages necessary for a web project. We will need the following packages
- Microsoft.EntityFrameworkCore
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.SqlServer.Design
- Microsoft.EntityFrameworkCore.Tools.DotNet (dotnet ef commands) or Microsoft.EntityFrameworkCore.Tools (powershell )
Run the following command for each of the aforementioned packages, ensuring the minimum version is 2.0.0
DotNet CLI
dotnet add package <packagename>
Powershell
Install-Package <packageName>
Looking for a correction here not sure how to add the tooling except by editing the csproj file directly it should look as follows:
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>netcoreapp2.0</TargetFramework>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.0" />
</ItemGroup>
<ItemGroup>
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
<DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.0" />
</ItemGroup>
</Project>
```
Finally Scaffolding the database
First up Scaffolding the database there is a command now called Scaffold-DbContext that does a great deal of the heavy lifting for you, creates your POCO (plain old code objects) and dbcontext along with any relationships, indexes etc...
Powershell
Scaffold-DbContext -Connection "Server=localhost;Database=AdventureWorks2016CTP3;Integrated Security=true;" -Provider Microsoft.EntityFrameworkCore.SqlServer -Context AdventureWorksCtx -OutputDir .\Models -DataAnnotations
Scaffold-DbContext comand we just ran gets all the POCO (Plain Old Code Objects) from the database, as well as the dbcontext and associated metadata about the tables and columns that the system can discern
Note The Scaffold presents us with a couple of issues
- Once the command is run you will get a verbose output
- Find the Context we referenced in the Scaffold command, here we called it AdventureWorksCtx
Just past the DbSets for each of the tables you will find two important pieces of information- The Tables that could not be created and why
- Below the table issues is the connection string we should make into either an Azure Secret or at a minimum move to a settings file
