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 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. (The issue is a composite primary key of a table acting as a foreign key in an intersection table. This appears to have been broken since version 6. Interestingly enough, making it an association table will tweak it enough to work - NO YOU WOULDN'T DO THAT IN A REAL DATABASE).

Are you looking for the Razor Pages tutorial?

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

   1 using CPTR319_DB_Example_EF.Models;
   2 using Microsoft.EntityFrameworkCore;
   3 
   4 
   5 //List student names along with their majors
   6 var context = new UniversityContext();
   7 var students = context.Students.ToList();
   8 foreach (var student in students)
   9 {
  10     Console.WriteLine($"Name = {student.Name}, Major = {student.DeptName}");
  11 }
  12 
  13 //List student names along with their classes ordered nicely by year, semester...
  14 var studentclasses = context.Students
  15     .Include(s => s.Takes)
  16         .ThenInclude(t => t.Section)
  17             .ThenInclude(e => e.Course)
  18     .OrderBy(s => s.Name);
  19 foreach (var sc in studentclasses)
  20 {
  21     Console.WriteLine($"STUDENT: {sc.Name}, Major: {sc.DeptName}");
  22     var takes = sc.Takes.OrderBy(x => x.Year).ThenBy(y => y.Semester);
  23     foreach (var ta in takes)
  24     {
  25         Console.WriteLine($"   {ta.Year} - {ta.Semester}: {ta.Section.Course.Title}");
  26     }
  27 }
  28 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");
  29 
  30 //Let's filter the afore mentioned query for those with 80 or more credits.
  31 var studentclasses2 = context.Students
  32     .Include(s => s.Takes)
  33         .ThenInclude(t => t.Section)
  34             .ThenInclude(e => e.Course)
  35     .Where(s => s.TotCred >= 80)
  36     .OrderBy(s => s.Name);
  37 foreach (var sc in studentclasses2)
  38 {
  39     Console.WriteLine($"STUDENT: {sc.Name}, Major: {sc.DeptName}, Credits: {sc.TotCred}");
  40     var takes = sc.Takes.OrderBy(x => x.Year).ThenBy(y => y.Semester);
  41     foreach (var ta in takes)
  42     {
  43         Console.WriteLine($"   {ta.Year} - {ta.Semester}: {ta.Section.Course.Title}");
  44     }
  45 }
  46 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");
  47 
  48 //Let's count the number of classes taken by students with 80 or more credits 
  49 var studentclasses3 = context.Students
  50     .Include(s => s.Takes);
  51 foreach (var sc in studentclasses3)
  52 {
  53     Console.WriteLine($"Name: {sc.Id}->{sc.Name}, Major: {sc.DeptName}, Hours: {sc.TotCred}, Classes: {sc.Takes.Count()}");
  54 }
  55 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");
  56 
  57 //Althernative ways of doing this using Link to SQL 
  58 var list = from o in context.Students
  59            where o.TotCred >= 80
  60            from t in o.Takes
  61            let foo = new
  62            {
  63                Name = o.Name,
  64                Id = o.Id,
  65                Major = o.DeptName,
  66                Hours = o.TotCred,
  67                Classes = o.Takes.Count()
  68            }
  69            orderby foo.Name, foo.Classes descending
  70            select foo;
  71 
  72 foreach (var l in list)
  73 {
  74     Console.WriteLine($"Name: {l.Id}->{l.Name}, Major: {l.Major}, Hours: {l.Hours}, Classes: {l.Classes}");
  75 }
  76 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");
  77 
  78 var deepsum = context.Students.Include(s => s.Takes).ThenInclude(t => t.Section).ThenInclude(c => c.Course)
  79                       .Where(c => c.DeptName == "Comp. Sci.")
  80                       .OrderBy(c => c.Name).ToList(); 
  81 
  82 foreach (var s in deepsum)
  83 {
  84     var creds = s.Takes.Sum(t => t.Section.Course.Credits);
  85     if (creds >=8) 
  86     {
  87         Console.WriteLine($"{s.Id}, {s.Name}, Major = {s.DeptName}, Class Credits = {creds}");
  88     }
  89 }
  90 
  91 //Here is an example of using the null coalescence operator
  92 var students = from c in context.Students.Include(s => s.Advisor).ThenInclude(t => t.IIdNavigation)
  93                       where c.DeptName == "Comp. Sci."
  94                       orderby c.Name
  95                       select new { c.Id, c.Name, c.DeptName, AdvisorName = c.Advisor.IIdNavigation.Name?? "None" };