处理2份清单

Question

我有xml文件来读取和处理数据。我有详细记录。我的要求是删除相同的数字和RefNo的记录，但有正和负值。

场景1:应该返回1条记录

<Detail>
    <Number>1</Number>
    <Amount>20.0</Amount>
    <RefNo>1</RefNo>
</Detail>

场景2:应该返回0记录，因为数量是正负

<Detail>
    <Number>1</Number>
    <Amount>20.0</Amount>
    <RefNo>1</RefNo>
</Detail>
<Detail>
    <Number>1</Number>
    <Amount>-20.0</Amount>
    <RefNo>1</RefNo>
</Detail>

我用了两个列表来表示正值和负值。我使用以下代码解决了上述情况。

var actualRecords =
    (from detailList in positiveDetails
    where !negativeDetails.Any(x => x.Number == detailList.PolicyNo
                                 && x.RefNo == detailList. RefNo)
    select detailList).ToList(); 

但是上面的代码在下面的场景中返回零条记录。阳性>阴性>阳性。对于相同的数目和RefNo，应返回1条记录取消1条正记录和1条阴性记录。

我想要解决以下方案与相同的号码和参考编号。

*阳性>阴性>阳性-1记录

*正>负>正>负-0记录

*阳性>阴性>阳性>阴性>阳性-1记录

*阳性>阴性>阳性>阴性>阴性-0记录

*正>负>正>负>正>n

假设负值总是出现在正数之后。我可以通过循环来完成这个任务。但我在寻找更好的解决方案。感谢有人能帮我。

Answer 1

你仅仅是在聚合细节，还是真的试图把彼此完全抵消的细节匹配起来？前者更容易写。

例如，对于这样的文档

<Root>
    <Details>
        <Detail>
            <Number>1</Number>
            <Amount>40.0</Amount>
            <RefNo>1</RefNo>
        </Detail>
        <Detail>
            <Number>1</Number>
            <Amount>-20.0</Amount>
            <RefNo>1</RefNo>
        </Detail>
        <Detail>
            <Number>1</Number>
            <Amount>20.0</Amount>
            <RefNo>1</RefNo>
        </Detail>
        <Detail>
            <Number>1</Number>
            <Amount>-30.0</Amount>
            <RefNo>2</RefNo>
        </Detail>
        <Detail>
            <Number>1</Number>
            <Amount>20.0</Amount>
            <RefNo>1</RefNo>
        </Detail>
    </Details>
</Root>

XDocument doc = ...;
var details = doc.XPathSelectElement("/Root/Details");
var newDetails =
    from detail in details.Elements("Detail")
    let amount = (decimal)detail.Element("Amount")
    let number = (int)detail.Element("Number")
    let refNo = (int)detail.Element("RefNo")
    // group amounts by number and refno
    group amount by new { number, refNo } into g
    let amount = g.Sum()
    // filter out completely canceled out groups
    where amount != 0M
    select new XElement("Detail",
        new XElement("Number", g.Key.number),
        new XElement("Amount", amount.ToString("N1")),
        new XElement("RefNo", g.Key.refNo)
    );
details.ReplaceAll(newDetails);

产量：

<Root>
  <Details>
    <Detail>
      <Number>1</Number>
      <Amount>60.0</Amount>
      <RefNo>1</RefNo>
    </Detail>
    <Detail>
      <Number>1</Number>
      <Amount>-30.0</Amount>
      <RefNo>2</RefNo>
    </Detail>
  </Details>
</Root>

否则，您需要匹配相互抵消的元素。它仍然可以在一个查询中完成，尽管这个查询稍微复杂一些。

XDocument doc = ...;
var details = doc.XPathSelectElement("/Root/Details");
var newDetails =
    from detail in details.Elements("Detail")
    let amount = (decimal)detail.Element("Amount")
    let number = (int)detail.Element("Number")
    let refNo = (int)detail.Element("RefNo")
    let key = Math.Abs(amount) // cancellable amounts
    // group amounts by key, number and refno
    group amount by new { key, number, refNo } into g
    let amount = g.Sum()
    // filter out completely canceled out groups
    where amount != 0M
    let count = (int)Math.Abs(amount / g.Key.key) // how many to recreate
    let sign = Math.Sign(amount)
    // recreate uncancelled values
    from a in Enumerable.Repeat(sign * g.Key.key, count)
    select new XElement("Detail",
        new XElement("Number", g.Key.number),
        new XElement("Amount", a.ToString("N1")),
        new XElement("RefNo", g.Key.refNo)
    );
details.ReplaceAll(newDetails);

产量：

<Root>
  <Details>
    <Detail>
      <Number>1</Number>
      <Amount>40.0</Amount>
      <RefNo>1</RefNo>
    </Detail>
    <Detail>
      <Number>1</Number>
      <Amount>20.0</Amount>
      <RefNo>1</RefNo>
    </Detail>
    <Detail>
      <Number>1</Number>
      <Amount>-30.0</Amount>
      <RefNo>2</RefNo>
    </Detail>
  </Details>
</Root>

Answer 2

我想一种方法是GroupBy，Number和RefNo，如果计数不匹配，则返回正和负Amount，然后返回这些Detail元素

类似于：

var actualRecords = detailList.GroupBy(x => new{ x.Number, x.RefNo}) // group by Number and RefNo
                              .Where(x => x.Count(p => p.Amount > 0) // count positives
                                       != x.Count(n => n.Amount < 0)) // count negatives
                              .SelectMany(detail => detail); // contains the Details that have unequal amounts of positive and negative amounts