Differences between revisions 1 and 8 (spanning 7 versions)
Revision 1 as of 2022-02-20 23:39:43
Size: 710
Editor: scot
Comment:
Revision 8 as of 2022-04-20 17:02:45
Size: 5364
Editor: scot
Comment:
Deletions are marked like this. Additions are marked like this.
Line 7: Line 7:
{{{#!hightlight sql SQL Server:
{{{#!highlight sql
Line 11: Line 12:

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

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}");
}

}}}

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

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 }

ProgrammingLinks/ConsoleDatabaseExampleEf6 (last edited 2022-04-20 17:02:45 by scot)