|
|
Ligne 777 : |
Ligne 777 : |
| } | | } |
| </kode> | | </kode> |
| | |
| | = [https://docs.microsoft.com/en-us/ef/core/testing/ Unit tests] = |
| | <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(); |
| | 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 = |
Version du 14 mars 2021 à 16:11
Liens
Description
- Réécriture complète d'EF
- Plus de nécessite d'utilisé des BdD relationnelles uniquement
|
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)
|
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
|
InMemory |
Microsoft.EntityFrameworkCore.InMemory |
databaseName: "test_database"
|
|
# 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
|
EF Core 3.x
|
# 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
|
ASP.NET Core 2.1+
|
Plus besoin depuis dotnet core 2.1 (lien) |
Si le fichier de projet ne contient pas Microsoft.EntityFrameworkCore.Tools.DotNet, l'ajouter. (version)
À la sauvegarde NuGet va restaurer les paquets nécessaires.
MyProject.csproj
|
<ItemGroup>
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.3" />
</ItemGroup>
|
DataAccess/MyAppContext.cs
|
public class MyAppContext : DbContext
{
public DbSet<Item> Items { get; set; }
}
|
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"); // 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/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
Data/MyAppContext.cs
|
public class MyAppContext : DbContext
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<MyQueryType>().ToView("MyView");
|
Connection string
OnConfiguring
DataAccess/MyAppContext.cs
|
public class MyAppContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("server=localhost;database=test;user=test;password=****");
optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString); // WPF
}
}
|
As 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)
{ }
|
Startup.cs
|
using Microsoft.EntityFrameworkCore;
public IConfiguration Configuration { get; }
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public void ConfigureServices(IServiceCollection services)
{
// SQL Server
services.AddDbContext<AppContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("SqlServer"))
);
// MySql
services.AddDbContext<AppDbContext>(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<AppContext>(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 ApplicationDbContext context;
public MyController(ApplicationDbContext 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);
|
|
# 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`;");
}
|
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.
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" });
}
|
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();
}
}
|
Startup.cs
|
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<AppContext>(options =>
options.UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()))
.UseSqlServer(Configuration.GetConnectionString("SqlServer"))
);
|
Program.cs
|
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
options.LogTo(Console.WriteLine);
|
|
# generate entity classes and context class
dotnet ef dbcontext scaffold "Server=localhost;Database=MyDb;User=sa;Password=pwd;" Microsoft.EntityFrameworkCore.SqlServer
--output-dir Model # output folder for entities
--context-dir DataAccess # output folder for DbContext
--context "MyDbContext" # default context name: DbNameContext
--force # overwrite all the class files
--table table1 --table table2 # scaffold only table1 and table2
|
- 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>();
|
|
public Task<Items> GetAsync()
=> this.context.Item
.Include(x => x.Property1) // load item.Property1
.ThenInclude(x => x.SubProperty1) // load item.Property1.SubProperty1
.ToListAsync();
|
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);
|
|
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);
|
|
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.
|
// 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%"));
|
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)
{
// 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]);
|
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 User per Group and many Group per User.
|
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);
}
|
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();
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
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);
// 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.
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
|
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]
|