Console Database Example using EF Core 8

Major Caveat: EntityFrameworkCore does not support a simple many-to-many relationship (as of 2/20/2022) nor does it do what you would expect and include the intersection table. Instead it creates code that causes a runtime error.

Fix: The fix is weirder than you might expect. You must include the intersection table on your own. But you might ask, can't I make the entity framework core six include the table for me. Why yes, YES YOU CAN. But it will take a bit of changing of your database. For me this required the altering of a single table in our database.

SQL Server:

   1 ALTER TABLE teaches
   2 ADD teacher_credit NUMERIC(3,2);

SQLite:

   1 ALTER TABLE teaches 
   2 ADD COLUMN teacher_credit NUMERIC(3,2);

What are we doing here? We are forcing EntityFramework6 to include a column that is not in the relationship. This forces the Framework to create the object.

There is much controversy relating to this problem in the previous entityframework (non-core). See: https://stackoverflow.com/questions/1367751/update-primary-key-value-using-entity-framework/6012040#6012040.

Creating a simple Example Console application

1. Create a .NET 8 Console application (that's .NET Core by default).

2. Next Open: Tools, NuGet Package Manager, Package Manager Console.

3. In the console type the following commands

As an asside, if you need to update a tool to a specific version or install a specific version, use the following commands.

#Check what version you have installed
dotnet ef --version
#To install a specific version add e.g. --version 8.0.2 to the lines below. 
dotnet tool install --global dotnet-ef
#Update to a specific version
dotnet tool update --global dotnet-ef 

#For the web project you will also need:
dotnet tool install --global dotnet-aspnet-codegenerator

SQL Server

As of Feb. 13, 2024 8.0.2 is the latest version and that is what will install if you don't put a version on it. However, on this date, that version requires an update to the .NET framework that does not exist. Hence, we use version 8.0.1.

dotnet add package Microsoft.EntityFrameworkCore 
dotnet add package Microsoft.EntityFrameworkCore.SqlServer 
dotnet add package Microsoft.EntityFrameworkCore.Tools 
dotnet add package Microsoft.EntityFrameworkCore.Design 
dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design #Only if you are using these directions for a Razor Pages Web App...
mkdir Models
dotnet ef dbcontext scaffold "Server=localhost;database=UniversitySmall;user id=sa; Password=ConstraintDB123;TrustServerCertificate=true" Microsoft.EntityFrameworkCore.SqlServer -c UniversityContext -o Models -f

Assuming of course that you are using Docker to host a SQL Server installation on localhost port 1433.

SQLite

First copy the .db file into your project. I made a separate folder for it called "Database"

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet ef dbcontext scaffold "DataSource=.\Database\UniversityLarge.db" Microsoft.EntityFrameworkCore.SQLite -c UniversityContext -o Database -f

MySql

Use the nuget package manager to add:

Pomelo.MySQL... need to complete this.

If you have already created a migration you can skip this step.

dotnet ef migrations add InitialCreate

Finally, you need to update the database schema:

dotnet ef database update

For more information see: https://docs.microsoft.com/en-us/ef/core/managing-schemas/scaffolding?tabs=dotnet-core-cli

Now to use the database

First make sure you project compiles. It did? Good!

Here is my code from program.cs