Differences between revisions 1 and 5 (spanning 4 versions)
Revision 1 as of 2022-02-20 23:39:43
Size: 710
Editor: scot
Comment:
Revision 5 as of 2022-02-21 00:35:53
Size: 4328
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
}}}

== Now to use the database ==

First make sure you project compiles. It did? Good!

'''Let's do something simple like list all the students names along with their major:'''

{{{#!highlight csharp
var context = new UniversityContext();
var students = context.Students.ToList();
foreach (var student in students)
{
    Console.WriteLine($"Name = {student.Name}, Major = {student.DeptName}");
}
Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");
}}}

'''No let's include the classes for each student, along with the course title, nicely ordered:'''
{{{#!highlight csharp
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.'''
{{{#!highlight csharp
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");
}}}

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

Now to use the database

First make sure you project compiles. It did? Good!

Let's do something simple like list all the students names along with their major:

   1 var context = new UniversityContext();
   2 var students = context.Students.ToList();
   3 foreach (var student in students)
   4 {
   5     Console.WriteLine($"Name = {student.Name}, Major = {student.DeptName}");
   6 }
   7 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");

No let's include the classes for each student, along with the course title, nicely ordered:

   1 var studentclasses = context.Students
   2     .Include(s => s.Takes)
   3         .ThenInclude(t => t.Section)
   4             .ThenInclude(e => e.Course)
   5     .OrderBy(s => s.Name);
   6 foreach (var sc in studentclasses)
   7 {
   8     Console.WriteLine($"STUDENT: {sc.Name}, Major: {sc.DeptName}");
   9     var takes = sc.Takes.OrderBy(x => x.Year).ThenBy(y => y.Semester);
  10     foreach (var ta in takes)
  11     {
  12         Console.WriteLine($"   {ta.Year} - {ta.Semester}: {ta.Section.Course.Title}");
  13     }
  14 }
  15 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.

   1 var studentclasses2 = context.Students
   2     .Include(s => s.Takes)
   3         .ThenInclude(t => t.Section)
   4             .ThenInclude(e => e.Course)
   5     .Where(s => s.TotCred >= 80)
   6     .OrderBy(s => s.Name);
   7 foreach (var sc in studentclasses2)
   8 {
   9     Console.WriteLine($"STUDENT: {sc.Name}, Major: {sc.DeptName}, Credits: {sc.TotCred}");
  10     var takes = sc.Takes.OrderBy(x => x.Year).ThenBy(y => y.Semester);
  11     foreach (var ta in takes)
  12     {
  13         Console.WriteLine($"   {ta.Year} - {ta.Semester}: {ta.Section.Course.Title}");
  14     }
  15 }
  16 Console.WriteLine("\r\n\r\n----Next Query----\r\n\r\n");

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