Asp.net Core Razor Pages多参数查询仅返回单参数结果求助

问题场景 #

传递3个ecs参数到页面的OnGet方法，执行查询后仅能看到最后一个参数对应的数据，排查后发现是OnGet方法的foreach循环中每次直接赋值给EvarisListCS，覆盖了之前的查询结果。

相关代码 #

1. 参数传递的Post方法 #

public IActionResult OnPostEvaluareA(string ea, string[] ecs, string ezl)
{
    return RedirectToPage("/RO/Pages/evaris/Index", new { ea, ezl, ecs });
}

2. 执行查询的Get方法 #

public async Task<IActionResult> OnGetAsync(string ea, string ezl, string[] ecs, string emlm, int count)
{
    if(ecs != null)
    {
        foreach(var item in ecs)
        {
            EvarisListCS = await (from a in _context.EvarisCaracterSpecials.Where(s => s.OrgID == orgid && s.ECS == item)
                                join b in _context.EvarisMains on a.ECS equals b.ECS into Temp1
                                from c in Temp1
                                join d in _context.EvarisMasuras on c.EMID equals d.EMID into Temp2
                                from e in Temp2
                                select new EvarisMasuraList
                                {
                                    ID = a.ECS,
                                    Pericol = c.Pericol,
                                    FormaManifestare = c.FormaManifestare,
                                    RiscInitial = c.Risc,
                                    Clasa = e.Clasa,
                                    Masura = e.Masura,
                                    RiscFinal = e.RiscFinal
                                }).ToListAsync();

            if (EvarisListCS.Count > 0)
            {
                HaveListCS = true;
            }
        }
    }
}

3. 前端展示代码 #

@if (Model.HaveListCS == true)
{
    <div class="row no-gutters">
        <div class="col-sm-2">
            <label class="form-control text-center" style="background-color:dodgerblue; color:whitesmoke">Pericol</label>
        </div>
        <div class="col-sm-3">
            <label class="form-control text-center" style="background-color:dodgerblue; color:whitesmoke">Forma de manifestare</label>
        </div>
        <div class="col-sm-1">
            <label class="form-control text-center" style="background-color:dodgerblue; color:whitesmoke">Risc initial</label>
        </div>
        <div class="col-sm-2">
            <label class="form-control text-center" style="background-color:dodgerblue; color:whitesmoke">Clasa</label>
        </div>
        <div class="col-sm-3">
            <label class="form-control text-center" style="background-color:dodgerblue; color:whitesmoke">Masura</label>
        </div>
        <div class="col-sm-1">
            <label class="form-control text-center" style="background-color:dodgerblue; color:whitesmoke">Risc final</label>
        </div>
    </div>
    @foreach (var group in Model.EvarisListCS.GroupBy(s => s.ID))
    {
        <div class="row no-gutters">
            <span class="form-control" style="font-size: 18px; background-color: antiquewhite; font-weight: 600">@Html.DisplayFor(modelItem => group.Key)</span>
        </div>
        foreach (var item in group)
        {
            <div class="row no-gutters mb-2">
                <div class="col-sm-2">
                    <textarea rows="2" class="form-control text-center" style="font-size: 12px; resize: none; ">@Html.DisplayFor(model => item.Pericol)</textarea>
                </div>
                <div class="col-sm-3">
                    <textarea rows="2" class="form-control text-center" style="font-size: 12px; resize: none; ">@Html.DisplayFor(model => item.FormaManifestare)</textarea>
                </div>
                <div class="col-sm-1">
                    <textarea rows="2" class="form-control text-center" style="font-size: 12px; resize: none; ">@Html.DisplayFor(model => item.RiscInitial)</textarea>
                </div>
                <div class="col-sm-2">
                    <textarea rows="2" class="form-control text-center" style="font-size: 12px; resize: none; ">@Html.DisplayFor(model => item.Clasa)</textarea>
                </div>
                <div class="col-sm-3">
                    <textarea rows="2" class="form-control text-center" style="font-size: 12px; resize: none; ">@Html.DisplayFor(model => item.Masura)</textarea>
                </div>
                <div class="col-sm-1">
                    <textarea rows="2" class="form-control text-center" style="font-size: 12px; resize: none; ">@Html.DisplayFor(model => item.RiscFinal)</textarea>
                </div>
            </div>
        }
    }
}

解决方案 #

方案1：修改循环逻辑，累加结果 #

在foreach循环中，不要直接覆盖EvarisListCS，而是将每次查询的结果添加到列表中。需要先初始化EvarisListCS为一个空列表：

public async Task<IActionResult> OnGetAsync(string ea, string ezl, string[] ecs, string emlm, int count)
{
    // 先初始化列表
    EvarisListCS = new List<EvarisMasuraList>();
    if(ecs != null)
    {
        foreach(var item in ecs)
        {
            var tempList = await (from a in _context.EvarisCaracterSpecials.Where(s => s.OrgID == orgid && s.ECS == item)
                                join b in _context.EvarisMains on a.ECS equals b.ECS into Temp1
                                from c in Temp1
                                join d in _context.EvarisMasuras on c.EMID equals d.EMID into Temp2
                                from e in Temp2
                                select new EvarisMasuraList
                                {
                                    ID = a.ECS,
                                    Pericol = c.Pericol,
                                    FormaManifestare = c.FormaManifestare,
                                    RiscInitial = c.Risc,
                                    Clasa = e.Clasa,
                                    Masura = e.Masura,
                                    RiscFinal = e.RiscFinal
                                }).ToListAsync();

            // 将临时列表的元素添加到总列表中
            EvarisListCS.AddRange(tempList);
            
            if (tempList.Count > 0)
            {
                HaveListCS = true;
            }
        }
    }
}

方案2：优化查询，一次性获取所有参数结果（推荐） #

避免循环查询数据库，直接用Contains方法筛选所有ecs参数对应的数据，减少数据库请求次数，提升性能：

public async Task<IActionResult> OnGetAsync(string ea, string ezl, string[] ecs, string emlm, int count)
{
    if(ecs != null && ecs.Length > 0)
    {
        EvarisListCS = await (from a in _context.EvarisCaracterSpecials.Where(s => s.OrgID == orgid && ecs.Contains(s.ECS))
                            join b in _context.EvarisMains on a.ECS equals b.ECS into Temp1
                            from c in Temp1
                            join d in _context.EvarisMasuras on c.EMID equals d.EMID into Temp2
                            from e in Temp2
                            select new EvarisMasuraList
                            {
                                ID = a.ECS,
                                Pericol = c.Pericol,
                                FormaManifestare = c.FormaManifestare,
                                RiscInitial = c.Risc,
                                Clasa = e.Clasa,
                                Masura = e.Masura,
                                RiscFinal = e.RiscFinal
                            }).ToListAsync();

        HaveListCS = EvarisListCS.Count > 0;
    }
}

这样修改后，就能一次性获取所有ecs参数对应的全部数据，不会出现覆盖的问题，同时性能更优。

内容的提问来源于stack exchange，提问作者NMV