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:
SQLite:
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
- Create a .NET 8 Console application (that's .NET Core by default).
Next Open: Tools, NuGet Package Manager, Package Manager Console.
- 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
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 }