abp框架提供了非常棒的模块开发体验,这些模块是可复用的,并且也适用于开发微服务;既然模块可以独立发布,那么它的数据库配置也是独立的,对于使用efcore的模块,每个模块中都包含一个不同的Dbcontext;
在Efcore中,同一个Dbcontext下,多个实体集合间是可以使用linq进行任意关联查询的,而对于多个不同Dbcontext下的关联查询,即时在同一数据库下,也是不能通过linq查询的。下面通过示例验证以下,框架代码还是使用上一章文件管理模块的代码吧,假设我们需要关联文件模块和Abp内置Abp.Identity模块,查询出用户的文件信息,要求包含用户姓名
在解决问题之前,我们先复现下问题
首先在文件模块的Domain项目中添加一个视图实体类UserFileView,用于接收联合查询的结果,代码如下
using System;
namespace MyCompany.FileManagement.Entities
{
public class UserFileView
{
public long FileSize { get; set; }
public string MimeType { get; set; }
public string Path { get; set; }
public string Name { get; set; }
// 所属用户id,不为空的为个人文件,否则为公共文件
public Guid? OwnerId { get; set; }
public string SurName { get; set; }
public string UserName { get; set; }
public DateTime CreationTime { get; set; }
public DateTime? LastModificationTime { get; set; }
}
}
添加一个数据仓库接口IUserFilesRepository:
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Volo.Abp.Domain.Repositories;
namespace MyCompany.FileManagement.Entities
{
public interface IUserFilesRepository: IRepository
{
// 根据用户Id获取分页记录
Task<List<UserFileView>> GetListAsync(string sorting = null, int maxResultCount = int.MaxValue, int skipCount = 0, Guid? userId = null,
CancellationToken cancellationToken = default);
// 根据用户Id获取记录总数
Task<long> GetCountAsync(Guid? userId = null, CancellationToken cancellationToken = default);
}
}
然后在EntityFrameworkCore项目中添加数据仓库实现类UserFilesRepository
using Microsoft.EntityFrameworkCore;
using MyCompany.FileManagement.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Volo.Abp.Domain.Entities;
using Volo.Abp.Domain.Repositories.EntityFrameworkCore;
using Volo.Abp.EntityFrameworkCore;
using Volo.Abp.Identity;
using Volo.Abp.Identity.EntityFrameworkCore;
namespace MyCompany.FileManagement.EntityFrameworkCore
{
public class UserFilesRepository: EfCoreRepository<IFileManagementDbContext, BlobFile>, IUserFilesRepository
{
// Identity身份模块DbContextProvider
private readonly IDbContextProvider<IIdentityDbContext> _identityDbContextProvider;
public UserFilesRepository(
IDbContextProvider<IFileManagementDbContext> dbContextProvider, IDbContextProvider<IIdentityDbContext> identityDbContextProvider)
: base(dbContextProvider)
{
_identityDbContextProvider = identityDbContextProvider;
}
public async Task<List<UserFileView>> GetListAsync(string sorting = null, int maxResultCount = int.MaxValue, int skipCount = 0, Guid? userId = null, CancellationToken cancellationToken = default)
{
var query = await GetQuery(userId);
return await query
.OrderBy(sorting.IsNullOrWhiteSpace() ? (nameof(UserFileView.FileSize) + " desc") : sorting)
.PageBy(skipCount, maxResultCount)
.ToListAsync(GetCancellationToken(cancellationToken));
}
public async Task<long> GetCountAsync(Guid? userId = null, CancellationToken cancellationToken = default)
{
var query = await GetQuery(userId);
return await query.LongCountAsync();
}
private async Task<IQueryable<UserFileView>> GetQuery(Guid? userId)
{
// 获取当前模块的DbContext实例
var dbContext = await GetDbContextAsync();
// 获取Identity模块的DbContext实例
var identityDbcontext = await GetIdentityDbContext();
// linq查询,模块间实体的外连接查询
var query = from f in dbContext.BlobFiles
from u in identityDbcontext.Users.Where(i => i.Id == f.OwnerId).DefaultIfEmpty()
select new UserFileView
{
Name = f.Name,
FileSize = f.FileSize,
MimeType = f.MimeType,
OwnerId = f.OwnerId,
Path = f.Path,
CreationTime = f.CreationTime,
LastModificationTime = f.LastModificationTime,
SurName = u.Surname,
UserName = u.Name
};
return query
.WhereIf(userId.HasValue, r => r.OwnerId == userId);
}
/// <summary>
/// 获取Identity模块的IdentityDbContext实例
/// </summary>
/// <returns></returns>
protected Task<IIdentityDbContext> GetIdentityDbContext()
{
if (!EntityHelper.IsMultiTenant<IdentityUser>())
{
using (CurrentTenant.Change(null))
{
return _identityDbContextProvider.GetDbContextAsync();
}
}
return _identityDbContextProvider.GetDbContextAsync();
}
}
}
可以看到在数据仓库实现中,对Identity模块的User表和文件管理模块的BlobFiles表进行外连接查询,将查询结果返回到UserFileView集合中
测试以下以上代码,打开MyCompany.TestProject.EntityFrameworkCore.Tests的SampleRepositoryTests.cs,修改为以下代码:
using Shouldly;
using System.Threading.Tasks;
using Xunit;
using MyCompany.FileManagement.Entities;
namespace MyCompany.TestProject.EntityFrameworkCore.Samples
{
/* This is just an example test class.
* Normally, you don't test ABP framework code
* (like default AppUser repository IRepository<AppUser, Guid> here).
* Only test your custom repository methods.
*/
public class SampleRepositoryTests : TestProjectEntityFrameworkCoreTestBase
{
private readonly IUserFilesRepository _userFilesRepository;
public SampleRepositoryTests()
{
_userFilesRepository = GetRequiredService<IUserFilesRepository>();
}
[Fact]
public async Task Should_Query_AppUser()
{
/* Need to manually start Unit Of Work because
* FirstOrDefaultAsync should be executed while db connection / context is available.
*/
await WithUnitOfWorkAsync(async () =>
{
//Act
var adminUser = await _userFilesRepository.GetCountAsync();
//Assert
adminUser.ShouldBeGreaterThan(0);
});
}
}
}
右键MyCompany.TestProject.EntityFrameworkCore.Tests项目,选择调试测试
可以看到测试不通过,出现了异常:
异常显示多个Context不能出现在一个查询中
下面我们来看看如何解决这个问题,有两种方法:
第一种很简单,我们打开主模块中MyCompany.TestProject.EntityFrameworkCore项目的TestProjectDbContext.cs文件,添加如下内容(代码中添加注释的部分):
[ReplaceDbContext(typeof(IIdentityDbContext))]
[ReplaceDbContext(typeof(ITenantManagementDbContext))]
[ReplaceDbContext(typeof(IFileManagementDbContext))] // 添加文件模块DbContext
[ConnectionStringName("Default")]
public class TestProjectDbContext : AbpDbContext<TestProjectDbContext>,
IIdentityDbContext,
ITenantManagementDbContext,
IFileManagementDbContext // 添加实现文件模块IDbContext接口
{
/* Add DbSet properties for your Aggregate Roots / Entities here. */
#region Entities from the modules
...
// 实现IFileManagementDbContext 的属性
public DbSet<BlobFile> BlobFiles { get; set; }
#endregion
}
在主模块中添加ReplaceDbContext修饰,可以理解成在程序运行时使用TestProjectDbContext来替换IFileManagementDbContext和IIdentityDbContext等进行查询,这样就相当于在一个DbContext中进行多模块关联查询了
我们再运行下测试,可以看到已经测试通过了
第二种使用sql语句查询
这种方法需要在文件模块的DbContext中添加视图实体集合,同时不能将实体映射到数据库表
首先在IFileManagementDbContext中添加
DbSet<UserFileView> UserFileView { get; }
在FileManagementDbContext中添加
public DbSet<UserFileView> UserFileView { get; set; }
在FileManagementDbContextModelCreatingExtensions.cs中添加
// 仅用作查询视图,不映射数据库表
builder.Entity<UserFileView>(b =>
{
b.HasNoKey().ToSqlQuery("select 1");
});
在UserFilesRepository.cs中添加GetQueryFromSql方法如下:
private async Task<IQueryable<UserFileView>> GetQueryFromSql(Guid? userId)
{
// 获取当前模块的DbContext实例
var dbContext = await GetDbContextAsync();
string sql = @"SELECT f.Name, f.FileSize, f.MimeType, f.Path, f.OwnerId, f.CreationTime, f.ConcurrencyStamp,
u.Surname as SurName, u.Name as UserName
FROM filemanagementblobfiles f left join abpusers u on f.OwnerId = u.Id";
return dbContext.UserFileView.FromSqlRaw(sql)
.WhereIf(userId.HasValue, r => r.OwnerId == userId);
}
将GetCountAsync方法中的GetQuery改成GetQueryFromSql,然后执行测试,可以看到测试也通过了
综上,第一种方法,使用linq查询适用性更高,切换各种数据库方便,缺点是有些查询linq实现不了;第二种方法需要编写sql语句,遇到复杂sql语句不同数据库的版本不一样,这样就要编写不同数据库的sql 提供程序
本文跟上一章使用同一份源码:Abp Vnext 中Efcore的多模块关联查询