Differences between revisions 2 and 3
Revision 2 as of 2022-02-20 23:40:03
Size: 709
Editor: scot
Comment:
Revision 3 as of 2022-02-21 00:04:05
Size: 2869
Editor: scot
Comment:
Deletions are marked like this. Additions are marked like this.
Line 7: Line 7:
SQL Server:
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 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 }

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