在使用 Entity Framework 作为 ORM 的时候，我们可能会根据得到的某个 IQueryable 查询对象，来“翻译”其最终的执行 SQL 以及传入的参数。经过一番拼凑，我得到了以下的方法，在此记录，便于日后学习和使用。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

/// <summary>
/// 根据 IQueryable 对象，获取其查询数据库的 SQL 语句，以及查询条件的参数值
/// </summary>
/// <param name="queryable">查询对象</param>
/// <param name="ctx">数据库上下文</param>
/// <returns></returns>
public static (string Sql, IReadOnlyDictionary<string, object> Parameters) GetSqlAndParameters(
    this IQueryable queryable, DbContext ctx)
{
    Expression query = queryable.Expression;

    var databaseDependencies = ctx.GetService<DatabaseDependencies>();

    IQueryTranslationPreprocessorFactory _queryTranslationPreprocessorFactory =
        ctx.GetService<IQueryTranslationPreprocessorFactory>();

    IQueryableMethodTranslatingExpressionVisitorFactory _queryableMethodTranslatingExpressionVisitorFactory =
        ctx.GetService<IQueryableMethodTranslatingExpressionVisitorFactory>();

    IQueryTranslationPostprocessorFactory _queryTranslationPostprocessorFactory =
        ctx.GetService<IQueryTranslationPostprocessorFactory>();

    QueryCompilationContext queryCompilationContext =
        databaseDependencies.QueryCompilationContextFactory.Create(true);

    IDiagnosticsLogger<DbLoggerCategory.Query>
        logger = ctx.GetService<IDiagnosticsLogger<DbLoggerCategory.Query>>();

    QueryContext queryContext = ctx.GetService<IQueryContextFactory>().Create();

    QueryCompiler queryCompiler = ctx.GetService<IQueryCompiler>() as QueryCompiler;

    MethodCallExpression methodCallExpr1 =
        queryCompiler.ExtractParameters(query, queryContext, logger, parameterize: true) as MethodCallExpression;

    QueryTranslationPreprocessor queryTranslationPreprocessor =
        _queryTranslationPreprocessorFactory.Create(queryCompilationContext);

    MethodCallExpression methodCallExpr2 =
        queryTranslationPreprocessor.Process(methodCallExpr1) as MethodCallExpression;

    QueryableMethodTranslatingExpressionVisitor queryableMethodTranslatingExpressionVisitor =
        _queryableMethodTranslatingExpressionVisitorFactory.Create(queryCompilationContext);

    ShapedQueryExpression shapedQueryExpression1 =
        queryableMethodTranslatingExpressionVisitor.Visit(methodCallExpr2) as ShapedQueryExpression;

    QueryTranslationPostprocessor queryTranslationPostprocessor =
        _queryTranslationPostprocessorFactory.Create(queryCompilationContext);

    ShapedQueryExpression shapedQueryExpression2 =
        queryTranslationPostprocessor.Process(shapedQueryExpression1) as ShapedQueryExpression;

    IRelationalParameterBasedSqlProcessorFactory _relationalParameterBasedSqlProcessorFactory =
        ctx.GetService<IRelationalParameterBasedSqlProcessorFactory>();

    RelationalParameterBasedSqlProcessor _relationalParameterBasedSqlProcessor =
        _relationalParameterBasedSqlProcessorFactory.Create(true);

    SelectExpression selectExpression = (SelectExpression)shapedQueryExpression2.QueryExpression;

    selectExpression =
        _relationalParameterBasedSqlProcessor.Optimize(selectExpression, queryContext.ParameterValues,
                                                       out bool canCache);

    IQuerySqlGeneratorFactory querySqlGeneratorFactory = ctx.GetService<IQuerySqlGeneratorFactory>();

    QuerySqlGenerator querySqlGenerator = querySqlGeneratorFactory.Create();

    var cmd = querySqlGenerator.GetCommand(selectExpression);
    var parametersDict = queryContext.ParameterValues;
    var sql = cmd.CommandText;
    return (sql, parametersDict);
}