Dapper等ORM开发记录

个人备查笔记

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();

        }
    }