« Entity Framework Core » : différence entre les versions
(91 versions intermédiaires par le même utilisateur non affichées) | |||
Ligne 7 : | Ligne 7 : | ||
* [[Entity_Framework#Utilisation|Utilisation]] | * [[Entity_Framework#Utilisation|Utilisation]] | ||
* [https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/ Migration] | * [https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/ Migration] | ||
* [[Entity_Framework_Plus|Entity Framework Plus]] | |||
= Description = | = Description = | ||
Ligne 37 : | Ligne 38 : | ||
|- | |- | ||
| MySQL / MariaDB || [https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql Pomelo.EntityFrameworkCore.MySql] || server=localhost;database=MyDb;user=root;password=pwd | | MySQL / MariaDB || [https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql Pomelo.EntityFrameworkCore.MySql] || server=localhost;database=MyDb;user=root;password=pwd | ||
|- | |||
| PostgreSQL || [https://www.npgsql.org/efcore Npgsql.EntityFrameworkCore.PostgreSQL] || Host=localhost;Database=MyDb;Username=root;Password=pwd | |||
|- | |- | ||
| InMemory || [https://docs.microsoft.com/en-us/ef/core/providers/in-memory/?tabs=dotnet-core-cli Microsoft.EntityFrameworkCore.InMemory] || databaseName: "test_database" | | InMemory || [https://docs.microsoft.com/en-us/ef/core/providers/in-memory/?tabs=dotnet-core-cli Microsoft.EntityFrameworkCore.InMemory] || databaseName: "test_database" | ||
Ligne 42 : | Ligne 45 : | ||
= [https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet Entity Framework Core Tools] = | = [https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet Entity Framework Core Tools] = | ||
* View → Other Windows → Package Manager Console | |||
* Default Project = the one containing the entity configurations | |||
* Startup Project = the one containing the sql server configuration | |||
<kode lang='powershell'> | <kode lang='powershell'> | ||
# tester si Entity Framework Core Tools est bien installé et fonctionnel | # tester si Entity Framework Core Tools est bien installé et fonctionnel | ||
Ligne 47 : | Ligne 54 : | ||
# bien se mettre dans le dossier du projet | # bien se mettre dans le dossier du projet | ||
# faire un dotnet restore au cas où la restoration n'aurait pas fonctionnée | # faire un dotnet restore au cas où la restoration n'aurait pas fonctionnée | ||
# dotnet ef must be installed as a global or local tool | # dotnet ef must be installed as a global or local tool | ||
dotnet tool install --global dotnet-ef | dotnet tool install --global dotnet-ef | ||
Ligne 62 : | Ligne 66 : | ||
dotnet add package Microsoft.EntityFrameworkCore.Design | dotnet add package Microsoft.EntityFrameworkCore.Design | ||
</kode> | </kode> | ||
= [https://docs.microsoft.com/en-us/ef/core/get-started/netcore/new-db-sqlite#create-the-model Create DbContext] = | = [https://docs.microsoft.com/en-us/ef/core/get-started/netcore/new-db-sqlite#create-the-model Create DbContext] = | ||
Ligne 78 : | Ligne 72 : | ||
public class MyAppContext : DbContext | public class MyAppContext : DbContext | ||
{ | { | ||
public DbSet<Item> Items | public DbSet<Item> Items => Set<Item>(); | ||
} | } | ||
</filebox> | </filebox> | ||
Ligne 184 : | Ligne 178 : | ||
Nécessite le package nuget {{boxx|System.ComponentModel.DataAnnotations}} | Nécessite le package nuget {{boxx|System.ComponentModel.DataAnnotations}} | ||
== [https://docs.microsoft.com/en-us/ef/core/modeling/query-types Vue/view | == [https://docs.microsoft.com/en-us/ef/core/modeling/query-types Vue / view] == | ||
<filebox fn='Model/BlogPostsCount.cs'> | |||
public class BlogPostsCount | |||
{ | |||
public int BlogId { get; set; } | |||
public int PostCount { get; set; } | |||
} | |||
</filebox> | |||
<filebox fn='Model/Configuration/BlogPostsCountConfiguration'> | |||
public class BlogPostsCountConfiguration : IEntityTypeConfiguration<BlogPostsCount> | |||
{ | |||
public void Configure(EntityTypeBuilder<BlogPostsCount> builder) | |||
{ | |||
builder.ToView("View_BlogPostCounts"); | |||
builder.HasKey(x => x.BlogId); | |||
} | |||
} | |||
</filebox> | |||
<filebox fn='Model/BloggingContext.cs'> | |||
public class BloggingContext : DbContext | |||
{ | |||
public DbSet<BlogPostsCount> BlogPostCounts { get; set; } | |||
</filebox> | |||
<kode lang='bash'> | |||
# create a new migration | |||
dotnet ef migrations add View_BlogPostCounts | |||
</kode> | |||
<filebox fn='Migration/YYYYMMDDHHmmss_View_BlogPostCounts.cs'> | |||
public partial class View_BlogPostCounts : Migration | |||
{ | |||
protected override void Up(MigrationBuilder migrationBuilder) | |||
{ | |||
migrationBuilder.Sql( | |||
@"CREATE VIEW View_BlogPostCounts AS | |||
SELECT b.BlogId, Count(p.PostId) as PostCount | |||
FROM Blogs b | |||
JOIN Posts p on p.BlogId = b.BlogId | |||
GROUP BY b.BlogId;"); | |||
} | |||
protected override void Down(MigrationBuilder migrationBuilder) | |||
{ | |||
migrationBuilder.Sql("DROP VIEW View_BlogPostCounts;"); | |||
} | |||
} | |||
</filebox> | |||
<kode lang='bash'> | |||
# update the database | |||
dotnet ef database update | |||
</kode> | |||
<filebox fn='Controllers/BlogController.cs'> | |||
[HttpGet] | |||
public async Task<IEnumerable<BlogDto>> Get() | |||
{ | |||
var query = from b in this.dbContext.Blogs | |||
join c in this.dbContext.BlogPostCounts | |||
on b.BlogId equals c.BlogId | |||
select new BlogDto | |||
{ | |||
BlogId = b.BlogId, | |||
Url = b.Url, | |||
PostsCount = c.PostCount, | |||
Posts = b.Posts.Select(y => new PostDto | |||
{ | |||
PostId = y.PostId, | |||
Title = y.Title, | |||
Content = y.Content | |||
}) | |||
}; | |||
var dto = await query.ToListAsync(); | |||
</filebox> | |||
== [https://docs.microsoft.com/en-us/ef/core/modeling/query-types QueryType] == | |||
<filebox fn='Data/MyAppContext.cs'> | <filebox fn='Data/MyAppContext.cs'> | ||
public class MyAppContext : DbContext | public class MyAppContext : DbContext | ||
Ligne 194 : | Ligne 267 : | ||
= Connection string = | = Connection string = | ||
* [[Asp. | * [[Asp.net_core_8#Configuration|Configuration file and secrets]] | ||
== OnConfiguring == | == DbContext OnConfiguring == | ||
<filebox fn='DataAccess/MyAppContext.cs'> | <filebox fn='DataAccess/MyAppContext.cs'> | ||
public class MyAppContext : DbContext | public class MyAppContext : DbContext | ||
Ligne 202 : | Ligne 275 : | ||
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) | protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) | ||
{ | { | ||
optionsBuilder.UseSqlServer("server=localhost;database=test;user=test;password=****"); | optionsBuilder.UseSqlServer("server=localhost;database=test;user=test;password=****"); // hard-coded connection string | ||
optionsBuilder.UseMySql("name=ConnectionStrings:MariaDb", ServerVersion.Parse("10.11.4-mariadb")); // connection string in the appsettings.json | |||
optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString); // WPF | optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString); // WPF | ||
} | } | ||
Ligne 208 : | Ligne 282 : | ||
</filebox> | </filebox> | ||
== | == Constructor parameter == | ||
<kode lang='cs'> | <kode lang='cs'> | ||
var builder = new ConfigurationBuilder() | var builder = new ConfigurationBuilder() | ||
Ligne 238 : | Ligne 312 : | ||
== [https://docs.microsoft.com/en-us/ef/core/dbcontext-configuration/#dbcontext-in-dependency-injection-for-aspnet-core Dependency injection (ASP.Net Core)] == | == [https://docs.microsoft.com/en-us/ef/core/dbcontext-configuration/#dbcontext-in-dependency-injection-for-aspnet-core Dependency injection (ASP.Net Core)] == | ||
<filebox fn=' | * [https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#2-services-configuration MariaDb / MySql] | ||
<filebox fn='Program.cs'> | |||
// MariaDb | |||
var connectionString = builder.Configuration.GetConnectionString("MariaDb"); | |||
var serverVersion = new MariaDbServerVersion(new Version(10, 11, 4)); | |||
builder.Services.AddDbContext<MyAppContext>( | |||
dbContextOptions => dbContextOptions | |||
.UseMySql(connectionString, serverVersion) | |||
// TODO The following three options help with debugging, but should be changed or removed for production. | |||
.LogTo(Console.WriteLine, LogLevel.Information) | |||
.EnableSensitiveDataLogging() | |||
.EnableDetailedErrors()); | |||
</filebox> | |||
=== Old === | |||
<filebox fn='Startup.cs'> | |||
public void ConfigureServices(IServiceCollection services) | public void ConfigureServices(IServiceCollection services) | ||
{ | { | ||
// SQL Server | // SQL Server | ||
services.AddDbContext< | var builder = new SqlConnectionStringBuilder(Configuration.GetConnectionString("SqlServer")); // read the appsettings.json | ||
options.UseSqlServer( | builder.Password = Configuration["DbPassword"]; // use the Secret Manager in dev, and an environment variable in prod | ||
services.AddDbContext<MyAppContext>(options => | |||
options.UseSqlServer(builder.ConnectionString) | |||
); | ); | ||
// MySql | // MySql | ||
services.AddDbContext< | services.AddDbContext<MyAppContext>(options => | ||
options.UseMySql( | options.UseMySql( | ||
Configuration.GetConnectionString("MySql"), | Configuration.GetConnectionString("MySql"), | ||
Ligne 266 : | Ligne 349 : | ||
// In Memory | // In Memory | ||
services.AddDbContext< | services.AddDbContext<MyAppContext>(options => | ||
options.UseInMemoryDatabase("WebApplicationCoreMemoryDb"); | options.UseInMemoryDatabase("WebApplicationCoreMemoryDb"); | ||
); | ); | ||
Ligne 282 : | Ligne 365 : | ||
public class MyController | public class MyController | ||
{ | { | ||
private readonly | private readonly MyAppContext context; | ||
public MyController( | public MyController(MyAppContext context) | ||
{ | { | ||
this.context = context; | this.context = context; | ||
Ligne 290 : | Ligne 373 : | ||
</filebox> | </filebox> | ||
== Design time DbContext factory == | == [https://learn.microsoft.com/en-us/ef/core/cli/dbcontext-creation?tabs=dotnet-core-cli Design time DbContext factory] == | ||
* Explain how to create a Context which doesn't have a parameterless ctor. | * Explain how to create a Context which doesn't have a parameterless ctor. | ||
* Separate the EF code needed for generating database tables at design-time from EF code used by your application at runtime. | * Separate the EF code needed for generating database tables at design-time from EF code used by your application at runtime. | ||
Ligne 404 : | Ligne 487 : | ||
{ | { | ||
modelBuilder.Entity<Item>().HasData( | modelBuilder.Entity<Item>().HasData( | ||
new Item | new Item { Id = 1, Name = "Item1" }, | ||
new Item | new Item { Id = 2, Name = "Item2" } | ||
); | |||
} | } | ||
</filebox> | </filebox> | ||
{{warn | | |||
* set the ids even if they belong to an auto-incremented column | |||
* Use the ids to link objects.}} | |||
== OLD == | == OLD == | ||
Ligne 448 : | Ligne 536 : | ||
</filebox> | </filebox> | ||
= [https:// | = [https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/extensions-logging?tabs=v3 Log underlying SQL query] = | ||
<filebox fn=' | <filebox fn='Program.cs'> | ||
public | public static readonly ILoggerFactory MyLoggerFactory = | ||
LoggerFactory.Create(builder => { builder.AddConsole(); }); // log in the Visual Studio Code Debug Console | |||
< | builder.Services.AddDbContext<AppContext>( | ||
options => options | |||
.LogTo(Console.WriteLine, LogLevel.Information) // simple logging | |||
options.LogTo(Console.WriteLine); | .UseLoggerFactory(MyLoggerFactory) // LoggerFactory | ||
.EnableSensitiveDataLogging() // include the values of data in exception messages | |||
.EnableDetailedErrors() // wrap each call to read a value in a try-catch block | |||
.UseMySql(connectionString, serverVersion)); | |||
</filebox> | </filebox> | ||
Ligne 468 : | Ligne 554 : | ||
# generate entity classes and context class | # generate entity classes and context class | ||
dotnet ef dbcontext scaffold "Server=localhost;Database=MyDb;User=sa;Password=pwd;" Microsoft.EntityFrameworkCore.SqlServer | dotnet ef dbcontext scaffold "Server=localhost;Database=MyDb;User=sa;Password=pwd;" Microsoft.EntityFrameworkCore.SqlServer | ||
--output-dir | --output-dir Entities # output folder for entities | ||
--context-dir DataAccess # output folder for DbContext | --context-dir DataAccess # output folder for DbContext | ||
--context "MyDbContext" # default context name: DbNameContext | --context "MyDbContext" # default context name: DbNameContext | ||
--force # overwrite all the class files | --force # overwrite all the class files | ||
--no-onconfiguring # do not generate the OnConfiguring method containing the connection string | |||
--table table1 --table table2 # scaffold only table1 and table2 | --table table1 --table table2 # scaffold only table1 and table2 | ||
</kode> | </kode> | ||
== Visual Studio == | |||
View → Other Windows → Package Manager Console | |||
<kode lang='ps'> | |||
# verify the installation | |||
Get-Help about_EntityFrameworkCore | |||
# install if not yet installed | |||
Install-Package Microsoft.EntityFrameworkCore.Tools | |||
# scaffold | |||
Scaffold-DbContext 'Name=ConnectionStrings:MyApp' Pomelo.EntityFrameworkCore.MySql | |||
-OutputDir DataAccess/Entities | |||
-ContextDir DataAccess | |||
-Context "MyAppContext" | |||
-Force | |||
-NoOnConfiguring | |||
Scaffold-DbContext 'Data Source=MY-PC;Initial Catalog=MyDb' Microsoft.EntityFrameworkCore.SqlServer -OutputDir Entities | |||
</kode> | |||
{| class="wikitable wtp" | |||
! Error | |||
! Titre colonne 2 | |||
|- | |||
| The certificate chain was issued by an authority that is not trusted || Add {{boxx|1=Encrypt=False}} to the connection string | |||
|- | |||
| Login failed for user '' || Add {{boxx|1=Integrated Security=True}} to the connection string | |||
|} | |||
== [https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/templates?tabs=dotnet-core-cli Custom Reverse Engineering Templates] == | |||
= [https://docs.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/infrastructure-persistence-layer-design#the-repository-pattern Repository Pattern] = | = [https://docs.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/infrastructure-persistence-layer-design#the-repository-pattern Repository Pattern] = | ||
Ligne 545 : | Ligne 663 : | ||
* [https://stackoverflow.com/questions/7348663/c-sharp-entity-framework-how-can-i-combine-a-find-and-include-on-a-model-obje Use Include with Find] | * [https://stackoverflow.com/questions/7348663/c-sharp-entity-framework-how-can-i-combine-a-find-and-include-on-a-model-obje Use Include with Find] | ||
= [https://docs.microsoft.com/en-us/ef/core/querying/related-data/eager Include] = | = [https://docs.microsoft.com/en-us/ef/core/querying/related-data/eager Include for navigation properties] = | ||
<kode lang='cs'> | <kode lang='cs'> | ||
await this.dbContext.Blogs | |||
.Include(x => x.Posts) // load Blog.Posts | |||
// .ThenInclude(x => x.AnotherProperty) | |||
.ToListAsync(); | |||
</kode> | |||
<kode lang='tsql'> | |||
SELECT [b].[BlogId], [b].[Url], [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title] | |||
FROM [Blogs] AS [b] | |||
LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId] | |||
ORDER BY [b].[BlogId], [p].[PostId] | |||
</kode> | </kode> | ||
Ligne 633 : | Ligne 757 : | ||
person.Where(x => EF.Functions.Like(x.Name, "%Nicolas%")); | person.Where(x => EF.Functions.Like(x.Name, "%Nicolas%")); | ||
</kode> | </kode> | ||
= [https://docs.microsoft.com/en-us/ef/core/modeling/inheritance Inheritance] = | |||
== [https://docs.microsoft.com/en-us/ef/core/modeling/inheritance#table-per-hierarchy-and-discriminator-configuration Table Per Hierarchy] == | |||
By default, EF maps the inheritance using the table-per-hierarchy (TPH) pattern.<br> | |||
TPH uses a single table to store the data for all types in the hierarchy, and a discriminator column is used to identify which type each row represents. | |||
{| class="wikitable wtp" | |||
! Id | |||
! Discriminator | |||
! OwnerId | |||
! BorrowerId | |||
|- | |||
| 1 || SimpleItem || Paul || null | |||
|- | |||
| 2 || BorrowedItem || Jean || Luc | |||
|} | |||
<filebox fn='ItemBase.cs' collapsed> | |||
public abstract class ItemBase | |||
{ | |||
public int Id { get; set; } | |||
public ItemType Type { get; set; } | |||
public int OwnerId { get; set; } | |||
public Person Owner { get; set; } | |||
} | |||
</filebox> | |||
<filebox fn='SimpleItem.cs' collapsed> | |||
public class SimpleItem : ItemBase | |||
{ } | |||
</filebox> | |||
<filebox fn='BorrowedItem.cs' collapsed> | |||
public class BorrowedItem : ItemBase | |||
{ | |||
public int BorrowerId { get; set; } | |||
public Person Borrower { get; set; } | |||
} | |||
</filebox> | |||
<filebox fn='ItemContext.cs' collapsed> | |||
public class ItemContext : DbContext | |||
{ | |||
public DbSet<ItemBase> Item { get; set; } | |||
public DbSet<SimpleItem> SimpleItem { get; set; } | |||
public DbSet<BorrowedItem> BorrowedItem { get; set; } | |||
</filebox> | |||
= Examples = | = Examples = | ||
Ligne 731 : | Ligne 902 : | ||
</kode> | </kode> | ||
== [https:// | == [https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#many-to-many Many to many relationship] == | ||
Many User per Group and many Group per User. | Many User per Group and many Group per User. | ||
* [https://www.learnentityframeworkcore.com/configuration/many-to-many-relationship-configuration Configuring Many To Many Relationships in Entity Framework Core] | |||
=== Short way === | |||
<kode lang='cs'> | <kode lang='cs'> | ||
public class Group | |||
{ | |||
public int Id { get; set; } | |||
[Required, StringLength(40)] | |||
public string Name { get; set; } | |||
public IReadOnlyCollection<User> Users { get; set; } | |||
} | |||
public class User | |||
{ | |||
public int Id { get; set; } | |||
[Required, StringLength(40)] | |||
public string Name { get; set; } | |||
public IReadOnlyCollection<User> Groups { get; set; } | |||
} | |||
public class MyAppContext : DbContext | |||
{ | |||
protected override void OnModelCreating(ModelBuilder modelBuilder) | |||
{ | |||
// seed | |||
var users = new [] { new User { Id = 1, Name = "User1" } }; | |||
modelBuilder.Entity<User>().HasData(users); | |||
var groups = new [] { new Group { Id = 1, Name = "Group1" } }; | |||
modelBuilder.Entity<Group>().HasData(groups); | |||
var userGroups = new [] { new { UserId = 1, GroupId = 1 } }; | |||
modelBuilder.Entity<User>() | |||
.HasMany(x => x.Groups) | |||
.WithMany(x => x.Users) | |||
.UsingEntity(x => x.HasData(userGroups)); | |||
} | |||
} | |||
// query | |||
var users = await context.Users | |||
.Include(x => x.Groups) | |||
.ToListAsync(); | |||
</kode> | |||
=== Detailed way === | |||
<kode lang='cs' collapsed> | |||
public class Group | public class Group | ||
{ | { | ||
Ligne 775 : | Ligne 992 : | ||
.WithMany(group => group.UserGroups) | .WithMany(group => group.UserGroups) | ||
.HasForeignKey(x => x.GroupId); | .HasForeignKey(x => x.GroupId); | ||
// seed | |||
var users = new [] { new User { Id = 1, Name = "User1" } }; | |||
modelBuilder.Entity<User>().HasData(users); | |||
var groups = new [] { new Group { Id = 1, Name = "Group1" } }; | |||
modelBuilder.Entity<Group>().HasData(groups); | |||
var userGroups = new [] { new UserGroup { UserId = 1, GroupId = 1 } }; | |||
modelBuilder.Entity<UserGroup>().HasData(userGroups); | |||
} | } | ||
} | |||
// query | |||
var users = await context.Users | |||
.Include(x => x.UserGroups) | |||
.ThenInclude(x => x.Group) | |||
.ToListAsync(); | |||
</kode> | </kode> | ||
= [https://docs.microsoft.com/en-us/ef/core/testing/ Unit tests] = | |||
Use the InMemory database provider to test the repositories and the services. | |||
<filebox fn='MyServiceTest.cs'> | |||
private DbContextOptions<MyContext> options; | |||
public MyServiceTest() | |||
{ | |||
options = new DbContextOptionsBuilder<MyContext>() | |||
.UseInMemoryDatabase(databaseName: "test") | |||
.Options; | |||
// Insert seed data into the database using one instance of the context | |||
using (var context = new MyContext(options)) | |||
{ | |||
context.Database.EnsureDeleted(); | |||
context.Database.EnsureCreated(); | |||
var entity = new MyEntity { Id = 1 }; | |||
context.MyEntity.Add(expense); | |||
context.SaveChanges(); | |||
} | |||
} | |||
[Fact] | |||
public async Task MyMethodAsync_Input_Output() | |||
{ | |||
// Use a clean instance of the context to run the test | |||
using (var context = new MyContext(options)) | |||
{ | |||
var repository = new MyRepository(context); | |||
var service = new MyService(repository); | |||
var result = await service.MyMethod(); | |||
Assert.Collection( | |||
result, | |||
item => Assert.Equal(1, item.Id)); | |||
} | |||
} | |||
</filebox> | |||
= Erreurs = | = Erreurs = | ||
Ligne 827 : | Ligne 1 103 : | ||
= [https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators Examples of generated queries] = | = [https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators Examples of generated queries] = | ||
== left join == | == [https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators#left-join left join] == | ||
<kode lang='csharp'> | <kode lang='csharp'> | ||
context.Users.Select(u => new { u.Group, u }); | context.Users.Select(u => new { u.Group, u }); | ||
Ligne 869 : | Ligne 1 145 : | ||
FROM [Group] AS [g] | FROM [Group] AS [g] | ||
CROSS JOIN [User] AS [u] | CROSS JOIN [User] AS [u] | ||
</kode> | |||
== find == | |||
<kode lang='csharp'> | |||
var item = await context.Items.FindAsync(id); | |||
</kode> | |||
<kode lang='tsql'> | |||
SELECT TOP(1) [i].[Id], [i].[Name] | |||
FROM [Items] AS [i] | |||
WHERE [i].[Id] = @__p_0 | |||
</kode> | |||
== where == | |||
<kode lang='csharp'> | |||
context.Items.Where(x => x.Name == "Item1"); | |||
</kode> | |||
<kode lang='tsql'> | |||
SELECT [i].[Id], [i].[Name] | |||
FROM [Items] AS [i] | |||
WHERE [i].[Name] = @__query_Name_0 | |||
</kode> | |||
== string contains == | |||
<kode lang='csharp'> | |||
context.Items.Where(x => x.Name.Contains("Item")); | |||
</kode> | |||
<kode lang='tsql'> | |||
SELECT [i].[Id], [i].[Name] | |||
FROM [Items] AS [i] | |||
WHERE (@__query_Name_0 LIKE N'') OR (CHARINDEX(@__query_Name_0, [i].[Name]) > 0) | |||
</kode> | |||
== like == | |||
<kode lang='csharp'> | |||
context.Items.Where(x => EF.Functions.Like(x.Name, "%Item%")); | |||
</kode> | |||
<kode lang='tsql'> | |||
SELECT [i].[Id], [i].[Name] | |||
FROM [Items] AS [i] | |||
WHERE [i].[Name] LIKE @__Format_1 | |||
</kode> | |||
== int contains == | |||
<kode lang='csharp'> | |||
context.Items.Where(x => ids.Contains(x.Id)); | |||
</kode> | |||
<kode lang='tsql'> | |||
SELECT [i].[Id], [i].[Name] | |||
FROM [Items] AS [i] | |||
WHERE [i].[Id] IN (1, 2) | |||
</kode> | |||
== many to many == | |||
<kode lang='csharp'> | |||
context.Items.Include(x => x.Categories).ToListAsync(); | |||
</kode> | |||
<kode lang='tsql'> | |||
SELECT [i].[Id], [i].[Name], [t].[CategoriesId], [t].[ItemsId], [t].[Id], [t].[Name] | |||
FROM [Items] AS [i] | |||
LEFT JOIN ( | |||
SELECT [c].[CategoriesId], [c].[ItemsId], [c0].[Id], [c0].[Name] | |||
FROM [CategoryItem] AS [c] | |||
INNER JOIN [Categories] AS [c0] ON [c].[CategoriesId] = [c0].[Id] | |||
) AS [t] ON [i].[Id] = [t].[ItemsId] | |||
ORDER BY [i].[Id], [t].[CategoriesId], [t].[ItemsId], [t].[Id] | |||
</kode> | |||
== update without fetch == | |||
<kode lang='csharp'> | |||
// create a new item | |||
var itemToUpdate = new Item { Id = 1, Name = "new name" } | |||
// attach it then mark the property as modified | |||
context.Attach(itemToUpdate).Property(x => x.Name).IsModified = true; | |||
await context.SaveChangesAsync(); | |||
</kode> | |||
<kode lang='tsql'> | |||
UPDATE [Items] | |||
SET [Name] = @p0 | |||
WHERE [Id] = @p1; | |||
</kode> | </kode> |
Dernière version du 25 octobre 2024 à 11:13
Liens
- Learn Entity Framework Core 5
- EF Core on .NET Core Console App
- Getting started with ASP.NET Core and Entity Framework Core using Visual Studio
- ASP.NET Core 2.0 with SQLite and Entity Framework Core
- Utilisation
- Migration
- Entity Framework Plus
Description
- Réécriture complète d'EF
- Plus de nécessite d'utilisé des BdD relationnelles uniquement
Ajouter les packages au projet
cd MyProject # 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 --version 3.1.12 # Pomelo.EntityFrameworkCore.MySql 3.2.4 -> Microsoft.EntityFrameworkCore.Relational (>= 3.1.8 && < 5.0.0) |
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; |
Sqlite | Microsoft.EntityFrameworkCore.Sqlite | Data Source=/tmp/file.db |
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" |
Entity Framework Core Tools
- View → Other Windows → Package Manager Console
- Default Project = the one containing the entity configurations
- Startup Project = the one containing the sql server configuration
# tester si Entity Framework Core Tools est bien installé et fonctionnel dotnet ef # bien se mettre dans le dossier du projet # faire un dotnet restore au cas où la restoration n'aurait pas fonctionnée # 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 # màj de dotnet-ef dotnet tool update --global dotnet-ef # ajouter le paquet nuget suivant dotnet add package Microsoft.EntityFrameworkCore.Design |
Create DbContext
DataAccess/MyAppContext.cs |
public class MyAppContext : DbContext { public DbSet<Item> Items => Set<Item>(); } |
Configure entity types
Target | Convention |
---|---|
Primary key | property named Id or <type_name>Id will be configured as the primary key of an entity with the name PK_<type_name>. |
Foreign key | a relationship will be created when there is a navigation property discovered on a type. |
required | property whose .NET type can contain null will be configured as optional, whereas properties whose .NET type cannot contain null will be configured as required. |
Table name | same name as the DbSet property that exposes the entity or same as class name. |
Links:
EntityTypeConfiguration with Fluent API
DataAccess/DbModelConfiguration/ItemConfiguration.cs |
internal sealed class ItemConfiguration : IEntityTypeConfiguration<Item> { public void Configure(EntityTypeBuilder<Item> builder) { builder.ToTable("item"); // by convention same name as the DbSet property builder.Property(e => e.Id) .HasColumnName("id") // bc same name as property name .HasColumnType("smallint") .ValueGeneratedOnAdd() // bc if the column is identified as the PK .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn); builder.HasKey(e => e.Id) // bc a primary key is automatically generated on property named \w*Id .HasName("pk_item"); // bc PK_item builder.Property(e => e.Name) .HasColumnName("name") .HasMaxLength(50) // varchar(50) CHARACTER SET utf8mb4 .HasColumnType("nvarchar(50)") .HasCharSet("utf8mb4") // if not specified the database character set and collation are used .HasCollation("utf8mb4_unicode_ci"); .IsRequired(); builder.Property(e => e.CreationDate) .HasColumnName("creation_date") .HasColumnType("date"); builder.Property(e => e.Price) .HasColumnName("price") .HasColumnType("decimal(5,2)"); builder.HasIndex(e => e.Name) .HasName("ix_item_name") .IsUnique(); // foreign key builder.Property(e => e.GroupId) .HasColumnName("group_id") .HasColumnType("tinyint"); builder.HasIndex(e => e.GroupId) .HasName("ix_items_group_id"); builder.HasOne(e => e.Group) // navigation property: Item.Group .WithMany(e => e.Items) // with navigation property: Group.Items .WithMany() // without navigation property: Group.Items .HasForeignKey(e => e.GroupId) // foreign key property: Item.GroupId .OnDelete(DeleteBehavior.ClientSetNull) .HasConstraintName("fk_items_groups_group_id"); |
Unsigned types are not supported. |
DataAccess/ItemContext.cs |
protected override void OnModelCreating(ModelBuilder modelBuilder) { // specify the configuration modelBuilder.ApplyConfiguration(new ItemConfiguration()); // look for EntityTypeConfiguration in the current assembly modelBuilder.ApplyConfigurationsFromAssembly(typeof(ItemContext).Assembly); } |
Data Annotations Attributes
Data/Entities/Item.cs |
// définit le nom de la table, par défaut le nom de la classe est utilisées [Table("Items")] public class Item { public int Id { get; set; } [Column("My_Name")] // mapping public string Name { get; set; } // définit le type SQL qui sera utilisé pour le stockage de la donnée, ici un décimal de 7 chiffres dont 2 après la virgule [Column(TypeName = "decimal(7, 2)")] public decimal Price { get; set; } [Column(TypeName = "date")] public DateTime Date { get; set; } } |
Nécessite le package nuget System.ComponentModel.DataAnnotations
Vue / view
Model/BlogPostsCount.cs |
public class BlogPostsCount { public int BlogId { get; set; } public int PostCount { get; set; } } |
Model/Configuration/BlogPostsCountConfiguration |
public class BlogPostsCountConfiguration : IEntityTypeConfiguration<BlogPostsCount> { public void Configure(EntityTypeBuilder<BlogPostsCount> builder) { builder.ToView("View_BlogPostCounts"); builder.HasKey(x => x.BlogId); } } |
Model/BloggingContext.cs |
public class BloggingContext : DbContext { public DbSet<BlogPostsCount> BlogPostCounts { get; set; } |
# create a new migration dotnet ef migrations add View_BlogPostCounts |
Migration/YYYYMMDDHHmmss_View_BlogPostCounts.cs |
public partial class View_BlogPostCounts : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.Sql( @"CREATE VIEW View_BlogPostCounts AS SELECT b.BlogId, Count(p.PostId) as PostCount FROM Blogs b JOIN Posts p on p.BlogId = b.BlogId GROUP BY b.BlogId;"); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.Sql("DROP VIEW View_BlogPostCounts;"); } } |
# update the database dotnet ef database update |
Controllers/BlogController.cs |
[HttpGet] public async Task<IEnumerable<BlogDto>> Get() { var query = from b in this.dbContext.Blogs join c in this.dbContext.BlogPostCounts on b.BlogId equals c.BlogId select new BlogDto { BlogId = b.BlogId, Url = b.Url, PostsCount = c.PostCount, Posts = b.Posts.Select(y => new PostDto { PostId = y.PostId, Title = y.Title, Content = y.Content }) }; var dto = await query.ToListAsync(); |
QueryType
Data/MyAppContext.cs |
public class MyAppContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Query<MyQueryType>().ToView("MyView"); |
Connection string
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")); // connection string in the appsettings.json optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString); // WPF } } |
Constructor parameter
var builder = new ConfigurationBuilder() .AddUserSecrets<MyAppContextFactory>(); var configuration = builder.Build(); var connectionStringBuilder = new SqlConnectionStringBuilder("server=localhost;database=test;user=test;"); connectionStringBuilder.Password = configuration["DbPassword"]; var connectionString = connectionStringBuilder.ConnectionString; var connectionString = "server=localhost;database=test;user=test;password=****"; var contextOptions = new DbContextOptionsBuilder<MyAppContext>() .UseMySql( connectionString, mySqlOptions => mySqlOptions.ServerVersion("10.5.8-mariadb")) .Options; using var context = new MyAppContext(contextOptions); |
DataAccess/MyAppContext.cs |
public class MyAppContext : DbContext { public MyAppContext(DbContextOptions<ApplicationDbContext> options) : base(options) { } |
Dependency injection (ASP.Net Core)
Program.cs |
// MariaDb var connectionString = builder.Configuration.GetConnectionString("MariaDb"); var serverVersion = new MariaDbServerVersion(new Version(10, 11, 4)); builder.Services.AddDbContext<MyAppContext>( dbContextOptions => dbContextOptions .UseMySql(connectionString, serverVersion) // TODO The following three options help with debugging, but should be changed or removed for production. .LogTo(Console.WriteLine, LogLevel.Information) .EnableSensitiveDataLogging() .EnableDetailedErrors()); |
Old
Startup.cs |
public void ConfigureServices(IServiceCollection services) { // SQL Server var builder = new SqlConnectionStringBuilder(Configuration.GetConnectionString("SqlServer")); // read the appsettings.json builder.Password = Configuration["DbPassword"]; // use the Secret Manager in dev, and an environment variable in prod services.AddDbContext<MyAppContext>(options => options.UseSqlServer(builder.ConnectionString) ); // MySql services.AddDbContext<MyAppContext>(options => options.UseMySql( Configuration.GetConnectionString("MySql"), mySqlOptions => mySqlOptions.ServerVersion( new ServerVersion(new Version(10, 5, 8), ServerType.MariaDb))); // mySqlOptions => mySqlOptions.ServerVersion("10.5.8-mariadb"); ); // In Memory services.AddDbContext<MyAppContext>(options => options.UseInMemoryDatabase("WebApplicationCoreMemoryDb"); ); |
DataAccess/MyAppContext.cs |
public class MyAppContext : DbContext { // permet de passer des options à la construction du DbContext public MyAppContext(DbContextOptions<MyAppContext> options) : base(options) { } |
MyController.cs |
public class MyController { private readonly MyAppContext context; public MyController(MyAppContext context) { this.context = context; } |
Design time DbContext factory
- Explain how to create a Context which doesn't have a parameterless ctor.
- Separate the EF code needed for generating database tables at design-time from EF code used by your application at runtime.
DataAccess/MyAppContextFactory |
public class MyAppContextFactory : IDesignTimeDbContextFactory<MyAppContext> { private string connectionString; public MyAppContextFactory() { var builder = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.json") .AddUserSecrets<MyAppContextFactory>(); // read stored secrets var configuration = builder.Build(); connectionString = configuration.GetConnectionString("SqlServer"); } public MyAppContext CreateDbContext(string[] args) { var builder = new DbContextOptionsBuilder<MyAppContext>(); builder.UseSqlServer(connectionString); var dbContext = new MyAppContext(builder.Options); return dbContext; } } |
DataAccess/MyAppContext.cs |
public class MyAppContext : DbContext { // permet de passer des options à la construction du DbContext public MyAppContext(DbContextOptions<MyAppContext> options) : base(options) { } |
var dbContextFactory = new MyAppContextFactory(); using var dbContext = dbContextFactory.CreateDbContext(args); |
Migrations
# création du fichier de migration dotnet ef migrations add InitialCreate # un fichier YYYYMMDDHHMMSSS_IntialCreate.cs est créé dans le dossier Migration # supprimer la dernière migration dotnet ef migrations remove # lister les migration dotnet ef migrations list # il faut créer une migration avant de pouvoir passer à la génération # génération d'un script SQL pour voir ce que va faire la migration dotnet ef migrations script <FROM> <TO> # From: 0 par défaut # To: dernière migration par défaut # génération et exécution du script SQL dotnet ef database update # revert the last already applied migration dotnet ef database update <previous-migration-to-keep> dotnet ef migrations remove |
Add data update during migration
After having added a migration, you can edit the Migrations/YYYYMMDDHHmmss_MigrationName.cs file:
Migrations/YYYYMMDDHHmmss_MigrationName.cs |
public partial class MigrationName : Migration { protected override void Up(MigrationBuilder migrationBuilder) { /* ... */ // update data migrationBuilder.UpdateData( table: "MyTable", keyColumn: "Id", keyValue: 1, column: "Column1", value: 1); // execute SQL command migrationBuilder.Sql("UPDATE `MyTable` SET `Column1` = `Column2`;"); } |
Apply migrations at runtime
ASP.Net web API
Startup.cs |
public static void Configure(IApplicationBuilder app, IWebHostEnvironment env, MyDbContext context) { // ensures your existing migrations are applied to the database, create it if needed context.Database.Migrate(); // create the db if it doesn't exist context.Database.EnsureCreated(); } |
EnsureCreated totally bypasses migrations and just creates the schema for you, you can't mix this with migrations. EnsureCreated is designed for testing or rapid prototyping where you are ok with dropping and re-creating the database each time. |
Data Seeding
Permet de remplir la bdd avec un jeu initial de données.
Data/MyAppContext.cs |
protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Item>().HasData( new Item { Id = 1, Name = "Item1" }, new Item { Id = 2, Name = "Item2" } ); } |
|
OLD
Data/MyAppSeeder.cs |
public class MyAppSeeder { private readonly MyAppContext _context; public MyAppSeeder(MyAppContext context) { _context = context; } public void Seed() { _context.Database.EnsureCreated(); if (!_context.Items.Any()) { _context.Items.Add(new Item() { Name = "Item 1" }); // ne pas définir l'Id _context.Items.Add(new Item() { Name = "Item 2" }); _context.SaveChanges(); } |
Sartup.cs |
public void ConfigureServices(IServiceCollection services) { services.AddTransient<MyAppSeeder>(); } public void Configure(IApplicationBuilder app, IHostingEnvironment env) { using (var scope = app.ApplicationServices.CreateScope()) { var seeder = scope.ServiceProvider.GetService<MyAppSeeder>(); seeder.Seed(); } } |
Log underlying SQL query
Program.cs |
public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); }); // log in the Visual Studio Code Debug Console builder.Services.AddDbContext<AppContext>( options => options .LogTo(Console.WriteLine, LogLevel.Information) // simple logging .UseLoggerFactory(MyLoggerFactory) // LoggerFactory .EnableSensitiveDataLogging() // include the values of data in exception messages .EnableDetailedErrors() // wrap each call to read a value in a try-catch block .UseMySql(connectionString, serverVersion)); |
Database first: scaffold database to model entities
# generate entity classes and context class dotnet ef dbcontext scaffold "Server=localhost;Database=MyDb;User=sa;Password=pwd;" Microsoft.EntityFrameworkCore.SqlServer --output-dir Entities # output folder for entities --context-dir DataAccess # output folder for DbContext --context "MyDbContext" # default context name: DbNameContext --force # overwrite all the class files --no-onconfiguring # do not generate the OnConfiguring method containing the connection string --table table1 --table table2 # scaffold only table1 and table2 |
Visual Studio
View → Other Windows → Package Manager Console
# verify the installation Get-Help about_EntityFrameworkCore # install if not yet installed Install-Package Microsoft.EntityFrameworkCore.Tools # scaffold Scaffold-DbContext 'Name=ConnectionStrings:MyApp' Pomelo.EntityFrameworkCore.MySql -OutputDir DataAccess/Entities -ContextDir DataAccess -Context "MyAppContext" -Force -NoOnConfiguring 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 par classe: ItemsRepository
- 1 DbContext pour toute l'application
Data/ItemsRepository.cs |
public class ItemsRepository : IItemsRepository { private readonly MyAppContext context; public ItemsRepository(MyAppContext context) { this.context = context; } public async Task<IReadOnlyList<Item>> GetAllAsync() { return await context.Set<Item>() .Include(x => x.SubClass) .ToListAsync(); } public async Task<Item> GetByIdAsync(int id) { var item = await context.FindAsync<Item>(id); // use Include with Find this.context.Entry(item).Reference(x => x.User).Load(); return item; } public async Task<Item> CreateAsync(Item item) { var createdItemEntry = await context.AddAsync(item); await context.SaveChangesAsync(); return createdItemEntry.Entity; } public async Task UpdateAsync(Item itemToUpdate, Item item) { itemToUpdate.Name = item.Name; await context.SaveChangesAsync(); } public async Task DeleteAsync(Item item) { context.Remove(item); await context.SaveChangesAsync(); } } |
La création d'une interface permettra d'utiliser d'autres sources de données pour faire des tests.
Data\IItemsRepository.cs |
public interface IItemsRepository { Task<IReadOnlyList<Item>> GetAllAsync(); Task<Item> GetByIdAsync(int id); Task<Item> CreateAsync(Item item); Task UpdateAsync(Item itemToUpdate, Item item); Task DeleteAsync(Item item); } |
Startup.cs |
public void ConfigureServices(IServiceCollection services) { services.AddScoped<IItemsRepository, ItemsRepository>(); |
await this.dbContext.Blogs .Include(x => x.Posts) // load Blog.Posts // .ThenInclude(x => x.AnotherProperty) .ToListAsync(); |
SELECT [b].[BlogId], [b].[Url], [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title] FROM [Blogs] AS [b] LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId] ORDER BY [b].[BlogId], [p].[PostId] |
Datetime
this.context.Item .Where(x => EF.Functions.DateDiffDay(x.Date2, x.Date1) > 0) .ToListAsync(); |
N+1 selects problem
EF fait ses requêtes en lazy loading, ce qui veut dire que les requêtes SQL ne sont exécutées que lorsqu'on a besoin des données.
// 1 requête pour récupérer tous les contacts IEnumerable<Contact> allContacts = context.Contacts; foreach (var contact in allContacts) { // pour récupérer l'age de chaque contact il faut faire une nouvelle requête pour chaque contact // ce qui donne de mauvaises performances if (contact.age > 30) { /* ... */ } } // inclure Age lors de la récupération des contacts // ainsi tous se fait en une seule requête IEnumerable<Contact> allContacts = context.Contacts.Include(c => c.Age); |
Executing Raw SQL Queries
using System.Data.SqlClient; // pour SqlParameter var books = context.Books.FromSql("SELECT Id, Title FROM Books").ToList(); // passage de paramètre avec l'interpolation de string var book = context.Books.FromSql($"SELECT Id, Title FROM Books WHERE Title = {title}").FirstOrDefault(); // passage de paramètre avec DbParameter var p1 = new SqlParameter("@Id", id); var book = db.Books.FromSql("SELECT * FROM Books WHERE Id = @Id", p1).FirstOrDefault(); // opérations INSERT, UPDATE, DELETE var commandText = "INSERT Authors (AuthorName) VALUES (@AuthorName)"; var name = new SqlParameter("@AuthorName", "Jean-Christophe Grangé"); context.Database.ExecuteSqlCommand(commandText, name); // procédure stockée var authorId = new SqlParameter("@AuthorId", 1); var books = context.Books.FromSql("EXEC GetBooksByAuthor @AuthorId" , authorId).ToList(); // procédure stockée avec plusieurs paramètres var parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter(name, SqlDbType.Int) { Value = (object)value?.ToInt32(CultureInfo.InvariantCulture) ?? DBNull.Value }); var parameterNamesList = string.Join(", ", parameters.Select(p => p.ParameterName)); var storedProcedureResultRows = await context.Database.SqlQuery<StoredProcedureResultRow>( $"{StoredProcedureName} {parameterNamesList}", parameters.Cast<object>().ToArray()).ToListAsync(cancellationToken); |
Pagination
private const int PageSize = 10; public async Task<IReadOnlyList<Item>> GetAsync(int pageIndex) { var items = await this.context.Item .AsNoTracking() .Skip((pageIndex - 1) * PageSize) .Take(PageSize) .ToListAsync(); return items; } |
Ignore accent in string comparaison (MySql - Pomelo)
Use the like operator.
// doesn't return Nicolàs person.Where(x => x.Name.Contains("Nicolas", StringComparison.CurrentCultureIgnoreCase)); // return Nicolàs person.Where(x => EF.Functions.Like(x.Name, "%Nicolas%")); |
Inheritance
Table Per Hierarchy
By default, EF maps the inheritance using the table-per-hierarchy (TPH) pattern.
TPH uses a single table to store the data for all types in the hierarchy, and a discriminator column is used to identify which type each row represents.
Id | Discriminator | OwnerId | BorrowerId |
---|---|---|---|
1 | SimpleItem | Paul | null |
2 | BorrowedItem | Jean | Luc |
ItemBase.cs |
public abstract class ItemBase { public int Id { get; set; } public ItemType Type { get; set; } public int OwnerId { get; set; } public Person Owner { get; set; } } |
SimpleItem.cs |
public class SimpleItem : ItemBase { } |
BorrowedItem.cs |
public class BorrowedItem : ItemBase { public int BorrowerId { get; set; } public Person Borrower { get; set; } } |
ItemContext.cs |
public class ItemContext : DbContext { public DbSet<ItemBase> Item { get; set; } public DbSet<SimpleItem> SimpleItem { get; set; } public DbSet<BorrowedItem> BorrowedItem { get; set; } |
Examples
One to one relationship
One Address per User.
public class Address { public int Id { get; set; } [Required, StringLength(40)] public string City { get; set; } public User User { get; set; } } public class User { public int Id { get; set; } [Required, StringLength(40)] public string Name { get; set; } public Address Address { get; set; } public int AddressId { get; set; } } public class MyAppContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { // allow to have both User.Address and Address.User modelBuilder.Entity<Address>() .HasOne(x => x.User) .WithOne(user => user.Address) .HasForeignKey<User>(x => x.AddressId); |
CREATE TABLE [Addresses] ( [Id] int NOT NULL IDENTITY, [City] nvarchar(40) NOT NULL, CONSTRAINT [PK_Addresses] PRIMARY KEY ([Id]) ); CREATE TABLE [Users] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(40) NOT NULL, [AddressId] int NOT NULL, CONSTRAINT [PK_Users] PRIMARY KEY ([Id]), CONSTRAINT [FK_Users_Addresses_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [Addresses] ([Id]) ON DELETE CASCADE ); CREATE UNIQUE INDEX [IX_Users_AddressId] ON [Users] ([AddressId]); |
One to many relationship
Many Task per User.
public class User { public int Id { get; set; } [Required, StringLength(40)] public string Name { get; set; } public Task[] Tasks { get; set; } // can be remove without changing the generated sql if Task.User exists } public class Task { public int Id { get; set; } [Required, StringLength(40)] public string Name { get; set; } [Required] public User User { get; set; } // can be remove without changing the generated sql if User.Tasks exists } public class MyAppContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<User>() .HasMany(x => x.Tasks) .WithOne(task => task.User); |
CREATE TABLE [Users] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(40) NOT NULL, CONSTRAINT [PK_Users] PRIMARY KEY ([Id]) ); CREATE TABLE [Task] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(40) NOT NULL, [UserId] int NOT NULL, CONSTRAINT [PK_Task] PRIMARY KEY ([Id]), CONSTRAINT [FK_Task_Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id]) ON DELETE CASCADE ); CREATE INDEX [IX_Task_UserId] ON [Task] ([UserId]); |
Many to many relationship
Many User per Group and many Group per User.
Short way
public class Group { public int Id { get; set; } [Required, StringLength(40)] public string Name { get; set; } public IReadOnlyCollection<User> Users { get; set; } } public class User { public int Id { get; set; } [Required, StringLength(40)] public string Name { get; set; } public IReadOnlyCollection<User> Groups { get; set; } } public class MyAppContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { // seed var users = new [] { new User { Id = 1, Name = "User1" } }; modelBuilder.Entity<User>().HasData(users); var groups = new [] { new Group { Id = 1, Name = "Group1" } }; modelBuilder.Entity<Group>().HasData(groups); var userGroups = new [] { new { UserId = 1, GroupId = 1 } }; modelBuilder.Entity<User>() .HasMany(x => x.Groups) .WithMany(x => x.Users) .UsingEntity(x => x.HasData(userGroups)); } } // query var users = await context.Users .Include(x => x.Groups) .ToListAsync(); |
Detailed way
public class Group { public int Id { get; set; } [Required, StringLength(40)] public string Name { get; set; } public UserGroup[] UserGroups { get; set; } } public class User { public int Id { get; set; } [Required, StringLength(40)] public string Name { get; set; } [Required] public Address Address { get; set; } public Task[] Tasks { get; set; } public UserGroup[] UserGroups { get; set; } } public class UserGroup { public int UserId { get; set; } public User User { get; set; } public int GroupId { get; set; } public Group Group { get; set; } } public class MyAppContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<UserGroup>() .HasKey(x => new { x.UserId, x.GroupId }); modelBuilder.Entity<UserGroup>() .HasOne(x => x.User) .WithMany(user => user.UserGroups) .HasForeignKey(x => x.UserId); modelBuilder.Entity<UserGroup>() .HasOne(x => x.Group) .WithMany(group => group.UserGroups) .HasForeignKey(x => x.GroupId); // seed var users = new [] { new User { Id = 1, Name = "User1" } }; modelBuilder.Entity<User>().HasData(users); var groups = new [] { new Group { Id = 1, Name = "Group1" } }; modelBuilder.Entity<Group>().HasData(groups); var userGroups = new [] { new UserGroup { UserId = 1, GroupId = 1 } }; modelBuilder.Entity<UserGroup>().HasData(userGroups); } } // query var users = await context.Users .Include(x => x.UserGroups) .ThenInclude(x => x.Group) .ToListAsync(); |
Unit tests
Use the InMemory database provider to test the repositories and the services.
MyServiceTest.cs |
private DbContextOptions<MyContext> options; public MyServiceTest() { options = new DbContextOptionsBuilder<MyContext>() .UseInMemoryDatabase(databaseName: "test") .Options; // Insert seed data into the database using one instance of the context using (var context = new MyContext(options)) { context.Database.EnsureDeleted(); context.Database.EnsureCreated(); var entity = new MyEntity { Id = 1 }; context.MyEntity.Add(expense); context.SaveChanges(); } } [Fact] public async Task MyMethodAsync_Input_Output() { // Use a clean instance of the context to run the test using (var context = new MyContext(options)) { var repository = new MyRepository(context); var service = new MyService(repository); var result = await service.MyMethod(); Assert.Collection( result, item => Assert.Equal(1, item.Id)); } } |
Erreurs
Cannot authenticate using Kerberos
Erreur sur Linux durant l'exécution de dotnet ef database update. Impossible de se connecter au serveur SQL.
Passer Trusted_Connection à False et ajouter le user et password à la connection string corrige le problème.
appsettings.json |
"ConnectionStrings": { "DefaultConnection": "Server=localhost;Database=MyDb;Trusted_Connection=False;MultipleActiveResultSets=true;User=sa;Password=xxx" }, |
Specified key was too long; max key length is 767 bytes
Les index InnoDB (MySQL) ont une taille limitée. Il faut donc limité la taille des index à la création de la bdd.
Data/MyAppContext.cs |
protected override void OnModelCreating(ModelBuilder builder) { base.OnModelCreating(builder); // Shorten key length for Identity builder.Entity<IdentityRole>(entity => { entity.Property(m => m.NormalizedName).HasMaxLength(127); }); builder.Entity<IdentityUser>(entity => { entity.Property(m => m.NormalizedUserName).HasMaxLength(127); }); } |
The host localhost does not support SSL connections
Ajouter SslMode=none à la ConnectionString MySQL.
OLD - No executable found matching command "dotnet-ef"
Installer les packages Nuget:
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.EntityFrameworkCore.Tools.DotNet
MyProject.csproj |
<Project Sdk="Microsoft.NET.Sdk"> <!-- Ajouter le groupe suivant --> <ItemGroup> <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.0" /> <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" /> </ItemGroup> |
dotnet restore |
Examples of generated queries
left join
context.Users.Select(u => new { u.Group, u }); from user in context.Users join grp in context.Groups on user.GroupId equals grp.Id into grouping from grp in grouping.DefaultIfEmpty() select new { grp, user }; |
SELECT [g].[Id], [g].[Name], [u].[Id], [u].[GroupId], [u].[Name] FROM [User] AS[u] LEFT JOIN [Group] AS [g] ON [u].[GroupId] = [g].[Id] |
inner join
from user in context.Users join grp in context.Groups on user.GroupId equals grp.Id select new { grp, user }; |
SELECT [g].[Id], [g].[Name], [u].[Id], [u].[GroupId], [u].[Name] FROM [User] AS[u] INNER JOIN [Group] AS [g] ON [u].[GroupId] = [g].[Id] |
cross join
from grp in context.Groups from user in context.Users select new { grp, user }; |
SELECT [g].[Id], [g].[Name], [u].[Id], [u].[GroupId], [u].[Name] FROM [Group] AS [g] CROSS JOIN [User] AS [u] |
find
var item = await context.Items.FindAsync(id); |
SELECT TOP(1) [i].[Id], [i].[Name] FROM [Items] AS [i] WHERE [i].[Id] = @__p_0 |
where
context.Items.Where(x => x.Name == "Item1"); |
SELECT [i].[Id], [i].[Name] FROM [Items] AS [i] WHERE [i].[Name] = @__query_Name_0 |
string contains
context.Items.Where(x => x.Name.Contains("Item")); |
SELECT [i].[Id], [i].[Name] FROM [Items] AS [i] WHERE (@__query_Name_0 LIKE N'') OR (CHARINDEX(@__query_Name_0, [i].[Name]) > 0) |
like
context.Items.Where(x => EF.Functions.Like(x.Name, "%Item%")); |
SELECT [i].[Id], [i].[Name] FROM [Items] AS [i] WHERE [i].[Name] LIKE @__Format_1 |
int contains
context.Items.Where(x => ids.Contains(x.Id)); |
SELECT [i].[Id], [i].[Name] FROM [Items] AS [i] WHERE [i].[Id] IN (1, 2) |
many to many
context.Items.Include(x => x.Categories).ToListAsync(); |
SELECT [i].[Id], [i].[Name], [t].[CategoriesId], [t].[ItemsId], [t].[Id], [t].[Name] FROM [Items] AS [i] LEFT JOIN ( SELECT [c].[CategoriesId], [c].[ItemsId], [c0].[Id], [c0].[Name] FROM [CategoryItem] AS [c] INNER JOIN [Categories] AS [c0] ON [c].[CategoriesId] = [c0].[Id] ) AS [t] ON [i].[Id] = [t].[ItemsId] ORDER BY [i].[Id], [t].[CategoriesId], [t].[ItemsId], [t].[Id] |
update without fetch
// create a new item var itemToUpdate = new Item { Id = 1, Name = "new name" } // attach it then mark the property as modified context.Attach(itemToUpdate).Property(x => x.Name).IsModified = true; await context.SaveChangesAsync(); |
UPDATE [Items] SET [Name] = @p0 WHERE [Id] = @p1; |