Version du 1 novembre 2023 à 19:57

Links

Entity Framework Core Tools

.NET Core CLI

# 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

# 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

# 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)

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));

MySQL .NET Connection String Options

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

# 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 ?

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

context.Items.Where(x => x.Name.Contains("item1"));

SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE (@__query_Name_0 LIKE N'') OR (CHARINDEX(@__query_Name_0, [i].[Name]) > 0)

context.Items.Where(x => EF.Functions.Like(x.Name, "%item1%"));

SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE [i].[Name] LIKE @__Format_1

any

context.Items.Any(x => x.Id == 1);

SELECT EXISTS (
    SELECT 1
    FROM `item` AS `i`
    WHERE `i`.`id` = @__id_0)

left join

Perform left outer joins

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

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 });

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 };

SELECT u.Name AS userName, g.Name AS groupName 
FROM User AS u
LEFT JOIN Group AS g
ON u.GroupId = g.Id

Pagination

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;
}

« Entity Framework Core 7 » : différence entre les versions

Version du 1 novembre 2023 à 19:57

Sommaire

Links

Entity Framework Core Tools

.NET Core CLI

Package Manager Console in Visual Studio

Add Entity Framework Core package

Data Providers

Connection string

Dependency injection (ASP.Net Core)

MariaDb / MySql

SSL Authentication Error

SQL Server

SqlConnectionStringBuilder

DbContext OnConfiguring

Log underlying SQL query

Database first: scaffold database to model entities

Visual Studio

Custom Reverse Engineering Templates

Repository Pattern

Generic repository

Query generation

string contains vs like

any

left join

Pagination

Actions de la page

Actions de la page

Outils personnels

Menu de navigation

Navigation

Rechercher

Outils

« Entity Framework Core 7 » : différence entre les versions

Version du 1 novembre 2023 à 19:57

Links

Entity Framework Core Tools

Connection string

SSL Authentication Error

SQL Server

SqlConnectionStringBuilder

Visual Studio

Query generation

string contains vs like

any

Menu de navigation

Rechercher

« Entity Framework Core 7 » : différence entre les versions