= 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:
{{{#!highlight sql
ALTER TABLE teaches
ADD teacher_credit NUMERIC(3,12);
}}}

SQLite:
{{{#!highlight sql
ALTER TABLE teaches 
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). 
 1. Next Open: Tools, !NuGet Package Manager, Package Manager Console.
 1. 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

{{{#!highlight csharp
using CPTR319_DB_Example_EF.Models;
using Microsoft.EntityFrameworkCore;


//List student names along with their majors
var context = new UniversityContext();
var students = context.Students.ToList();
foreach (var student in students)
{
    Console.WriteLine($"Name = {student.Name}, Major = {student.DeptName}");
}

//List student names along with their classes ordered nicely by year, semester...
var studentclasses = context.Students
    .Include(s => s.Takes)
        .ThenInclude(t => t.Section)
            .ThenInclude(e => e.Course)
    .OrderBy(s => s.Name);
foreach (var sc in studentclasses)
{
    Console.WriteLine($"STUDENT: {sc.Name}, Major: {sc.DeptName}");
    var takes = sc.Takes.OrderBy(x => x.Year).ThenBy(y => y.Semester);
    foreach (var ta in takes)
    {
        Console.WriteLine($"   {ta.Year} - {ta.Semester}: {ta.Section.Course.Title}");
    }
}
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.
var studentclasses2 = context.Students
    .Include(s => s.Takes)
        .ThenInclude(t => t.Section)
            .ThenInclude(e => e.Course)
    .Where(s => s.TotCred >= 80)
    .OrderBy(s => s.Name);
foreach (var sc in studentclasses2)
{
    Console.WriteLine($"STUDENT: {sc.Name}, Major: {sc.DeptName}, Credits: {sc.TotCred}");
    var takes = sc.Takes.OrderBy(x => x.Year).ThenBy(y => y.Semester);
    foreach (var ta in takes)
    {
        Console.WriteLine($"   {ta.Year} - {ta.Semester}: {ta.Section.Course.Title}");
    }
}
Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");

//Let's count the number of classes taken by students with 80 or more credits 
var studentclasses3 = context.Students
    .Include(s => s.Takes);
foreach (var sc in studentclasses3)
{
    Console.WriteLine($"Name: {sc.Id}->{sc.Name}, Major: {sc.DeptName}, Hours: {sc.TotCred}, Classes: {sc.Takes.Count()}");
}
Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");

//Althernative ways of doing this using Link to SQL 
var list = from o in context.Students
           where o.TotCred >= 80
           from t in o.Takes
           let foo = new
           {
               Name = o.Name,
               Id = o.Id,
               Major = o.DeptName,
               Hours = o.TotCred,
               Classes = o.Takes.Count()
           }
           orderby foo.Name, foo.Classes descending
           select foo;

foreach (var l in list)
{
    Console.WriteLine($"Name: {l.Id}->{l.Name}, Major: {l.Major}, Hours: {l.Hours}, Classes: {l.Classes}");
}

}}}