3

I've got CodeFirst collection defined as defined below.

For any given EmailOwnerId, I want to count the number of EmailDetailAttachments records exist without actually downloading all the images themselves.

I know I can do something like

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();
}

but that means I've already downloaded all the bytes of images from my far away server.

Any suggestion would be appreciated.

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
  • 14,748
  • 25
  • 102
  • 188

1 Answers1

4

The engine should be able to update this to a COUNT(*) statement. 

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

But you'll have to verify if this produces the right (and more efficient) SQL.

H H
  • 263,252
  • 30
  • 330
  • 514