如何计算嵌套集合/ 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; }
}

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。