C# linq entity-framework-4 linq-to-entities

LINQ - 对存储为字符串的数字求和

发布于 2024-12-27 20:21:20 字数 639 浏览 2 评论 0原文

我有以下数据：

PK    OrderNumber      USERDEFFIELD
1     0001             10
2     0001             25
3     0002             20
4     0002             22
5     0002             NULL
6     0003             ABC123

UserDefField 列在数据库中是 VARCHAR 类型。使用 LINQ，如何获取每个订单的 SUM(UserDefField)？ UserDefField 的 NULL 和非数字值将被视为零。我试图得到的结果：

OrderNumber  TotalQty
0001         35
0002         42
0003          0

如果 UserDefField 是严格可为空的数字字段，我知道我会在 foreach 循环中执行此操作：

 TotalQtyForThisOrder = orders.Sum(w => w.UserDefField ?? 0 );

但对于字符串字段，应该做什么？非常感谢您的帮助。

原文

I have the following data:

PK    OrderNumber      USERDEFFIELD
1     0001             10
2     0001             25
3     0002             20
4     0002             22
5     0002             NULL
6     0003             ABC123

The UserDefField column is of VARCHAR type in the database. Using LINQ, how can I get the SUM(UserDefField) per order? NULL and non-numeric values for UserDefField are to be considered as zero. The result I'm trying to get:

OrderNumber  TotalQty
0001         35
0002         42
0003          0

If UserDefField is strictly nullable numeric field I know I would do this inside a foreach loop:

 TotalQtyForThisOrder = orders.Sum(w => w.UserDefField ?? 0 );

But for a string field, what should be done? Thank you very much for the help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故笙诉离歌 2025-01-03 20:21:20

TotalQtyForThisOrder = orders.Sum( w = > {
            int result;
            Int32.TryParse(w.UserDefField, out result);
            return result;
});

TotalQtyForThisOrder = orders.Sum( w = > {
            int result;
            Int32.TryParse(w.UserDefField, out result);
            return result;
});

回复收藏 0 原文

花开半夏魅人心 2025-01-03 20:21:20

从根本上说，我想说你的模式是错误的：如果你将文本值视为一个数字，如果它恰好可以这样解析，那么你真的是在反对合理的设计。

我不知道如何编写 LINQ 查询来使该总和出现在数据库中。您可能想要编写一些自定义转换函数，或者可能是一个自定义视图，它执行“尝试解析它并返回 0 否则”操作。

您可以在 LINQ to Objects 中轻松完成此操作：

x = orders.Select(y => y.UserDefField) // To just bring down the right values
          .AsEnumerable()
          .Sum(text => {
                   int result;
                   // We don't care about the return value... and
                   // it even handles null input. (In any error condition,
                   // "result" will be 0 afterwards.)
                   int.TryParse(text, out result);
                   return result;
               });

...但我真的建议您如果可能的话重新访问您的架构。

Fundamentally, I'd say your schema is off: if you're treating a textual value as a number if it happens to be parsable that way, you're really fighting against sensible design.

I don't know how you'd be able to write a LINQ query to make that sum occur in the database. You'd probably want to write some custom conversion function, or perhaps a custom view which did the "try to parse it and return 0 otherwise" operation.

You can do it easily enough within LINQ to Objects:

x = orders.Select(y => y.UserDefField) // To just bring down the right values
          .AsEnumerable()
          .Sum(text => {
                   int result;
                   // We don't care about the return value... and
                   // it even handles null input. (In any error condition,
                   // "result" will be 0 afterwards.)
                   int.TryParse(text, out result);
                   return result;
               });

... but I'd really recommend that you revisit your schema if you possibly can.

回复收藏 0 原文

喜爱纠缠 2025-01-03 20:21:20

我会像这样使用聚合函数：

var result = orders.Aggregate(new Dictionary<OrderIdType, int>(), (accumulator, item) => {
    int quantity;
    if (!int.TryParse(item.USERDEFFIELD, out quantity))
        quantity = 0;
    if (!accumulator.ContainsKey(item.OrderId))
       accumulator[item.OrderId] = quantity;
    else
       accumulator[item.OrderId] += quantity;
    return accumulator;
});

I would use the Aggregate function like so:

var result = orders.Aggregate(new Dictionary<OrderIdType, int>(), (accumulator, item) => {
    int quantity;
    if (!int.TryParse(item.USERDEFFIELD, out quantity))
        quantity = 0;
    if (!accumulator.ContainsKey(item.OrderId))
       accumulator[item.OrderId] = quantity;
    else
       accumulator[item.OrderId] += quantity;
    return accumulator;
});

回复收藏 0 原文

~没有更多了~