Entity Framework zu existierendem Projekt hinzufügen
Entity Framework mit Posgres
Docker-Compose file für die DB
- Datenbank in Docker starten
version: "3.9"
services:
db:
image: postgres:16.0
restart: always
environment:
POSTGRES_USER: hoell
POSTGRES_PASSWORD: 123
POSTGRES_DB: MyDB
volumes:
- /var/lib/postgresql/data:/var/lib/postgresql/data
ports:
- 5432:5432
pgadmin:
image: dpage/pgadmin4
restart: always
environment:
PGADMIN_DEFAULT_EMAIL: hoell@hl-dev.de
PGADMIN_DEFAULT_PASSWORD: 123
ports:
- 80:80
Pakete installieren
Install-Package Npgsql.EntityFrameworkCore.PostgreSQL -Version 6.0.22
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.22
DBContext Datei anlegen
public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
/Models/MyDbContext.cs
public class MyDbContext : DbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options)
: base(options)
{
}
public DbSet<Person> Persons { get; set; }
}
Datenbank registrieren
Folgendes der Program.cs Datei hinzufügen. Entsprechendes vor der Zeile var app = builder.Build(); stehen.
/appsettings.json
{
"ConnectionStrings": {
"DefaultConnection": "TODO: setup connection for realease server"
},
...
}
/Program.cs
// Register database
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<MyDbContext>(options =>
options.UseNpgsql(connectionString));
Neue Migration erstellen über den Package Manager
Add-Migration CreateIdentitySchema
Update-Database
Entity Framework mit Oracle DB
Docker-Compose file für die DB
- Datenbank in Docker starten
version: "3.6"
services:
proddbservice:
image: hldev/oracle:21.3.0-xe #Nutzt eigenes Oracle Image
container_name: myOracle21-3-0
environment:
- ORACLE_PWD=123456789
- ORACLE_CHARACTERSET=AL32UTF8
ports:
- 1521:1521
- 5500:5500
volumes:
- /srv/oracle/oradata:/opt/oracle/oradata
Container Starten
vor dem ersten Start
mkdir /srv/oracle/oradata -p
sudo chmod 775 /srv/oracle/oradata/
sudo chown 54321:54322 /srv/oracle/oradata/
Oracle Benutzer anlegen
docker exec -it myOracle21-3-0 bash
su
sqlplus SYSTEM/123456789@localhost:1521/XE
alter session set "_ORACLE_SCRIPT"=true;
create user test identified by 123;
GRANT CONNECT, RESOURCE, DBA TO test;
Pakete installieren
Install-Package Oracle.EntityFrameworkCore -Version 8.21.121
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 8.0.1
Scaffolding DB
Das Datenmodell wird automatisch erstellt.
Scaffold-DbContext "User Id=test;Password=123;Data Source=localhost:1521/xe;" Oracle.EntityFrameworkCore -Tables PERSON
Manuelle Erstellung des DB Models
public class MyDbContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseOracle(@"User Id=test;Password=123;Data Source=localhost:1521/xe;");
}
}
public class PERSON
{
public int PERSONID { get; set; }
public string? NAME { get; set; }
}
Es beachten ist, dass Tabellennamen und Spaltennamen in Oracle in Großbuchstaben geschrieben werden. Daher sie auch in den Modellklassen großgeschrieben werden.
Alternativ bieten sich folgende Möglichkeiten an:
Namen als Attribut schreiben
[Table("PERSON")]
public class Person
{
/ [Column("PERSONID")]
public int PersonId { get; set; }
[Column("NAME")]
public string? Name { get; set; }
}
Angabe der Namen in der OnModelCreating Methode
public class Person
{
public int PersonId { get; set; }
public string? Name { get; set; }
}
public class MyDbContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseOracle(@"User Id=test;Password=123;Data Source=localhost:1521/xe;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Person>(entity =>
{
entity.ToTable("PERSON");
entity.Property(e => e.PersonId)
.HasColumnName("PERSONID");
entity.Property(e => e.Name)
.HasColumnName("NAME");
});
}
}
Automatisches umwandeln aller Tabellennamen und Spaltennamen in Großbuchstaben
public class Person
{
public int PersonId { get; set; }
public string? Name { get; set; }
}
public class MyDbContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseOracle(@"User Id=test;Password=123;Data Source=localhost:1521/xe;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
ConvertTableNamesPropertyNamesToUpper(modelBuilder);
}
private static void ConvertTableNamesPropertyNamesToUpper(ModelBuilder modelBuilder)
{
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
// Set table name to uppercase
entity.SetTableName(entity.ClrType.Name.ToUpper());
foreach (var property in entity.GetProperties())
{
// Set column name to uppercase
property.SetColumnName(property.GetColumnName().ToUpper());
}
}
}
}
Lesen und Schreiben von Daten
public class Program
{
static void Main(string[] args)
{
using (var ctx = new MyDbContext())
{
// write
Person person = new()
{
PersonId = 101,
Name = "Test",
};
ctx.Add(person);
ctx.SaveChanges();
// read
var query = from p in ctx.People
select p;
Console.WriteLine(query.ToList()[0].Name);
}
}
}