最近的项目（后端：.Net Core Web API 、数据库： SQL Server、前端：Blazor WebAssembly、前端Ui框架：Ant Design Blazor）中有些列表页面需要从后端拿到多表查询的结果，一种做法是从后端多个接口分别拿到数据在前端进行整合，比如：
@page "/orders"
@inject IDictService DictService
@inject IUserService UserService
@inject IOrderService OrderService
@inject IProductService ProductService
@inject ICustomerService CustomerService
@inject NavigationManager NavigationManager
@implements IDisposable
@attribute [Authorize]
<PageTitle>全部订单</PageTitle>
<Breadcrumb Style="margin:16px 0;">
<BreadcrumbItem>订单</BreadcrumbItem>
<BreadcrumbItem>全部订单</BreadcrumbItem>
</Breadcrumb>
<div class="site-layout-background" style="padding: 24px;">
<GridRow>
<GridCol Span="24">
<Table TItem="OrderMainDTO" DataSource="@orders" @bind-PageSize="pageSize" ScrollX="1300px">
<ChildContent>
<PropertyColumn Property="c=>c.OrderRequestNo" Title="订单申请编号" Sortable Filterable>
@context.OrderRequestNo
</PropertyColumn>
<PropertyColumn Property="c=>c.OrderCode" Title="外部订单编号" Sortable Filterable>
@context.OrderCode
</PropertyColumn>
<PropertyColumn Property="c=>c.CustomerId" Title="客户" Sortable Filterable>
<Template>
@{
var customer = CustomerService.Customers.FirstOrDefault(c => c.Id == context.CustomerId);
if (customer != null)
{
@customer.CustomerName
}
}
</Template>
</PropertyColumn>
<PropertyColumn Property="c=>c.IndustryId" Title="行业" Sortable Filterable>
<Template>
@{
var industry = ProductService.Industries.FirstOrDefault(c => c.Id == context.IndustryId);
if (industry != null)
{
@industry.IndustryName
}
}
</Template>
</PropertyColumn>
<PropertyColumn Property="c=>c.ProductLineId" Title="产品系列" Sortable Filterable>
<Template>
@{
var productLine = ProductService.ProductLines.FirstOrDefault(c => c.Id == context.ProductLineId);
if (productLine != null)
{
@productLine.ProductLineName
}
}
</Template>
</PropertyColumn>
<PropertyColumn Property="c=>c.SalesOwnerId" Title="销售负责人" Sortable Filterable>
<Template>
@{
var salesOwner = UserService.Employees.FirstOrDefault(c => c.Id == context.SalesOwnerId);
if (salesOwner != null)
{
@salesOwner.FullName
}
}
</Template>
</PropertyColumn>
<PropertyColumn Property="c=>c.ProductLineOwnerId" Title="产品负责人" Sortable Filterable>
<Template>
@{
var productLineOwner = UserService.Employees.FirstOrDefault(c => c.Id == context.ProductLineOwnerId);
if (productLineOwner != null)
{
@productLineOwner.FullName
}
}
</Template>
</PropertyColumn>
<PropertyColumn Property="c=>c.ApprovalStatus" Title="审批状态" Sortable Filterable>
@{
var color = "";
var approvalTag = "";
@switch (context.ApprovalStatus)
{
case 0:
approvalTag = "草稿";
color = "orange";
break;
case 1:
approvalTag = "审批中";
color = "purple";
break;
case 2:
approvalTag = "审批完成";
color = "green";
break;
case 3:
approvalTag = "拒绝";
color = "red";
break;
case 4:
approvalTag = "退回";
color = "magenta";
break;
default:
approvalTag = "无";
color = "default";
break;
}
<Tag Color="@color">
@approvalTag
</Tag>
}
</PropertyColumn>
<PropertyColumn Property="c=>c.ExecutingStatus" Title="执行状态" Sortable Filterable>
@{
var color = "";
var executingTag = "";
@switch (context.ExecutingStatus)
{
case 0:
executingTag = "未执行";
color = "orange";
break;
case 1:
executingTag = "执行中";
color = "geekblue";
break;
case 2:
executingTag = "执行完成";
color = "green";
break;
default:
executingTag = "无";
color = "default";
break;
}
<Tag Color="@color">
@executingTag
</Tag>
}
</PropertyColumn>
<PropertyColumn Property="c=>c.SubmitterId" Title="提交人" Sortable Filterable>
<Template>
@{
var submitter = UserService.Employees.FirstOrDefault(c => c.Id == context.SubmitterId);
if (submitter != null)
{
@submitter.FullName
}
}
</Template>
</PropertyColumn>
<PropertyColumn Property="c=>c.CreateTime" Title="提交时间" Format="yyyy-MM-dd hh:mm:ss" Sortable Filterable></PropertyColumn>
<ActionColumn Title="操作" Width="100" Fixed="right">
<Tooltip Title="查看">
<Button Shape="@ButtonShape.Circle" Icon="@IconType.Outline.Eye" OnClick="() => GoToOrder(context.Id)" />
</Tooltip>
</ActionColumn>
</ChildContent>
<PaginationTemplate>
<Pagination Class="@(context.PaginationClass + " my-custom-pagination")"
Total="context.Total"
PageSize="context.PageSize"
Current="context.PageIndex"
ShowSizeChanger
OnChange="context.HandlePageChange" />
</PaginationTemplate>
</Table>
</GridCol>
</GridRow>
</div>
@code {
List<OrderMainDTO> orders = new();
IEnumerable<Dict> dicts = [];
int total => orders.Count();
int pageSize = 20;
void GoToOrder(Guid orderId)
{
NavigationManager.NavigateTo($"/orders/detail/{orderId}");
}
protected override async Task OnInitializedAsync()
{
var dictResponse = await DictService.GetDicts();
if (dictResponse.Success)
{
dicts = dictResponse.Data?.Count > 0 ? dictResponse.Data : [];
}
// 获取所有行业数据
await ProductService.GetIndustries();
// 获取所有产品线数据
await ProductService.GetProductLines();
// 获取所有产品
await ProductService.GetProducts();
// 获取所有客户数据
await CustomerService.GetCustomers();
// 获取所有用户数据
await UserService.GetEmployees();
var result = await OrderService.GetOrders();
if (result.Success)
{
orders = result.Data != null ? result.Data : [];
}
ProductService.OnChange += StateHasChanged;
CustomerService.OnChange += StateHasChanged;
UserService.OnChange += StateHasChanged;
}
public void Dispose()
{
ProductService.OnChange -= StateHasChanged;
CustomerService.OnChange -= StateHasChanged;
UserService.OnChange -= StateHasChanged;
}
}
从这个Blazor WebAssembly 组件中可以看到为了实现一个列表分别调用了：
行业数据、产品线数据、客户数据、用户数据、字典数据等接口，并且在Table中进行适配，这种方式虽然能实现这个订单列表的页面，然而产生了一个和UI框架的冲突，如下图：
Ant Design Blazor 的 Table 带有过滤器（Filterable），由于 Table 中的各列是遍历的原始值，再通过适配转成了文本，这直接导致过滤器作用的是未适配前的原始值，比如截图中过滤器它应该过滤审批状态，你填入“审批完成”再点确定是查不到任何结果的，审批完成对应的是2，如果你输入2再查询是可以有结果的。
订单列表直接使用视图数据可以减少调用接口的数量也顺带解决掉Table过滤器的这个冲突，EF Core 如何映射视图呢？从微软的官方文档通过关键字没找到合适的内容，搜索引擎上找了一会儿拿到的内容一尝试还是错的，花了一些时间踩坑和试错。
- 数据库中新建好视图
- 后端新建一个用于适配该视图的模型
- DbContext 数据库上下文中新增视图
- 服务和Controller中修改数据库操作和查询返回的类型
- 前端接口请求服务中类型修改
- 前端组件中重新适配新接口以及绑定
新建视图-下列SQL Case When 遇到中文需要在引号前加N，否则会乱码
SELECT
O.Id,
O.OrderRequestNo,
O.OrderCode,
C.CustomerName,
I.IndustryName,
PL.ProductLineName,
O.SalesOwnerId,
U1.FullName AS SalesOwner,
O.SubmitterId,
U3.FullName AS Submitter,
O.ProductLineOwnerId,
U2.FullName AS ProductLineOwner,
O.ApprovalStatus,
CASE
WHEN O.ApprovalStatus = 0 THEN N'草稿'
WHEN O.ApprovalStatus = 1 THEN N'审批中'
WHEN O.ApprovalStatus = 2 THEN N'审批完成'
WHEN O.ApprovalStatus = 3 THEN N'拒绝'
WHEN O.ApprovalStatus = 4 THEN N'退回'
END AS ApprovalStatusName,
O.ExecutingStatus,
CASE
WHEN O.ExecutingStatus = 0 THEN N'未执行'
WHEN O.ExecutingStatus = 1 THEN N'执行中'
WHEN O.ExecutingStatus = 2 THEN N'执行完成'
END AS ExecutingStatusName,
O.CreateTime,
O.IsDisabled
FROM [dbo].[T_OrderMains] AS O
LEFT JOIN [dbo].[T_Industries] AS I ON O.IndustryId = I.Id
LEFT JOIN [dbo].[T_ProductLines] AS PL ON PL.Id = O.ProductLineId
LEFT JOIN [dbo].[T_Users] AS U1 ON U1.Id = O.SalesOwnerId
LEFT JOIN [dbo].[T_Users] AS U2 ON U2.Id = O.ProductLineOwnerId
LEFT JOIN [dbo].[T_Users] AS U3 ON U3.Id = O.SubmitterId
LEFT JOIN [dbo].[T_Customers] AS C ON C.Id = O.CustomerId
新建模型
namespace Gatcis.Plus.Shared.Models.OrderModel
{
public class OrderView
{
public Guid Id { get; set; }
public string? OrderRequestNo { get; set; }
public string? OrderCode { get; set; }
public string? CustomerName { get; set; }
public string? IndustryName { get; set; }
public string? ProductLineName { get; set; }
public Guid SalesOwnerId { get; set; }
public string? SalesOwner { get; set; }
public Guid SubmitterId { get; set; }
public string? Submitter { get; set; }
public Guid ProductLineOwnerId { get; set; }
public string? ProductLineOwner { get; set; }
public int ApprovalStatus { get; set; }
public string? ApprovalStatusName { get; set; }
public int ExecutingStatus { get; set; }
public string? ExecutingStatusName { get;set; }
public DateTime CreateTime { get; set; }
public bool IsDisabled { get; set; }
}
}DbContext 中新增视图-这样去映射视图是不需要进行 Migration （数据库迁移）的
namespace Gatcis.Plus.Service.Data
{
public class IDbContext: IdentityDbContext<IUser, IRole, Guid>
{
// ... 其他
// 订单视图
public DbSet<OrderView> View_OrderLists { get; set; }
public IDbContext(DbContextOptions<IDbContext> options) : base(options) { }
protected override void OnModelCreating(ModelBuilder builder)
{
builder.Entity<OrderView>().ToView("View_OrderLists");
}
}
}修改后端接口服务
public async Task<ServiceResponse<List<OrderView>>> GetMyOrders(Guid operatorId)
{
var response = new ServiceResponse<List<OrderView>>();
var orders = await context.View_OrderLists
.Where(o => o.IsDisabled == false && (o.ProductLineOwnerId == operatorId || o.SalesOwnerId == operatorId || o.SubmitterId == operatorId))
.OrderByDescending(o => o.CreateTime)
.ToListAsync();
response.Code = 200;
response.Data = orders;
return response;
}最终前端页面适配
@page "/orders/my"
@inject IOrderService OrderService
@inject NavigationManager NavigationManager
@attribute [Authorize]
<PageTitle>我的订单</PageTitle>
<Breadcrumb Style="margin:16px 0;">
<BreadcrumbItem>订单</BreadcrumbItem>
<BreadcrumbItem>我的订单</BreadcrumbItem>
</Breadcrumb>
<div class="site-layout-background" style="padding: 24px;">
<GridRow>
<GridCol Span="24">
<Table TItem="OrderView" DataSource="@orders" @bind-PageSize="pageSize" ScrollX="1300px">
<ChildContent>
<PropertyColumn Property="c=>c.OrderRequestNo" Title="订单申请编号" Sortable Filterable>
@context.OrderRequestNo
</PropertyColumn>
<PropertyColumn Property="c=>c.OrderCode" Title="外部订单编号" Sortable Filterable>
@context.OrderCode
</PropertyColumn>
<PropertyColumn Property="c=>c.CustomerName" Title="客户" Sortable Filterable>
@context.CustomerName
</PropertyColumn>
<PropertyColumn Property="c=>c.IndustryName" Title="行业" Sortable Filterable>
@context.IndustryName
</PropertyColumn>
<PropertyColumn Property="c=>c.ProductLineName" Title="产品系列" Sortable Filterable>
@context.ProductLineName
</PropertyColumn>
<PropertyColumn Property="c=>c.SalesOwner" Title="销售负责人" Sortable Filterable>
@context.SalesOwner
</PropertyColumn>
<PropertyColumn Property="c=>c.ProductLineOwner" Title="产品负责人" Sortable Filterable>
@context.ProductLineOwner
</PropertyColumn>
<PropertyColumn Property="c=>c.ApprovalStatusName" Title="审批状态" Sortable Filterable>
@{
var color = "";
@switch (context.ApprovalStatus)
{
case 0:
color = "orange";
break;
case 1:
color = "purple";
break;
case 2:
color = "green";
break;
case 3:
color = "red";
break;
case 4:
color = "magenta";
break;
}
<Tag Color="@color">
@context.ApprovalStatusName
</Tag>
}
</PropertyColumn>
<PropertyColumn Property="c=>c.ExecutingStatusName" Title="执行状态" Sortable Filterable>
@{
var color = "";
@switch (context.ExecutingStatus)
{
case 0:
color = "orange";
break;
case 1:
color = "geekblue";
break;
case 2:
color = "green";
break;
}
<Tag Color="@color">
@context.ExecutingStatusName
</Tag>
}
</PropertyColumn>
<PropertyColumn Property="c=>c.Submitter" Title="提交人" Sortable Filterable>
@context.Submitter
</PropertyColumn>
<PropertyColumn Property="c=>c.CreateTime" Title="提交时间" Format="yyyy-MM-dd hh:mm:ss" Sortable Filterable></PropertyColumn>
<ActionColumn Title="操作" Width="100" Fixed="right">
@if (context.ApprovalStatus == 1 && context.ProductLineOwnerId == currentUserId)
{
<Tooltip Title="审批">
<Button Shape="@ButtonShape.Circle" Icon="@IconType.Outline.Check" OnClick="() => GoToOrder(context.Id)" />
</Tooltip>
}
else
{
<Tooltip Title="查看">
<Button Shape="@ButtonShape.Circle" Icon="@IconType.Outline.Eye" OnClick="() => GoToOrder(context.Id)" />
</Tooltip>
}
</ActionColumn>
</ChildContent>
<PaginationTemplate>
<Pagination Class="@(context.PaginationClass + " my-custom-pagination")"
Total="context.Total"
PageSize="context.PageSize"
Current="context.PageIndex"
ShowSizeChanger
OnChange="context.HandlePageChange" />
</PaginationTemplate>
</Table>
</GridCol>
</GridRow>
</div>
@code {
[CascadingParameter]
private Task<AuthenticationState>? AuthenticationState { get; set; }
public ClaimsPrincipal? authenticatedUser { get; set; }
Guid? currentUserId = null;
List<OrderView> orders = new();
int total => orders.Count();
int pageSize = 20;
void GoToOrder(Guid orderId)
{
NavigationManager.NavigateTo($"/orders/detail/{orderId}");
}
protected override async Task OnInitializedAsync()
{
var result = await OrderService.GetMyOrders();
if (result.Success)
{
orders = result.Data != null ? result.Data : [];
}
if (AuthenticationState is not null)
{
var state = await AuthenticationState;
authenticatedUser = state.User;
string? userId = authenticatedUser.FindFirst(c => c.Type.Contains("nameidentifier"))?.Value;
if (userId != null)
{
currentUserId = Guid.Parse(userId);
}
}
}
}
