Abp.VNext-拆分查询

Abp默认采用的是拆分查询，优点是提高性能，缺点是使用Linq进行多表关联操作时打印查询字符串得到的SQL语句是单表查询语句。而实际上代码执行的是多表关联查询，容易误导开发人员。

例如下列LINQ查询是多表关联，但是得到的查询字符串是单表操作。

 var query = (await _blogRepository.GetQueryableAsync()).Include(x=>x.Posts);
 var queryable = await _blogRepository.WithDetailsAsync(x => x.Posts);

 //转换成查询字符串
 string sqlStr1 = query.ToQueryString();
 string sqlStr2 = queryable.ToQueryString();

得到的查询字符串如下所示，

SET @__ef_filter__p_0 = FALSE;


SELECT `t`.`Id`, `t`.`ConcurrencyStamp`, `t`.`CreationTime`, `t`.`Description`, `t`.`IsDeleted`, `t`.`LastModificationTime`, `t`.`Name`, `t`.`ShortName`, `t`.`Url`
FROM `t_blog` AS `t`
WHERE (@__ef_filter__p_0 OR NOT (`t`.`IsDeleted`)) 
ORDER BY `t`.`Id`

This LINQ query is being executed in split-query mode, and the SQL shown is for the first query to be executed. Additional queries may also be executed depending on the results of the first query.

解决办法

方法1：使用AsSingleQuery().ToQueryString()代替ToQueryString()

string sqlStr2 = query.AsSingleQuery().ToQueryString();

方法2：全局配置使用单个查询

[DependsOn(typeof(AbpEntityFrameworkCoreMySQLModule))]
public class EntityFrameworkCoreModule : AbpModule
{
    public override void ConfigureServices(ServiceConfigurationContext context)
    {
        Configure<AbpDbContextOptions>(options =>
        {
            //默认使用拆分查询，提高性能。
            //options.UseMySQL();   
            
            //全局配置使用单个查询
            options.UseMySQL(config => config.UseQuerySplittingBehavior(QuerySplittingBehavior.SingleQuery));
        });
    }
}

 var query = (await _blogRepository.GetQueryableAsync()).Include(x=>x.Posts);
string sqlStr2 = query.ToQueryString();

SET @__ef_filter__p_0 = FALSE;
SET @__ef_filter__p_1 = FALSE;

SELECT `t`.`Id`, `t`.`ConcurrencyStamp`, `t`.`CreationTime`, `t`.`Description`, `t`.`IsDeleted`, `t`.`LastModificationTime`, `t`.`Name`, `t`.`ShortName`, `t`.`Url`, `t0`.`Id`, `t0`.`BlogId`, `t0`.`ConcurrencyStamp`, `t0`.`Content`, `t0`.`CoverImage`, `t0`.`CreationTime`, `t0`.`Description`, `t0`.`IsDeleted`, `t0`.`LastModificationTime`, `t0`.`ReadCount`, `t0`.`Title`, `t0`.`Url`
FROM `t_blog` AS `t`
LEFT JOIN (
  SELECT `t1`.`Id`, `t1`.`BlogId`, `t1`.`ConcurrencyStamp`, `t1`.`Content`, `t1`.`CoverImage`, `t1`.`CreationTime`, `t1`.`Description`, `t1`.`IsDeleted`, `t1`.`LastModificationTime`, `t1`.`ReadCount`, `t1`.`Title`, `t1`.`Url`
  FROM `t_post` AS `t1`
  WHERE @__ef_filter__p_1 OR NOT (`t1`.`IsDeleted`)
) AS `t0` ON `t`.`Id` = `t0`.`BlogId`
WHERE (@__ef_filter__p_0 OR NOT (`t`.`IsDeleted`)) 
ORDER BY `t`.`Id`