如何在嵌套集合/CodeFirst EntityFramework中计算项数

Question

我有一个如下定义的CodeFirst集合。

对于任何给定的EmailOwnerId，我想计算存在多少个EmailDetailAttachments记录，而不实际下载所有图片本身。

我知道我可以做这样的事情：

var emailsToView = (from data in db.EmailDetails.Include("EmailDetailAttachments")
                        where data.EmailAccount.EmailOwnerId = 999
                        select data).ToList();
int cnt = 0;    
foreach (var email in emailsToView)
{
     cnt += email.EmailDetailAttachments.Count();
}

但这意味着我已经从我的远程服务器下载了所有图像的字节。

欢迎提出任何建议。

public class EmailDetail
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int EmailOwnerId {get;set;}

    public virtual ICollection<ImageDetail> EmailDetailAttachments { get; set; }
    ..
}

public class ImageDetail
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [MaxLengthAttribute(256)]
    public string FileName { get; set; }

    [MaxLengthAttribute(256)]
    public string ContentMimeType { get; set; }

    public byte[] ImageDataBytes { get; set; }
    public DateTime ImageCreation { get; set; }
}

- Peter Kellner

Answer 1

引擎应该能够将其更新为COUNT(*)语句。

  var emailsToView = (from data in db.EmailDetails  // no Include
                    where data.EmailAccount.EmailOwnerId = 999
                    select new { 
                      Detail = data, 
                      Count=data.EmailDetailAttachments.Count() }
                     ).ToList();

但是你需要验证一下是否产生了正确且更高效的SQL。

- H H

---

谢谢Henk，技巧非常棒。我将来肯定会使用它。我之前没想到过这个。 - Peter Kellner