abp framework provides a great module development experience. These modules are reusable and also suitable for developing microservices; Since the module can be published independently, its database configuration is also independent. For the module using efcore, each module contains a different Dbcontext;
In Efcore, under the same Dbcontext, multiple entity sets can use linq for arbitrary Association queries, and for association queries under multiple different dbcontexts, even under the same database, they cannot be queried through linq. Let's verify the following framework code through an example. Let's use the code of the file management module in the previous chapter. Suppose we need to associate the file module with the built-in Abp The identity module queries the user's file information, which is required to include the user's name
Before we solve the problem, let's reproduce the problem
First, add a view entity class UserFileView in the Domain project of the file module to receive the results of the union query. The code is as follows
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; }
// User id; if it is not empty, it is a personal file; otherwise, it is a public file
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; }
}
}
Add a data warehouse interface 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
{
// Get paging record according to user Id
Task<List<UserFileView>> GetListAsync(string sorting = null, int maxResultCount = int.MaxValue, int skipCount = 0, Guid? userId = null,
CancellationToken cancellationToken = default);
// Gets the total number of records based on user Id
Task<long> GetCountAsync(Guid? userId = null, CancellationToken cancellationToken = default);
}
}
Then add the data warehouse implementation class UserFilesRepository in the EntityFrameworkCore project
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 module 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)
{
// Gets the DbContext instance of the current module
var dbContext = await GetDbContextAsync();
// Gets the DbContext instance of the Identity module
var identityDbcontext = await GetIdentityDbContext();
// linq query, external connection query of entities between modules
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>
///Get the IdentityDbContext instance of the Identity module
/// </summary>
/// <returns></returns>
protected Task<IIdentityDbContext> GetIdentityDbContext()
{
if (!EntityHelper.IsMultiTenant<IdentityUser>())
{
using (CurrentTenant.Change(null))
{
return _identityDbContextProvider.GetDbContextAsync();
}
}
return _identityDbContextProvider.GetDbContextAsync();
}
}
}
You can see that in the data warehouse implementation, external connection queries are performed on the User table of the Identity module and the BlobFiles table of the file management module, and the query results are returned to the UserFileView set
Test the above code below and open mycompany TestProject. EntityFrameworkCore. Samplerepositorytests. For tests CS, modify to the following code:
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);
});
}
}
}
Right click mycompany TestProject. EntityFrameworkCore. Tests project, select debug test
You can see that the test fails and an exception occurs:
The exception shows that multiple contexts cannot appear in one query
Let's see how to solve this problem. There are two methods:
The first one is very simple. We open mycompany in the main module TestProject. Testprojectdbcontext. For entityframeworkcore project CS file, add the following (the part with comments in the code):
[ReplaceDbContext(typeof(IIdentityDbContext))]
[ReplaceDbContext(typeof(ITenantManagementDbContext))]
[ReplaceDbContext(typeof(IFileManagementDbContext))] // Add file module DbContext
[ConnectionStringName("Default")]
public class TestProjectDbContext : AbpDbContext<TestProjectDbContext>,
IIdentityDbContext,
ITenantManagementDbContext,
IFileManagementDbContext // Add an implementation file module IDbContext interface
{
/* Add DbSet properties for your Aggregate Roots / Entities here. */
#region Entities from the modules
...
// Implement the properties of IFileManagementDbContext
public DbSet<BlobFile> BlobFiles { get; set; }
#endregion
}Adding the ReplaceDbContext modifier in the main module can be understood as using TestProjectDbContext to replace IFileManagementDbContext and iiidentitydbcontext for query when the program is running, which is equivalent to multi module Association query in one DbContext
When we run the test again, we can see that the test has passed
The second uses sql statements to query
This method needs to add the view entity collection in the DbContext of the file module, and the entity cannot be mapped to the database table
First, add in IFileManagementDbContext
DbSet<UserFileView> UserFileView { get; }Add in FileManagementDbContext
public DbSet<UserFileView> UserFileView { get; set; }In filemanagementdbcontextmodelcreatingextensions Add in CS
// It is only used as a query view and does not map database tables
builder.Entity<UserFileView>(b =>
{
b.HasNoKey().ToSqlQuery("select 1");
});In userfilesrepository The method of adding GetQueryFromSql to CS is as follows:
private async Task<IQueryable<UserFileView>> GetQueryFromSql(Guid? userId)
{
// Gets the DbContext instance of the current module
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);
}Change GetQuery in GetCountAsync method to GetQueryFromSql, and then execute the test. You can see that the test has also passed
To sum up, in the first method, linq query is more applicable and convenient to switch various databases. The disadvantage is that some queries can not be realized by linq; The second method needs to write sql statements. In case of complex sql statements, the versions of different databases are different, so it is necessary to write sql providers for different databases
This article uses the same source code as the previous chapter: Multi module Association query of Efcore in Abp Vnext