ef core – Mack Blog

最近的项目（后端：.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);
            }
        }
    }
}

标签： ef core

EF Core SQL Server 添加视图映射