|
Size: 5364
Comment:
|
Size: 5705
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 19: | Line 19: |
| What are we doing here? We are forcing EntityFramework6 to | 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. |
| Line 30: | Line 30: |
| Hint: Do you need to update a tool? | Hint: Do you need to update a tool to a specific version? |
| Line 32: | Line 32: |
| dotnet tool update --global dotnet-ef | dotnet tool update --global dotnet-ef --version 6.0.25 |
| Line 58: | Line 58: |
| }}} 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 |
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:
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 6 Console application (that's .NET Core by default).
Next Open: Tools, NuGet Package Manager, Package Manager Console.
- In the console type the following commands.
Hint: Do you need to update a tool to a specific version?
dotnet tool update --global dotnet-ef --version 6.0.25
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
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 }