如何在我的Linq to Sql代码中修复这个Count语句?

本文关键字：Count 语句代码我的 Linq Sql to | 更新日期: 2023-09-27 18:16:12

我有以下的Linq-to-SQL查询。在第5行，我试图从我的Packages表中获得记录的数量，其中我的where语句中列出的条件得到满足。我认为这段代码中除了第5行之外一切都是正确的。我做错了什么?

var Overage = from s in db.Subscriptions
    join p in db.Packages on s.UserID equals p.UserID
    where 
      s.SubscriptionType != "PayAsYouGo" && 
      (s.CancelDate == null || s.CancelDate >= day) && 
      s.StartDate <= day && p.DateReceived <= day && 
      (p.DateShipped == null || p.DateShipped >= day)
    let AverageBoxSize = (p.Height * p.Length * p.Width) / 1728
    let ActiveBoxCount = p.Count()
    select new
    {
      p,
      AverageBoxSize,
      ActiveBoxCount,
      s.Boxes
    };

错误信息是"Unknown method Count() of Foo.Data.Package"

订阅表:

UserID | Boxes
001      5
002      25
003      5

Boxes是每个用户在其订阅下允许的最大数目

包表

UserID | PackageID | Height | Length | Width
001      00001       10       10       10
001      00002       10       10       10
001      00003       20       10       10
003      00004       10       20       20
003      00005       10       10       10

期望查询结果

UserID | Boxes | Box Count | Average Box Size
001      5           3           1,333
003      5           2           2,500

用户002没有出现，因为Where子句排除了该用户

如何在我的Linq to Sql代码中修复这个Count语句?

从你对Ryan的回答的评论来看，这可能是你想要的:

var Overage = from s in db.Subscriptions
    join p in db.Packages on s.UserID equals p.UserID
    where 
      s.SubscriptionType != "PayAsYouGo" && 
      (s.CancelDate == null || s.CancelDate >= day) && 
      s.StartDate <= day && p.DateReceived <= day && 
      (p.DateShipped == null || p.DateShipped >= day)
    select new
    {
      p,
      AverageBoxSize = (p.Height * p.Length * p.Width) / 1728,
      ActiveBoxCount = db.Packages.Where(x => x.UserID == p.UserID).Count(),
      s.Boxes
    }

p不是集合。它应该是:let ActiveBoxCount = db.Packages.Count()

将Let行替换为-

可能是最简单的方法。

Group <columns> By UserId into summary = Group 
Aggregate x in summary Into ActiveBoxCount = x.Count

相关文章：

最新信息: