|
|
Ligne 94 : |
Ligne 94 : |
| ==== SSL Authentication Error ==== | | ==== SSL Authentication Error ==== |
| On Windows, if named pipe authentication is used you may have this issue.<br> | | On Windows, if named pipe authentication is used you may have this issue.<br> |
| Disable SSL in the connection string to workaround it: {{boxx|sslmode=none}} | | Disable SSL in the connection string to workaround it: {{boxx|1=sslmode=none}} |
|
| |
|
| === SQL Server === | | === SQL Server === |
Version du 1 novembre 2023 à 17:39
Links
Entity Framework Core Tools
|
# 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
|
|
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
|
|
# 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
|
|
# 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
|
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=***"
}
}
|
|
By default the DbContext is registered as a scoped service: a new DbContext is created on a new thread for each API request. |
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;
|
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
|
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
},
|
|
# 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
|
- 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;
}
|
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)
|
|
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;
}
|