C#导出CSV时SQL查询结果重复行无法完全去除问题

问题背景 #

开发了一款控制台应用，核心功能为从数据库抓取数据后导出为CSV文件，近期发现导出文件存在重复行，尝试多种方案均无法完全移除所有重复数据：

• 数据查询SQL未添加GROUP BY子句时，共返回2083行数据，其中包含210条重复行
• 给SQL添加GROUP BY后返回2031行，仍残留158条无法去除的重复行
• 编写内存级DataTable去重方法处理数据后，去重完全不生效，返回结果行数仍为2031行
• 重复数据可被验证：导出的文件用Excel打开后，使用Excel自带的“删除重复值”功能可以移除大量重复行
• 初步怀疑SQL查询编写逻辑存在问题，但无法定位具体错误点

相关实现代码 #

历史数据查询SQL生成方法 #

private static string HistoryQuery(Guid itemDetailId) {
   var sql = $@"
Select IH.ItemDetailID, i.Type, v.Number, v.Title, ISNULL(CAST(V.MajorRevisionNumber AS VARCHAR(10) ), '') +'.'+
ISNULL(CAST(V.MinorRevisionNumber AS VARCHAR(10) ) , '')  AS RevisionNumber,
Ih.Action, id2.Title AS ActionedBy, Ih.ActionedDate, Ih.Comment, iwft.Type as TaskType, iwft.StartDate,
iwft.CompletedDate, Iwft.Status, Iwft.Outcome, id3.Title AS TaskActionedBy, Iwfta.ActionDate, Iwfta.Action AS TaskAction, Iwfta.Comment AS TaskComment, lid.URL
From ItemView v
Join ItemHistory Ih
    On Ih.ItemDetailID = v.ItemDetailId
Join Item i
    On i.ItemID = v.ItemId
Left Outer join ItemWorkflowTask Iwft
    On Iwft.ItemDetailID = v.ItemDetailId
Left outer Join ItemWorkflowTaskAction Iwfta
    On Iwfta.ItemWorkflowTaskID = Iwft.ItemWorkflowTaskID
Left outer Join DocumentItemDetail did 
    ON did.ItemDetailID = Ih.ItemDetailID
Left outer Join ItemDetail id
    ON id.ItemID = did.LinkItemID
Left outer Join LinkItemDetail lid
    ON lid.ItemDetailID = id.ItemDetailID
Left outer Join ItemDetail id2
    ON id2.ItemID = ih.ActionedBy
Left outer Join ItemDetail id3
    ON id3.ItemID = iwfta.ActionedBy
Where IH.ItemDetailID = '{itemDetailId}'
AND Ih.Action NOT IN ('Access', 'AddToFavourites', 'ItemDefaultFavouriteChanged', 'RemoveFromFavourites', 'View')
AND (I.Type = 'Document' OR I.Type = 'ProcessMap')
GROUP BY
IH.ItemDetailID, i.Type, v.Number, v.Title, ISNULL(CAST(V.MajorRevisionNumber AS VARCHAR(10) ), '') +'.'+
ISNULL(CAST(V.MinorRevisionNumber AS VARCHAR(10) ) , ''),
Ih.Action, id2.Title, Ih.ActionedDate, Ih.Comment, iwft.Type, iwft.StartDate,
iwft.CompletedDate, Iwft.Status, Iwft.Outcome, id3.Title, Iwfta.ActionDate, Iwfta.Action, Iwfta.Comment, lid.URL";

    return sql;
}

数据提取与清洗逻辑 #

public DataTable GetExtractData(List<Guid>ItemDetailIds)
{
    var historyTable = new DataTable();
    using var cnn = new SqlConnection(_connectionString);
    foreach(var itemDetailId in ItemDetailIds)
    {
        AddDataToTable(HistoryQuery(itemDetailId), cnn, historyTable);
    }


    DataTable replaceNulls = historyTable.Clone();
#pragma warning disable CS8602 // Dereference of a possibly null reference.
    replaceNulls.Columns["TaskActionedBy"].DataType = typeof(string);
#pragma warning restore CS8602 // Dereference of a possibly null reference.
    foreach (DataRow row in historyTable.Rows)
    {
        replaceNulls.ImportRow(row);
    }

    foreach (DataRow row in replaceNulls.Rows)
    {
        if (row["TaskActionedBy"] is DBNull)
        {
            row["TaskActionedBy"] = "";
        }

       row["Comment"]= row["Comment"].ToString().Replace("
", "").Replace("
", "");
       row["TaskComment"] = row["Comment"].ToString().Replace("
", "").Replace("
", "");

    }

    return replaceNulls;
}

private static DataTable AddDataToTable(string query, SqlConnection connection, DataTable tableToFill)
{
    using var cmd = new SqlCommand(query, connection);

    var adapter = new SqlDataAdapter(cmd);
    adapter.Fill(tableToFill);

    return tableToFill;
}

已尝试的重复行移除方法 #

private DataTable RemoveDuplicateRecords(DataTable dt)
{
    var uniqueRows = dt.AsEnumerable().Distinct(DataRowComparer.Default);
    DataTable uniqueData = uniqueRows.CopyToDataTable();
    return uniqueData;
}

该方法在GetExtractData中被调用，用于对完成空值处理的replaceNulls表做去重后返回，但未产生任何去重效果。

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