Links
Entity Framework Core Tools
|
dotnet ef
dotnet tool install --global dotnet-ef
dotnet tool update dotnet-ef --global
|
 |
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
|
|
Get-Help about_EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Tools
Update-Package Microsoft.EntityFrameworkCore.Tools
|
|
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Pomelo.EntityFrameworkCore.MySql
|
 |
Microsoft.EntityFrameworkCore.Design allows to scaffold and has to be installed on the executed project. |
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");
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);
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=***");
optionsBuilder.UseMySql("name=ConnectionStrings:MariaDb", ServerVersion.Parse("10.11.4-mariadb"));
optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString);
|
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
},
|
|
dotnet ef dbcontext scaffold
"Server=localhost;Database=MyDb;User=sa;Password=***;"
"Name=ConnectionStrings:SqlServer"
Microsoft.EntityFrameworkCore.SqlServer
--output-dir DataAccess/Entities
--context-dir DataAccess
--context "MyDbContext"
--force
--table table1 --table 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;
}
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;
}
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<TResult>> GetAsync<TResult>(
Expression<Func<TEntity, TResult>> selector,
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)
where TResult : class
{
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.Select(selector).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;
}
}
|
AsNoTracking is used to improve performances when you don't need the entities to be tracked for changes. But a better way to improve performances is to use projection so you don't return the whole entity but only the needed fields. This way you don't need AsNoTracking because there is no more entities to track.
Include is used to retrieve other entities linked by a foreign key / navigation property. If you use a projection with a navigation property, you don't have to use Include.
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)
|
|
context.Users.Select(u => new { u.Name, u.Group?.Name });
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
|
|
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;
}
|
|
await context.Tags.Where(t => t.Text.Contains(".NET")).ExecuteDeleteAsync();
|
|
DELETE FROM [t]
FROM [Tags] AS [t]
WHERE [t].[Text] LIKE N'%.NET%'
|
|
await context.Posts
.Where(p => p.PublishedOn.Year < 2022)
.ExecuteUpdateAsync(s => s
.SetProperty(b => b.Title, b => b.Title + " (" + b.PublishedOn.Year + ")")
.SetProperty(b => b.Content, b => b.Content + " ( This content was published in " + b.PublishedOn.Year + ")"));
|
|
UPDATE [p]
SET [p].[Content] = (([p].[Content] + N' ( This content was published in ') + COALESCE(CAST(DATEPART(year, [p].[PublishedOn]) AS nvarchar(max)), N'')) + N')',
[p].[Title] = (([p].[Title] + N' (') + COALESCE(CAST(DATEPART(year, [p].[PublishedOn]) AS nvarchar(max)), N'')) + N')'
FROM [Posts] AS [p]
WHERE DATEPART(year, [p].[PublishedOn]) < 2022
|