Liens
Description
- Réécriture complète d'EF
- Plus de nécessite d'utilisé des BdD relationnelles uniquement
|
cd MyProject
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.12
|
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"
|
- View → Other Windows → Package Manager Console
- Default Project = the one containing the entity configurations
- Startup Project = the one containing the sql server configuration
|
dotnet ef
dotnet tool install --global dotnet-ef
dotnet tool update --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design
|
DataAccess/MyAppContext.cs
|
public class MyAppContext : DbContext
{
public DbSet<Item> Items => Set<Item>();
}
|
Configure entity types
Conventions
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");
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");
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();
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)
{
modelBuilder.ApplyConfiguration(new ItemConfiguration());
modelBuilder.ApplyConfigurationsFromAssembly(typeof(ItemContext).Assembly);
}
|
Data/Entities/Item.cs
|
[Table("Items")]
public class Item
{
public int Id { get; set; }
[Column("My_Name")]
public string Name { get; set; }
[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
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; }
|
|
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;");
}
}
|
|
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();
|
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=****");
optionsBuilder.UseMySql("name=ConnectionStrings:MariaDb", ServerVersion.Parse("10.11.4-mariadb"));
optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString);
}
}
|
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)
{ }
|
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)
// 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)
{
var builder = new SqlConnectionStringBuilder(Configuration.GetConnectionString("SqlServer"));
builder.Password = Configuration["DbPassword"];
services.AddDbContext<MyAppContext>(options =>
options.UseSqlServer(builder.ConnectionString)
);
services.AddDbContext<MyAppContext>(options =>
options.UseMySql(
Configuration.GetConnectionString("MySql"),
mySqlOptions => mySqlOptions.ServerVersion(
new ServerVersion(new Version(10, 5, 8),
ServerType.MariaDb)));
);
services.AddDbContext<MyAppContext>(options =>
options.UseInMemoryDatabase("WebApplicationCoreMemoryDb");
);
|
DataAccess/MyAppContext.cs
|
public class MyAppContext : DbContext
{
public MyAppContext(DbContextOptions<MyAppContext> options) : base(options)
{ }
|
MyController.cs
|
public class MyController
{
private readonly MyAppContext context;
public MyController(MyAppContext context)
{
this.context = context;
}
|
- 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>();
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
{
public MyAppContext(DbContextOptions<MyAppContext> options) : base(options)
{ }
|
|
var dbContextFactory = new MyAppContextFactory();
using var dbContext = dbContextFactory.CreateDbContext(args);
|
|
dotnet ef migrations add InitialCreate
dotnet ef migrations remove
dotnet ef migrations list
dotnet ef migrations script <FROM> <TO>
dotnet ef database update
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)
{
migrationBuilder.UpdateData(
table: "MyTable",
keyColumn: "Id",
keyValue: 1,
column: "Column1",
value: 1);
migrationBuilder.Sql("UPDATE `MyTable` SET `Column1` = `Column2`;");
}
|
Startup.cs
|
public static void Configure(IApplicationBuilder app, IWebHostEnvironment env, MyDbContext context)
{
context.Database.Migrate();
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.
If you are using migrations and want to have them automatically applied on app start, then you can use Migrate instead. |
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" }
);
}
|
 |
- set the ids even if they belong to an auto-incremented column
- Use the ids to link objects.
|
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" });
_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();
}
}
|
Program.cs
|
public static readonly ILoggerFactory MyLoggerFactory =
LoggerFactory.Create(builder => { builder.AddConsole(); });
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));
|
|
dotnet ef dbcontext scaffold "Server=localhost;Database=MyDb;User=sa;Password=pwd;" Microsoft.EntityFrameworkCore.SqlServer
--output-dir Entities
--context-dir DataAccess
--context "MyDbContext"
--force
--no-onconfiguring
--table table1 --table table2
|
Visual Studio
View → Other Windows → Package Manager Console
|
Get-Help about_EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Tools
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
|
- 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);
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.
|
IEnumerable<Contact> allContacts = context.Contacts;
foreach (var contact in allContacts)
{
if (contact.age > 30) { }
}
IEnumerable<Contact> allContacts = context.Contacts.Include(c => c.Age);
|
|
using System.Data.SqlClient;
var books = context.Books.FromSql("SELECT Id, Title FROM Books").ToList();
var book = context.Books.FromSql($"SELECT Id, Title FROM Books WHERE Title = {title}").FirstOrDefault();
var p1 = new SqlParameter("@Id", id);
var book = db.Books.FromSql("SELECT * FROM Books WHERE Id = @Id", p1).FirstOrDefault();
var commandText = "INSERT Authors (AuthorName) VALUES (@AuthorName)";
var name = new SqlParameter("@AuthorName", "Jean-Christophe Grangé");
context.Database.ExecuteSqlCommand(commandText, name);
var authorId = new SqlParameter("@AuthorId", 1);
var books = context.Books.FromSql("EXEC GetBooksByAuthor @AuthorId" , authorId).ToList();
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);
|
|
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;
}
|
Use the like operator.
|
person.Where(x => x.Name.Contains("Nicolas", StringComparison.CurrentCultureIgnoreCase));
person.Where(x => EF.Functions.Like(x.Name, "%Nicolas%"));
|
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 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)
{
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]);
|
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; }
}
public class Task
{
public int Id { get; set; }
[Required, StringLength(40)]
public string Name { get; set; }
[Required]
public User User { get; set; }
}
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 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)
{
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));
}
}
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);
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);
}
}
var users = await context.Users
.Include(x => x.UserGroups)
.ThenInclude(x => x.Group)
.ToListAsync();
|
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;
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()
{
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
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"
},
|
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);
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.
Installer les packages Nuget:
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.EntityFrameworkCore.Tools.DotNet
MyProject.csproj
|
<Project Sdk="Microsoft.NET.Sdk">
<ItemGroup>
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.0" />
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
</ItemGroup>
|
|
dotnet restore
|
|
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
|
var itemToUpdate = new Item { Id = 1, Name = "new name" }
context.Attach(itemToUpdate).Property(x => x.Name).IsModified = true;
await context.SaveChangesAsync();
|
|
UPDATE [Items]
SET [Name] = @p0
WHERE [Id] = @p1;
|