Entity Framework Core 7

De Banane Atomic
Aller à la navigationAller à la recherche

Links

Entity Framework Core Tools

.NET Core CLI

Powershell.svg 
# test if Entity Framework Core Tools has been installed
dotnet ef
# be sure to run the previous command in the folder of the project where EF has been added

# dotnet ef must be installed as a global or local tool
dotnet tool install --global dotnet-ef
# installed in ~/.dotnet/tools
# Add ~/.dotnet/tools to PATH

# update
dotnet tool update --global dotnet-ef

Package Manager Console in Visual Studio

Visual Studio
  • View → Other Windows → Package Manager Console
  • Default Project = the one containing the entity configurations
  • Startup Project = the one containing the sql server configuration
Ps.svg 
# test if Entity Framework Core Tools has been installed
Get-Help about_EntityFrameworkCore

# install
Install-Package Microsoft.EntityFrameworkCore.Tools

# update
Update-Package Microsoft.EntityFrameworkCore.Tools

Add Entity Framework Core package

Bash.svg 
# sql server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

# mysql
dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet add package Microsoft.EntityFrameworkCore.Design

Data Providers

Provider Package NuGet Connection String
SQL Server Microsoft.EntityFrameworkCore.SqlServer Server=(localdb)\\MSSQLLocalDB;Database=MyDb;Integrated Security=True;MultipleActiveResultSets=True;
Server=localhost;Database=MyDb;User=sa;Password=pwd;
MySQL / MariaDB Pomelo.EntityFrameworkCore.MySql server=localhost;database=MyDb;user=root;password=pwd
PostgreSQL Npgsql.EntityFrameworkCore.PostgreSQL Host=localhost;Database=MyDb;Username=root;Password=pwd
InMemory Microsoft.EntityFrameworkCore.InMemory databaseName: "test_database"
Sqlite Microsoft.EntityFrameworkCore.Sqlite Data Source=/tmp/file.db

Connection string

  • The connection string could be stored in the secret store in dev environnement
  • It could also be stored in appsettings.Development.json in dev environnement
appsettings.json 
{
  "ConnectionStrings": {
    "MariaDb": "server=localhost;database=test;user=test;password=***"
  }
}

Dependency injection (ASP.Net Core)

By default the DbContext is registered as a scoped service: a new DbContext is created on a new thread for each API request.

MariaDb / MySql

Program.cs 
var connectionString = builder.Configuration.GetConnectionString("MariaDb");  // get the connection string from the appsettings.json or the secret store
var serverVersion = new MariaDbServerVersion(new Version(10, 11, 4));

builder.Services.AddDbContext<MyAppContext>(
    dbContextOptions => dbContextOptions
        .UseMySql(connectionString, serverVersion));

SSL Authentication Error

On Windows, if named pipe authentication is used you may have this issue.
Disable SSL in the connection string to workaround it: sslmode=none

SQL Server

SqlConnectionStringBuilder

Program.cs 
var connectionStringWithoutPassword = builder.Configuration.GetConnectionString("SqlServer");
var connectionStringBuilder = new SqlConnectionStringBuilder(connectionStringWithoutPassword);  // Package: System.Data.SqlClient
connectionStringBuilder.Password = builder.Configuration["SqlServerPassword"];
var connectionString = connectionStringBuilder.ConnectionString;

DbContext OnConfiguring

DataAccess/MyAppContext.cs 
public class MyAppContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("server=localhost;database=test;user=test;password=***"); // hard-coded connection string
        optionsBuilder.UseMySql("name=ConnectionStrings:MariaDb", ServerVersion.Parse("10.11.4-mariadb")); // get it from the appsettings.json or the secret storage
        optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString); // WPF

Log underlying SQL query

