Counting records in associated tables in Entity Framework

The following example shows how to query a table and count records associated in another. Model

// Base table entity
public abstract class TableBase
{
    [Key]
    public int id { get; set; }
}


// Customer entity
public class Customer: TableBase
{                    
    // customer fields   
    public string Name { get; set; }        
    public int? Type { get; set; }

    // contacts collection
    public virtual ICollection Contacts { get; set; }
}

// Contact entity
public class Contact : TableBase
{
    // contact fields
    public string FirstName { get; set; }
    public string LastName { get; set; }        
}

// Customer View entity (not mapped)
[NotMapped]
public class CustomerView
{
    // define customer
    public Customer Customer { get; set; }

    // define count of contacts
    public int ContactCount { get; set; }
}

Querying data

private void countContacts()
{
    // get the database context
    using (var db = new Data.Db())
    {
        // select the data, project to customer view 
        var c = db.Customers
            .ToList()
            .Select(x => new CustomerView
            {
                Customer = x,
                ContactCount = x.Contacts.Count
            });
        
        // traverse
        foreach (var i in c)
        {
            Console.WriteLine(“{0}: {1}”, i.Customer.Name, i.ContactCount);
        }                
    }
}

---