个人备查笔记
Q.EF Core 视图的模型映射：
Note：EF的CreateDbDatabase和数据迁移并不会自动创建视图，需要手动创建...
简单例子：
配置类：
public class View_AccountFemaleEFConfig : IEntityTypeConfiguration<View_AccountFemale> { public void Configure(EntityTypeBuilder<View_AccountFemale> builder) { #region //水平拆分处理处 builder.ToView<View_AccountFemale>("View_AccountFemale").SetupBaseEntity(); #endregion } }
上下文：
public class ExamContext : DbContext { virtual public DbSet<View_AccountFemale> view_AccountFemales { get; } protected override void OnModelCreating(ModelBuilder modelBuilder) { // Database 必须存在 View_AccountFemale 视图，否则将报错 modelBuilder.ApplyConfiguration<View_AccountFemale>(new View_AccountFemaleEFConfig()); base.OnModelCreating(modelBuilder); }
Q:EF DateTime 默认值多数据兼容问题：
mysql:NOW()或sysdate()
Sql Server: GetDate()
EF兼容使用 ANSI SQL:
builder.Property(x => x.UpdatedOn).HasDefaultValueSql("CURRENT_TIMESTAMP");
Q:EF/Linq语句转Raw SQL：
代码转载备查,
转载来源：https://stackoverflow.com/questions/37527783/get-sql-code-from-an-entity-framework-core-iqueryablet
EF Core 2.x:
public static class QueryableExtensions
{
private static readonly TypeInfo QueryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();
private static readonly FieldInfo QueryCompilerField = typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryCompiler");
private static readonly FieldInfo QueryModelGeneratorField = typeof(QueryCompiler).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryModelGenerator");
private static readonly FieldInfo DataBaseField = QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");
private static readonly PropertyInfo DatabaseDependenciesField = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");
public static string ToSql<TEntity>(this IQueryable<TEntity> query)
{
var queryCompiler = (QueryCompiler) QueryCompilerField.GetValue(query.Provider);
var queryModelGenerator = (QueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
var queryModel = queryModelGenerator.ParseQuery(query.Expression);
var database = DataBaseField.GetValue(queryCompiler);
var databaseDependencies = (DatabaseDependencies) DatabaseDependenciesField.GetValue(database);
var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
var modelVisitor = (RelationalQueryModelVisitor) queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
var sql = modelVisitor.Queries.First().ToString();
return sql;
}
}
EF Core 3.x:
public static string ToSql<T>(this IQueryable<T> query)
{
var enumerator = query.Provider.Execute<IEnumerable<T>>(query.Expression).GetEnumerator();
var enumeratorType = enumerator.GetType();
var selectFieldInfo = enumeratorType.GetField("_selectExpression", BindingFlags.NonPublic | BindingFlags.Instance) ?? throw new InvalidOperationException($"cannot find field _selectExpression on type {enumeratorType.Name}");
var sqlGeneratorFieldInfo = enumeratorType.GetField("_querySqlGeneratorFactory", BindingFlags.NonPublic | BindingFlags.Instance) ?? throw new InvalidOperationException($"cannot find field _querySqlGeneratorFactory on type {enumeratorType.Name}");
var selectExpression = selectFieldInfo.GetValue(enumerator) as SelectExpression ?? throw new InvalidOperationException($"could not get SelectExpression");
var factory = sqlGeneratorFieldInfo.GetValue(enumerator) as IQuerySqlGeneratorFactory ?? throw new InvalidOperationException($"could not get IQuerySqlGeneratorFactory");
var sqlGenerator = factory.Create();
var command = sqlGenerator.GetCommand(selectExpression);
var sql = command.CommandText;
return sql;
}
Q:EF的Mysql乐观锁实现：
RowVersion只支持Sql Server，如果需要通用（多数据库支持）的乐观锁的话：
以EF Core 为例：
Entity:
abstract public class BaseEntity
{
public Int64 Qing_Version { get; set; }
}
Config：
public class AccountEFConfig : IEntityTypeConfiguration<Account>
{
public void Configure(EntityTypeBuilder<Account> builder)
{
EntityTypeBuilder<Account> tableBuilder = builder.ToTable("Account");
tableBuilder.Property(x => x.Qing_Version).IsConcurrencyToken(true);
}
}
重载重写SaveChange/SaveChangeAsyc：
public class BaseDBContext<DBCtx> : DbContext where DBCtx : DbContext
{
public override int SaveChanges()
{
var validationErrors = this.ChangeTracker;
if (this.ChangeTracker != null)
{
var entities = this.ChangeTracker
.Entries()
.Where( x => x.State == EntityState.Modified &&
x.Entity != null &&
typeof(BaseEntity).IsAssignableFrom(x.Entity.GetType()) )
.Select(x => x)
.ToList();
foreach (var entity in entities)
{
BaseEntity entityBase = entity.Entity as BaseEntity;
entityBase.Qing_UpdateTime = DateTime.Now;
entityBase.Qing_Version++;
}
}
return base.SaveChanges();
}
}
扩展阅读：
但当如果需要做通用的乐观锁支持的话，EF6.x或以上时，需要支持MySQL 的话你需要：
https://stackoverflow.com/questions/7608619/optimistic-concurrency-with-entity-framework-and-mysql
或
https://www.cnblogs.com/easygame/p/4456965.html
EF Core 1.x或以上，需要：
参考 ：https://long2know.com/2016/07/porting-ef6-to-ef7-or-ef-core/