Program.cs 
builder.Services.AddDbContext<AppContext>(
    options => options
        .LogTo(Console.WriteLine, LogLevel.Information)  // simple logging
        .UseLoggerFactory(LoggerFactory.Create(loggingBuilder =>
            loggingBuilder
                .AddConfiguration(builder.Configuration.GetSection("Logging"))  // log the configuration
                .AddConsole()  // log into the console if there is one
                .AddDebug()))  // log into VS output Window and Linux journal
        .EnableSensitiveDataLogging(  // include the values of data in exception messages
            builder.Configuration.GetValue<bool>("DbContextLogOptions:EnableSensitiveDataLogging", false))
        .EnableDetailedErrors(        // wrap each call to read a value in a try-catch block
            builder.Configuration.GetValue<bool>("DbContextLogOptions:EnableDetailedErrors", false))
        .UseMySql(connectionString, serverVersion)
);
appsettings.json 
"DbContextLogOptions": {
  "EnableSensitiveDataLogging": true,
  "EnableDetailedErrors":  true
},

Database first: scaffold database to model entities

Bash.svg 
# generate entity classes and context class
dotnet ef dbcontext scaffold
  "Server=localhost;Database=MyDb;User=sa;Password=***;"  # an harcoded connection string
  "Name=ConnectionStrings:SqlServer"                      # get the connection string from the appsettings.json or the secret storage
  Microsoft.EntityFrameworkCore.SqlServer  # the database provider
  --output-dir DataAccess/Entities  # output folder for entities
  --context-dir DataAccess          # output folder for DbContext
  --context "MyDbContext"           # default context name: DbNameContext
  --force                           # overwrite all the class files
  --table table1 --table table2     # scaffold only table1 and table2

Visual Studio

  • Ensure Entity Framework Core Tools have been installed
  • View → Other Windows → Package Manager Console
  • Default Project = the one containing the entity configurations ?
  • Startup Project = the one containing the sql server configuration ?
Ps.svg 
Scaffold-DbContext 'Data Source=MY-PC;Initial Catalog=MyDb' Microsoft.EntityFrameworkCore.SqlServer -OutputDir Entities
Error Titre colonne 2
The certificate chain was issued by an authority that is not trusted Add Encrypt=False to the connection string
Login failed for user Add Integrated Security=True to the connection string

Custom Reverse Engineering Templates

Repository Pattern

  • 1 Repository per resource: ItemRepository
  • 1 DbContext for the whole application
Repositories/ItemRepository.cs 
public class ItemRepository : IItemRepository
{
    private readonly MyAppContext context;

    public ItemRepository(MyAppContext context)
    {
        this.context = context;
    }

    public async Task<IReadOnlyCollection<Item>> GetAllItemsAsync(CancellationToken cancellationToken)
    {
        var items = await context.Items
            .Include(x => x.NavigationProperty)
            .AsNoTracking()
            .ToListAsync(cancellationToken);
        return items;
    }

    public async Task<Item?> GetItemByIdAsync(int id, CancellationToken cancellationToken)
    {
        var item = await context.Items
            .AsNoTracking()
            .SingleOrDefaultAsync(x => x.Id == id, cancellationToken);
        return item;
    }

    public async Task<IReadOnlyCollection<Item>> GetItemsByNameAsync(string name, CancellationToken cancellationToken)
    {
        var items = await context.Items
            .Where(x => EF.Functions.Like(x.Name, $"%{name}%"))
            .AsNoTracking()
            .ToListAsync(cancellationToken);
        return items;
    }

    public async Task<Item> CreateItemAsync(Item item, CancellationToken cancellationToken)
    {
        await context.AddAsync(item, cancellationToken);
        await context.SaveChangesAsync(cancellationToken);
        return item; // it now contains its id
    }

    public async Task<bool> UpdateItemAsync(int id, Item item, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(item);

        var itemToUpdate = await context.Items.SingleOrDefaultAsync(x => x.Id == id, cancellationToken);

        if (itemToUpdate is null)
        {
            return false; // send not found from controller then
        }

        itemToUpdate.Name = item.Name;
        await context.SaveChangesAsync();
        return true;
    }

    public async Task<bool> DeleteItemAsync(int id, CancellationToken cancellationToken)
    {
        var nbAffectedRows = await context.Items.Where(x => x.Id == id).ExecuteDeleteAsync(cancellationToken);
        return nbAffectedRows == 1;
    }

Generic repository

RepositoryBase.cs 
public abstract class RepositoryBase<TEntity, TCreateUpdateQuery> where TEntity : class, new()
{
    private readonly AppContext context;

    private DbSet<TEntity> dbEntities { get; }

