会议议程导入：如何实现重复值校验与精准错误提示？

需求校验规则 #

• 每行必须包含Meeting ID，且所有行的Meeting ID需一致，缺失则返回错误并终止流程；
• Agenda Item为必填项且不可重复，缺失或重复则返回错误；
• 若Legistar ID缺失，将Agenda Item的值赋值给Legistar ID；
• Agenda Item和Legistar ID的值均需唯一，存在重复则返回错误并终止流程。

待校验测试数据 #

AgendaItem  LegistarID  Title
1   74490   Public Comment. (October 31 2022)
2   74491   Welcome Affirmative Action Commission Appointment. <-- Agenda Item 2
2   73403   Nomination Open Update.                            <-- Agenda Item 2
4   74490   Communication Strategy Update.                     <-- Legistar Id same as row 1

当前ImportData方法代码 #

public ActionResult ImportData(List<MeetingAgendaXref_WIP> meeting) // see comment above procedure name
{

    bool status = false;
    string message = "";

    try
    {
        if (ModelState.IsValid)
        {
            var errors = new List<string>();
            var rowCounter = 1;

            using (ClerkEntities db = new ClerkEntities())
            {
                foreach (var i in meeting)
                {

                    if (i.MeetingID == 0)
                    {

                        message = string.Format("No Meeting ID. Please make sure that the Cross Reference meets the required criteria for each row before clicking the ""Upload"" button.");
                        // log error to list
                        errors.Add($"Row {rowCounter}: No Meeting ID. Please make sure that the Cross Reference meets the required criteria for each row before clicking the ""Upload"" button.");
                        return new JsonResult { Data = new { status = status, message = message } };

                    }

                    if (i.AgendaItem == 0)
                    {
                        message = string.Format("No Agenda Item. Please make sure that the Cross Reference file meets the required criteria for each row before clicking the ""Upload"" button.");
                        // log error to list
                        errors.Add($"{rowCounter}:No Agenda Item. Please make sure that the Cross Reference file meets the required criteria for each row before clicking the ""Upload"" button.");
                        return new JsonResult { Data = new { status = status, message = message } };
                    }

                    // if Legistar ID, 
                    if (i.LegistarID == 0)
                    {
                        // and Agenda Item are not present, return error message, log error message
                        if (i.AgendaItem == 0)
                        {
                            message = string.Format("Agenda Item, and Legistar Id are missing. Please make sure that the Cross Reference file meets the required criteria for each row before clicking the ""Upload"" button.");
                            // log error to list
                            errors.Add("Agenda Item, and Legistar Id are missing. Please make sure that the Cross Reference file meets the required criteria for each row before clicking the ""Upload"" button.");
                            return new JsonResult { Data = new { status = status, message = message } };
                        }
                        // otherwise, if legistar id, is empty, but agenda item is not, then assign Agenda Item to Legistar Id.
                        else
                        {
                            i.LegistarID = i.AgendaItem;
                        }
                    }

                    var compositeKey = db.MeetingAgendaXref_WIP.Find(i.MeetingID, i.AgendaItem);

                    if (compositeKey == null)
                    {
                        // Add new
                        db.MeetingAgendaXref_WIP.Add(i);

                    }
                    else
                    {
                        // Update previously saved values (same or new)
                        db.Entry(compositeKey).CurrentValues.SetValues(i);
                        db.Entry(compositeKey).State = EntityState.Modified;
                    }
                    rowCounter++;
                }
                
                // If there are errors do not save and return error message
                if (errors.Count > 0)
                {
                    return new JsonResult { Data = new { status = status, message = string.Join("
", errors) } };
                }
                else
                {
                    db.SaveChanges();
                    message = string.Format(@"Your Cross Reference file has been uploaded successfuly!");
                    status = true;
                    return new JsonResult { Data = new { status = status, message = message } };
                }
            }
        }
        else
        {
            message = string.Format(@"Please make sure that the Cross Reference file meets the required criteria for each row before clicking the ""Upload"" button.");
            return new JsonResult {Data = new {status = status, message = message}};
        }
    }
    catch (System.ArgumentException ex)
    {
        status = false;
        ExceptionLogging.WriteLogError(ex.ToString());
    }
    return new JsonResult {Data = new {status = status, message = message}};
}

修改后的代码实现 #

public ActionResult ImportData(List<MeetingAgendaXref_WIP> meeting)
{
    bool status = false;
    string message = "";
    var errors = new List<string>();
    int rowCounter = 1;

    try
    {
        if (ModelState.IsValid && meeting.Any())
        {
            // 校验规则1：所有行Meeting ID一致且非空
            var firstMeetingId = meeting.First().MeetingID;
            if (firstMeetingId == 0)
            {
                errors.Add($"Row 1: Meeting ID缺失，请补充后重新上传");
            }
            else
            {
                foreach (var item in meeting.Skip(1))
                {
                    rowCounter++;
                    if (item.MeetingID == 0)
                    {
                        errors.Add($"Row {rowCounter}: Meeting ID缺失，请补充后重新上传");
                    }
                    else if (item.MeetingID != firstMeetingId)
                    {
                        errors.Add($"Row {rowCounter}: Meeting ID与第1行的{firstMeetingId}不一致，请修正后重新上传");
                    }
                }
            }

            rowCounter = 1;
            var existingAgendaItems = new HashSet<int>();
            var existingLegistarIds = new HashSet<int>();

            foreach (var i in meeting)
            {
                // 校验规则2：Agenda Item必填且不重复
                if (i.AgendaItem == 0)
                {
                    errors.Add($"Row {rowCounter}: Agenda Item缺失，请补充后重新上传");
                }
                else if (!existingAgendaItems.Add(i.AgendaItem))
                {
                    errors.Add($"Row {rowCounter}: Agenda Item {i.AgendaItem}重复，已在之前行出现，请修正后重新上传");
                }

                // 规则3：Legistar ID缺失时赋值为Agenda Item
                if (i.LegistarID == 0)
                {
                    i.LegistarID = i.AgendaItem;
                }

                // 校验规则4：Legistar ID唯一
                if (!existingLegistarIds.Add(i.LegistarID))
                {
                    errors.Add($"Row {rowCounter}: Legistar ID {i.LegistarID}重复，已在之前行出现，请修正后重新上传");
                }

                rowCounter++;
            }

            // 有错误则返回，不执行数据库操作
            if (errors.Any())
            {
                message = string.Join("\n", errors);
                return new JsonResult { Data = new { status = status, message = message } };
            }

            // 数据库操作
            using (ClerkEntities db = new ClerkEntities())
            {
                foreach (var i in meeting)
                {
                    var compositeKey = db.MeetingAgendaXref_WIP.Find(i.MeetingID, i.AgendaItem);
                    if (compositeKey == null)
                    {
                        db.MeetingAgendaXref_WIP.Add(i);
                    }
                    else
                    {
                        db.Entry(compositeKey).CurrentValues.SetValues(i);
                        db.Entry(compositeKey).State = EntityState.Modified;
                    }
                }

                db.SaveChanges();
                message = @"会议议程交叉引用文件上传成功！";
                status = true;
                return new JsonResult { Data = new { status = status, message = message } };
            }
        }
        else
        {
            message = @"请确保交叉引用文件每行都符合要求后再点击上传按钮";
            return new JsonResult { Data = new { status = status, message = message } };
        }
    }
    catch (Exception ex)
    {
        status = false;
        message = $"上传过程中发生错误：{ex.Message}";
        ExceptionLogging.WriteLogError(ex.ToString());
        return new JsonResult { Data = new { status = status, message = message } };
    }
}

修改说明 #

1. 规则1校验：先获取第一行的Meeting ID，遍历后续行检查是否一致且非空，精准提示缺失或不一致的行号和问题。
2. 规则2校验：使用HashSet跟踪已出现的Agenda Item，重复时直接提示行号和重复值。
3. 规则3实现：提前处理Legistar ID缺失的赋值逻辑，确保后续规则4校验基于正确的值。
4. 规则4校验：同样用HashSet跟踪Legistar ID，重复时给出明确的行号和重复值提示。
5. 错误收集逻辑：先收集所有错误再一次性返回，避免中途终止导致用户看不到全部问题；错误信息明确指向具体行和问题点，方便用户定位修正。
6. 异常处理优化：捕获所有异常并返回友好提示，同时保留日志记录。

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