Kurse:Entity Framework Core

EF Core Cheatsheet (Deutsch)

DbContext & DbSet

public class PersonsDbContext : DbContext
{
    public DbSet<Person> Persons { get; set; }
    public DbSet<Country> Countries { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Country>().ToTable("Countries");
        modelBuilder.Entity<Person>().ToTable("Persons");
    }
}

Verbindungskette (appsettings.json)

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=PersonsDatabase;Integrated Security=True"
  }
}

DI Registrierung (Program.cs)

builder.Services.AddDbContext<PersonsDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

LINQ-Abfrage mit Navigation

var peopleFromUSA = _dbContext.Persons
    .Include(p => p.Country)
    .Where(p => p.Country.CountryName == "USA")
    .ToList();

Seed Data mit JSON-Dateien

var countries = JsonSerializer.Deserialize<List<Country>>(File.ReadAllText("countries.json"));
modelBuilder.Entity<Country>().HasData(countries);

Fluent API Beispiel

modelBuilder.Entity<Person>()
    .Property(p => p.TIN)
    .HasColumnName("TaxID")
    .HasColumnType("varchar(8)")
    .HasDefaultValue("ABC12345");

Migrationen (Konsole)

dotnet ef migrations add InitialCreate
dotnet ef database update

Stored Procedure (Aufruf)

public List<Person> GetPersons() =>
    Persons.FromSqlRaw("EXEC dbo.GetAllPersons").ToList();

Create (Controller POST)

[HttpPost]
public async Task<IActionResult> Create(PersonAddRequest request)
{
    if (ModelState.IsValid)
    {
        await _personsService.AddPerson(request);
        return RedirectToAction("Index");
    }

    ViewBag.Countries = await _countriesService.GetAllCountries();
    return View(request);
}

PDF mit Rotativa

public async Task<IActionResult> PersonsPDF()
{
    var persons = await _personsService.GetAllPersons();
    return new ViewAsPdf("PersonsPDF", persons)
    {
        PageOrientation = Orientation.Landscape,
        PageMargins = { Top = 20, Bottom = 20, Left = 20, Right = 20 }
    };
}

CSV mit CsvHelper

public async Task<MemoryStream> GetPersonsCSV()
{
    var stream = new MemoryStream();
    using var writer = new StreamWriter(stream, leaveOpen: true);
    using var csv = new CsvWriter(writer, CultureInfo.InvariantCulture);
    
    var persons = await _dbContext.Persons.Include(p => p.Country).ToListAsync();
    csv.WriteRecords(persons);
    writer.Flush();
    stream.Position = 0;
    return stream;
}

Beziehungskonfiguration (Fluent API)

modelBuilder.Entity<Person>()
    .HasOne(p => p.Country)
    .WithMany(c => c.Persons)
    .HasForeignKey(p => p.CountryID);