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
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.
internalsealedclassItemConfiguration : IEntityTypeConfiguration<Item>
{
publicvoidConfigure(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) CHARACTERSET utf8mb4
.HasColumnType("nvarchar(50)")
.HasCharSet("utf8mb4") // ifnot specified the database character setand 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
protectedoverridevoidOnModelCreating(ModelBuildermodelBuilder)
{
// specify the configuration
modelBuilder.ApplyConfiguration(newItemConfiguration());
// look for EntityTypeConfiguration in the current assembly
modelBuilder.ApplyConfigurationsFromAssembly(typeof(ItemContext).Assembly);
}
// définit le nom de la table, par défaut le nom de la classe est utilisées
[Table("Items")]
publicclassItem
{
publicint Id { get; set; }
[Column("My_Name")] // mappingpublicstring 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)")]
publicdecimal Price { get; set; }
[Column(TypeName = "date")]
publicDateTime Date { get; set; }
}
Nécessite le package nuget System.ComponentModel.DataAnnotations
# create a new migration
dotnet ef migrations add View_BlogPostCounts
Migration/YYYYMMDDHHmmss_View_BlogPostCounts.cs
publicpartialclassView_BlogPostCounts : Migration
{
protectedoverridevoidUp(MigrationBuildermigrationBuilder)
{
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;");
}
protectedoverridevoidDown(MigrationBuildermigrationBuilder)
{
migrationBuilder.Sql("DROP VIEW View_BlogPostCounts;");
}
}
# update the database
dotnet ef database update
Controllers/BlogController.cs
[HttpGet]
publicasyncTask<IEnumerable<BlogDto>> Get()
{
varquery = from b inthis.dbContext.Blogs
join c inthis.dbContext.BlogPostCounts
on b.BlogId equals c.BlogId
selectnewBlogDto
{
BlogId = b.BlogId,
Url = b.Url,
PostsCount = c.PostCount,
Posts = b.Posts.Select(y => newPostDto
{
PostId = y.PostId,
Title = y.Title,
Content = y.Content
})
};
vardto = await query.ToListAsync();
// MariaDbvarconnectionString = builder.Configuration.GetConnectionString("MariaDb");
varserverVersion = newMariaDbServerVersion(newVersion(10, 11, 4));
builder.Services.AddDbContext<MyAppContext>(
dbContextOptions => dbContextOptions
.UseMySql(connectionString, serverVersion)
// TODOThe following three options help with debugging, but should be changed or removed for production.
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging()
.EnableDetailedErrors());
Old
Startup.cs
publicvoidConfigureServices(IServiceCollectionservices)
{
// SQL Servervarbuilder = newSqlConnectionStringBuilder(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(
newServerVersion(newVersion(10, 5, 8),
ServerType.MariaDb)));
// mySqlOptions => mySqlOptions.ServerVersion("10.5.8-mariadb");
);
// In Memory
services.AddDbContext<MyAppContext>(options =>
options.UseInMemoryDatabase("WebApplicationCoreMemoryDb");
);
DataAccess/MyAppContext.cs
publicclassMyAppContext : DbContext
{
// permet de passer des options à la construction du DbContextpublicMyAppContext(DbContextOptions<MyAppContext> options) : base(options)
{ }
publicclassMyAppContext : DbContext
{
// permet de passer des options à la construction du DbContextpublicMyAppContext(DbContextOptions<MyAppContext> options) : base(options)
{ }
# 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:
publicstaticvoidConfigure(IApplicationBuilderapp, IWebHostEnvironmentenv, MyDbContextcontext)
{
// 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.
publicstaticreadonly 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 valuein a try-catch block
.UseMySql(connectionString, serverVersion));
# 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 installationGet-Help about_EntityFrameworkCore
# install if not yet installedInstall-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
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 contactsIEnumerable<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 performancesif (contact.age > 30) { /* ... */ }
}
// inclure Age lors de la récupération des contacts// ainsi tous se fait en une seule requêteIEnumerable<Contact> allContacts = context.Contacts.Include(c => c.Age);
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.
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.
<ProjectSdk="Microsoft.NET.Sdk"><!-- Ajouter le groupe suivant --><ItemGroup><DotNetCliToolReferenceInclude="Microsoft.EntityFrameworkCore.Tools"Version="2.0.0" /><DotNetCliToolReferenceInclude="Microsoft.EntityFrameworkCore.Tools.DotNet"Version="2.0.0" /></ItemGroup>
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()
selectnew { grp, user };
// create a new itemvaritemToUpdate = newItem { 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();