9.1 EF Core 如何生成(Hint)注解

EF Core生成mycat的Hint比其它ORM要复杂一些，EF Core的TagWith方法生成的Hint是这这样的。

-- #mycat:db_type=master
SELECT * FROM TABLE1

mycat要求是这样

/*#mycat:db_type=master*/ SELECT * FROM TABLE1

EF Core的TagWith是什么请参考微软官方文档。

我以Pomelo.EntityFrameworkCore.MySql为例，简单点说就是EFCore有一个IQuerySqlGeneratorFactory接口，Pomelo的MySqlQuerySqlGeneratorFactory类实现了这个接口，Create()方法负责创建具体的QuerySqlGenerator，这个类负责查询SQL的生成。

我们需要做三件事情：

点击查看完整源码。

1. 新建工厂类AdncMySqlQuerySqlGeneratorFactory继承MySqlQuerySqlGeneratorFactory并覆写Create()方法。代码如下

namespace Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal
{
    /// <summary>
    /// adnc sql生成工厂类
    /// </summary>
    public class AdncMySqlQuerySqlGeneratorFactory : MySqlQuerySqlGeneratorFactory
    {
        private readonly QuerySqlGeneratorDependencies _dependencies;
        private readonly MySqlSqlExpressionFactory _sqlExpressionFactory;
        private readonly IMySqlOptions _options;

        public AdncMySqlQuerySqlGeneratorFactory(
            [NotNull] QuerySqlGeneratorDependencies dependencies,
            ISqlExpressionFactory sqlExpressionFactory,
            IMySqlOptions options) : base(dependencies, sqlExpressionFactory, options)
        {
            _dependencies = dependencies;
            _sqlExpressionFactory = (MySqlSqlExpressionFactory)sqlExpressionFactory;
            _options = options;
        }

        /// <summary>
        /// 重写QuerySqlGenerator
        /// </summary>
        /// <returns></returns>
        public override QuerySqlGenerator Create()
        {
            var result = new AdncQuerySqlGenerator(_dependencies, _sqlExpressionFactory, _options);
            return result;
        }
    }
}

2. 新建Sql生成类AdncQuerySqlGenerator继承QuerySqlGenerator,覆写两个方法。

namespace Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal
{
    /// <summary>
    /// adnc sql 生成类
    /// </summary>
    public class AdncQuerySqlGenerator : MySqlQuerySqlGenerator
    {
        protected readonly Guid ContextId;
        private bool _isQueryMaseter = false;

        public AdncQuerySqlGenerator(
            [NotNull] QuerySqlGeneratorDependencies dependencies,
            [NotNull] MySqlSqlExpressionFactory sqlExpressionFactory,
            [CanBeNull] IMySqlOptions options)
            : base(dependencies, sqlExpressionFactory, options)
        {
            ContextId = Guid.NewGuid();
        }

        /// <summary>
        /// 获取IQueryable的tags
        /// </summary>
        /// <param name="selectExpression"></param>
        protected override void GenerateTagsHeaderComment(SelectExpression selectExpression)
        {
            if (selectExpression.Tags.Contains(EfCoreConsts.MyCAT_ROUTE_TO_MASTER))
            {
                _isQueryMaseter = true;
                selectExpression.Tags.Remove(EfCoreConsts.MyCAT_ROUTE_TO_MASTER);
            }
            base.GenerateTagsHeaderComment(selectExpression);
        }

        /// <summary>
        /// pomelo最终生成的sql
        /// 该方法主要是调试用
        /// </summary>
        /// <param name="selectExpression"></param>
        /// <returns></returns>
        public override IRelationalCommand GetCommand(SelectExpression selectExpression)
        {
            var command = base.GetCommand(selectExpression);
            return command;
        }

        /// <summary>
        /// 在pomelo生成查询sql前，插入mycat注解
        /// 该注解的意思是从写库读取数据
        /// </summary>
        /// <param name="selectExpression"></param>
        /// <returns></returns>
        protected override Expression VisitSelect(SelectExpression selectExpression)
        {
            if (_isQueryMaseter)
                Sql.Append(string.Concat("/*", EfCoreConsts.MyCAT_ROUTE_TO_MASTER, "*/ "));

            return base.VisitSelect(selectExpression);
        }
    }
}

3. 注册DbContext时替换Pomelo的SQL生成工厂

/// <summary>
/// 注册EfcoreContext
/// </summary>
public virtual void AddEfCoreContext()
{
    _services.AddDbContext<AdncDbContext>(options =>
    {
       options.UseMySql(_mysqlConfig.ConnectionString, mySqlOptions =>
       {
          mySqlOptions.ServerVersion(new ServerVersion(new Version(10, 5, 4), ServerType.MariaDb));
          mySqlOptions.CharSet(CharSet.Utf8Mb4);
       });
       //替换默认查询sql生成器,如果通过mycat中间件实现读写分离需要替换默认SQL工厂。
       options.ReplaceService<IQuerySqlGeneratorFactory, AdncMySqlQuerySqlGeneratorFactory>();
    });
}

使用方法

点击查看完整源码

public class EfCoreConsts
{
    public const string MyCAT_ROUTE_TO_MASTER = "#mycat:db_type=master";
}

public abstract class BaseRepository<TDbContext, TEntity> : IEfRepository<TEntity>
       where TDbContext : DbContext
       where TEntity : EfEntity
{
        public virtual IQueryable<TrdEntity> GetAll<TrdEntity>(bool writeDb = false) where TrdEntity : EfEntity
        {
            var dbSet = DbContext.Set<TrdEntity>().AsNoTracking();
            if (writeDb)
                //读操作路由到写库
                return dbSet.TagWith(EfCoreConsts.MyCAT_ROUTE_TO_MASTER);
            return dbSet;
        }

        public virtual async Task<IEnumerable<TResult>> QueryAsync<TResult>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null, bool writeDb = false)
        {
            if (writeDb)
                //这个方法集成了dapper实现复杂查询,读操作路由到写库
                sql = string.Concat("/* ", EfCoreConsts.MyCAT_ROUTE_TO_MASTER, " */", sql);
            return await DbContext.Database.GetDbConnection().QueryAsync<TResult>(sql, param, null, commandTimeout, commandType);
        }
}

基于mycat要写的代码就是上面这些，数据库连接字符串与直连数据库一样，端口改成mycat的端口。

---