    protected RepositoryBase(BourseContext context)
    {
        this.context = context ?? throw new ArgumentNullException(nameof(context));
        dbEntities = this.context.Set<TEntity>();
    }

    protected async Task<IReadOnlyCollection<TEntity>> GetAsync(
        Func<IQueryable<TEntity>, IIncludableQueryable<TEntity, object>>? include = null,
        Expression<Func<TEntity, bool>>? predicate = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>>? orderBy = null,
        Func<IQueryable<TEntity>, IQueryable<TEntity>>? skipTake = null,
        bool disableTracking = true,
        CancellationToken cancellationToken = default)
    {
        IQueryable<TEntity> query = dbEntities;

        if (include is not null)
        {
            query = include(query);
        }

        if (predicate is not null)
        {
            query = query.Where(predicate);
        }

        if (orderBy is not null)
        {
            query = orderBy(query);
        }

        if (skipTake is not null)
        {
            query = skipTake(query);
        }

        if (disableTracking)
        {
            query = query.AsNoTracking();
        }

        return await query.ToListAsync(cancellationToken);
    }

    public async Task<TEntity> CreateAsync(
        TEntity entity, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(entity);

        await context.AddAsync(entity, cancellationToken);
        await context.SaveChangesAsync(cancellationToken);
        return entity;
    }

    public async Task<bool> UpdateAsync(
        Expression<Func<TEntity, bool>> filterByIdExpression,
        TCreateUpdateQuery query,
        CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(query);

        var entityToUpdate = await dbEntities
            .FirstOrDefaultAsync(filterByIdExpression, cancellationToken);

        if (entityToUpdate is null)
        {
            return false;
        }

        UpdateEntityFromCreateUpdateQuery(query, entityToUpdate);
        await context.SaveChangesAsync(cancellationToken);
        return true;
    }

    public async Task<bool> DeleteAsync(
        Expression<Func<TEntity, bool>> filterByIdExpression,
        CancellationToken cancellationToken)
    {
        var nbAffectedRows = await dbEntities
            .Where(filterByIdExpression)
            .ExecuteDeleteAsync(cancellationToken);

        return nbAffectedRows > 0;
    }
}

Query generation

string contains vs like

Csharp.svg 
context.Items.Where(x => x.Name.Contains("item1"));
Tsql.svg 
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE (@__query_Name_0 LIKE N'') OR (CHARINDEX(@__query_Name_0, [i].[Name]) > 0)
Csharp.svg 
context.Items.Where(x => EF.Functions.Like(x.Name, "%item1%"));
Tsql.svg 
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE [i].[Name] LIKE @__Format_1

any

Csharp.svg 
context.Items.Any(x => x.Id == 1);
Mysql.svg 
SELECT EXISTS (
    SELECT 1
    FROM `item` AS `i`
    WHERE `i`.`id` = @__id_0)

left join

Csharp.svg 
// with navigation property
context.Users.Select(u => new { u.Name, u.Group?.Name });

// method syntax
context.Users
    .GroupJoin(
        context.Groups,
        user => user.GroupId,
        group => group.Id,
        (user, grouping) => new { user, grouping }
    )
    .SelectMany(z => z.grouping.DefaultIfEmpty(), (z, grp) => new { userName = z.user.Name, groupName = grp.Name })
    .Select(x => new { x.userName, x.groupName });

// query syntax
from user in context.Users
join group in context.Groups
on user.GroupId equals group.Id into grouping
from grp in grouping.DefaultIfEmpty()
select new { userName = user.Name, groupName = grp.Name };
Tsql.svg 
SELECT u.Name AS userName, g.Name AS groupName 
FROM User AS u
LEFT JOIN Group AS g
ON u.GroupId = g.Id

Pagination

Cs.svg 
public async Task<IReadOnlyCollection<Item>> GetItemsAsync(ItemQuery query, CancellationToken cancellationToken)
{
    var items = await this.context.Items
        .Skip((query.PageIndex - 1) * query.PageSize)
        .Take(query.PageSize)
        .AsNoTracking()
        .ToListAsync(cancellationToken);
    return items;
}
Récupérée de « http://wiki.bananeatomic.fr/index.php?title=Entity_Framework_Core_7&oldid=29719 »