[译]EF6新特性–中

介绍

接下来我将给大家重点介绍一下.Net 6 之后的一些新的变更，文章都是来自于外国大佬的文章，我这边进行一个翻译，并加上一些自己的理解和解释。

源作者链接：https://blog.okyrylchuk.dev/entity-framework-core-6-features-part-1#heading-1-unicode-attribute

正文

列属性

在模型中使用继承时，您可能对创建的表中的默认 EF Core 列顺序不满意。在 EF Core 6.0 中，您可以使用 ColumnAttribute 指定列顺序。

此外，您可以使用新的 Fluent API - HasColumnOrder()来实现。

public class EntityBase
{
[Column(Order = 1)]
public int Id { get; set; }
[Column(Order = 99)]
public DateTime UpdatedOn { get; set; }
[Column(Order = 98)]
public DateTime CreatedOn { get; set; }
}
public class Person : EntityBase
{
[Column(Order = 2)]
public string FirstName { get; set; }
[Column(Order = 3)]
public string LastName { get; set; }
public ContactInfo ContactInfo { get; set; }
}
public class Employee : Person
{
[Column(Order = 4)]
public string Position { get; set; }
[Column(Order = 5)]
public string Department { get; set; }
}
[Owned]
public class ContactInfo
{
[Column(Order = 10)]
public string Email { get; set; }
[Column(Order = 11)]
public string Phone { get; set; }
}
迁移：
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Employees",
columns: table => new
{
Id = table.Column(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
FirstName = table.Column(type: "nvarchar(max)", nullable: true),
LastName = table.Column(type: "nvarchar(max)", nullable: true),
Position = table.Column(type: "nvarchar(max)", nullable: true),
Department = table.Column(type: "nvarchar(max)", nullable: true),
ContactInfo_Email = table.Column(type: "nvarchar(max)", nullable: true),
ContactInfo_PhOne= table.Column(type: "nvarchar(max)", nullable: true),
CreatedOn = table.Column(type: "datetime2", nullable: false),
UpdatedOn = table.Column(type: "datetime2", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Employees", x => x.Id);
});
}


临时表

EF Core 6.0 支持 SQL Server 时态表。可以将表配置为时间戳和历史表的 SQL Server 默认值的临时表。

public class ExampleContext : DbContext
{
public DbSet

People { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity

()
.ToTable("People", b => b.IsTemporal());
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=TemporalTables;Trusted_COnnection=True;");
}
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
迁移：
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "People",
columns: table => new
{
Id = table.Column(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column(type: "nvarchar(max)", nullable: true),
PeriodEnd = table.Column(type: "datetime2", nullable: false)
.Annotation("SqlServer:IsTemporal", true)
.Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
.Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart"),
PeriodStart = table.Column(type: "datetime2", nullable: false)
.Annotation("SqlServer:IsTemporal", true)
.Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
.Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart")
},
constraints: table =>
{
table.PrimaryKey("PK_People", x => x.Id);
})
.Annotation("SqlServer:IsTemporal", true)
.Annotation("SqlServer:TemporalHistoryTableName", "PersonHistory")
.Annotation("SqlServer:TemporalHistoryTableSchema", null)
.Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
.Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
}


您可以使用以下方法查询或检索历史数据：

• 时间AsOf


• 时间全部


• 时间从到


• 时间间隔


• 时间包含在
  
  使用时态表：

using ExampleContext cOntext= new();
context.People.Add(new() { Name = "Oleg" });
context.People.Add(new() { Name = "Steve" });
context.People.Add(new() { Name = "John" });
await context.SaveChangesAsync();
var people = await context.People.ToListAsync();
foreach (var person in people)
{
var persOnEntry= context.Entry(person);
var validFrom = personEntry.Property("PeriodStart").CurrentValue;
var validTo = personEntry.Property("PeriodEnd").CurrentValue;
Console.WriteLine($"Person {person.Name} valid from {validFrom} to {validTo}");
}
// Output:
// Person Oleg valid from 06-Nov-21 17:50:39 PM to 31-Dec-99 23:59:59 PM
// Person Steve valid from 06-Nov-21 17:50:39 PM to 31-Dec-99 23:59:59 PM
// Person John valid from 06-Nov-21 17:50:39 PM to 31-Dec-99 23:59:59 PM


查询历史数据：

var oleg = await context.People.FirstAsync(x => x.Name == "Oleg");
context.People.Remove(oleg);
await context.SaveChangesAsync();
var history = context
.People
.TemporalAll()
.Where(e => e.Name == "Oleg")
.OrderBy(e => EF.Property(e, "PeriodStart"))
.Select(
p => new
{
Person = p,
PeriodStart = EF.Property(p, "PeriodStart"),
PeriodEnd = EF.Property(p, "PeriodEnd")
})
.ToList();
foreach (var pointInTime in history)
{
Console.WriteLine(
$"Person {pointInTime.Person.Name} existed from {pointInTime.PeriodStart} to {pointInTime.PeriodEnd}");
}
// Output:
// Person Oleg existed from 06-Nov-21 17:50:39 PM to 06-Nov-21 18:11:29 PM


检索历史数据：

var removedOleg = await context
.People
.TemporalAsOf(history.First().PeriodStart)
.SingleAsync(e => e.Name == "Oleg");
Console.WriteLine($"Id = {removedOleg.Id}; Name = {removedOleg.Name}");
// Output:
// Id = 1; Name = Oleg


稀疏列

EF Core 6.0 支持 SQL Server 稀疏列。它在使用 TPH（每层次表）继承映射时很有用。

public class ExampleContext : DbContext
{
public DbSet

People { get; set; }
public DbSet Employees { get; set; }
public DbSet Users { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity()
.Property(e => e.Login)
.IsSparse();
modelBuilder
.Entity()
.Property(e => e.Position)
.IsSparse();
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=SparseColumns;Trusted_COnnection=True;");
}
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
public class User : Person
{
public string Login { get; set; }
}
public class Employee : Person
{
public string Position { get; set; }
}
迁移：
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "People",
columns: table => new
{
Id = table.Column(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column(type: "nvarchar(max)", nullable: false),
Discriminator = table.Column(type: "nvarchar(max)", nullable: false),
Position = table.Column(type: "nvarchar(max)", nullable: true)
.Annotation("SqlServer:Sparse", true),
Login = table.Column(type: "nvarchar(max)", nullable: true)
.Annotation("SqlServer:Sparse", true)
},
constraints: table =>
{
table.PrimaryKey("PK_People", x => x.Id);
});
}


结语

联系作者：加群：867095512 @MrChuJiu