EF Core scaffolding

Tags: EF Core

Wednesday, November 22, 2017 12:03:00 AM

There's no EDMX designer support for EF Core. Code first is possible but why would you doodle about like that when the database almost certainly already exists, and even if it doesn't SQL Management Studio has a designer. Not a very good one, to be sure, but it's better than no designer. 

Why am I so keen for MSSQL when SQLite is ready to go out of the box? My current project is a NoSQL document database affair. The cool kids will be excitedly chanting "Mongo" but Microsoft has a smackdown answer: FTI. Look it up. 

  1. Ensure you have the 2.0 SDK
  2. Install these packages. Versions are explicit. This is necessary.
    1. dotnet add package Microsoft.EntityFrameworkCore -v 2.0.0
    2. dotnet add package Microsoft.EntityFrameworkCore.Design -v 2.0.0
    3. dotnet add package Microsoft.EntityFrameworkCore.SqlServer -v 2.0.0
    4. dotnet add package Microsoft.EntityFrameworkCore.Tools.DotNet -v 2.0.0
  3. Then you have to manually hack the CSPROJ file of your project. If you already have an item group containing a DotNetCliToolReference just add this reference to it.
 <ItemGroup>
   <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
 </ItemGroup>
You are now in a position to scaffold. At a command prompt set the current folder to the root of the project for which you wish to scaffold and then execute the following command. The command as written uses a connection string for a database FOO on the default instance of a SQL Server running on the local machine. Adjust the connection string details to describe your situation. Depending on the configuration of your server you may need to specify uid and pwd instead of integrated security.
dotnet ef dbcontext scaffold -o Models -f -c FooDbContext "server=localhost;database=foo;Integrated Security=True;" Microsoft.EntityFrameworkCore.SqlServer

Specifying integrated security is necessary. For some reason it isn't the default. The -o Models clause is optional and indicates that the created file should be place in ./Models. The -f option means force, causing replacement of any existing file in the event of a name collision, and finally -c FooDbContext means the generated context file should be named FooDbContext.cs.  Finally we have a connection string and a provider name. 

Specifying a folder with the -o foldername option is a good idea because in addition to the database context class, the model you are generating will contain a class for every table in your schema. The folder will be created if it doesn't already exist.

This will leave you with an embedded connection string; moving it to your configuration file is a separate problem.

At the time of writing (end of 2017) putting a connection string into your app entails an entry in appsettings.json. The ConnectionStrings object should be a child of the root, and its properties are your connection strings. Here I have only one entry, mysteriously named DefaultConnection. which connects to the database foo served by a default instance of SQL Server, running on the local machine. As mentioned above integrated security is in use, so there is no need to embed credentials into the connection string, but it is necessary to specify a trusted connection.

  "ConnectionStrings": {
    "DefaultConnection": "Server=(local);Database=foo;Trusted_Connection=True;"
  },

No Comments