Console Database Example using EF Core 6

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,12);

SQLite:

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

What are we doing here? We are forcing EntityFramework6 to

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 6 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.

Hint: Do you need to update a tool?

dotnet tool update --global dotnet-ef

SQL Server

dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.SqlServer.Design
dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design
mkdir Models
dotnet ef dbcontext scaffold "Server=localhost;database=UniversitySmall;user id=sa; Password=ConstraintDB123" 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

Now to use the database

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

Let's do something simple like list all the students names along with their major:

   1 var context = new UniversityContext();
   2 var students = context.Students.ToList();
   3 foreach (var student in students)
   4 {
   5     Console.WriteLine($"Name = {student.Name}, Major = {student.DeptName}");
   6 }
   7 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");

No let's include the classes for each student, along with the course title, nicely ordered:

   1 var studentclasses = context.Students
   2     .Include(s => s.Takes)
   3         .ThenInclude(t => t.Section)
   4             .ThenInclude(e => e.Course)
   5     .OrderBy(s => s.Name);
   6 foreach (var sc in studentclasses)
   7 {
   8     Console.WriteLine($"STUDENT: {sc.Name}, Major: {sc.DeptName}");
   9     var takes = sc.Takes.OrderBy(x => x.Year).ThenBy(y => y.Semester);
  10     foreach (var ta in takes)
  11     {
  12         Console.WriteLine($"   {ta.Year} - {ta.Semester}: {ta.Section.Course.Title}");
  13     }
  14 }
  15 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");

Let's filter the afore mentioned query for those with 80 or more credits.

   1 var studentclasses2 = context.Students
   2     .Include(s => s.Takes)
   3         .ThenInclude(t => t.Section)
   4             .ThenInclude(e => e.Course)
   5     .Where(s => s.TotCred >= 80)
   6     .OrderBy(s => s.Name);
   7 foreach (var sc in studentclasses2)
   8 {
   9     Console.WriteLine($"STUDENT: {sc.Name}, Major: {sc.DeptName}, Credits: {sc.TotCred}");
  10     var takes = sc.Takes.OrderBy(x => x.Year).ThenBy(y => y.Semester);
  11     foreach (var ta in takes)
  12     {
  13         Console.WriteLine($"   {ta.Year} - {ta.Semester}: {ta.Section.Course.Title}");
  14     }
  15 }
  16 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");