title: Querying multiple tables connected by ICollections
body: <p>I am working on this project where we were given a database to connect with EF database first.</p>
  
  <p>I am trying to retrieve the address for a given customer.</p>
  
  <p>Here is the Customer class:</p>
  
  <pre><code>[DataContract]
  public partial class Customer
  {
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
      public Customer()
      {
          this.Orders = new HashSet&lt;Order&gt;();
          this.WishLists = new HashSet&lt;WishList&gt;();
          this.Addresses = new HashSet&lt;Address&gt;();
          this.Contacts = new HashSet&lt;Contact&gt;();
      }
  
      [DataMember]
      public int CustomerID { get; set; }
  
      [DataMember]
      public string UserName { get; set; }
  
      [DataMember]
      public string FirstName { get; set; }
  
      [DataMember]
      public string LastName { get; set; }
  
      [DataMember]
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public ICollection&lt;Order&gt; Orders { get; set; }
  
      [DataMember]
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public ICollection&lt;WishList&gt; WishLists { get; set; }
  
      [DataMember]
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public ICollection&lt;Address&gt; Addresses { get; set; }
  
      [DataMember]
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public ICollection&lt;Contact&gt; Contacts { get; set; }
  }
  </code></pre>
  
  <p>And the Address class:</p>
  
  <pre><code>[DataContract]
  public partial class Address
  {
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
      public Address()
      {
          this.Orders = new HashSet&lt;Order&gt;();
          this.Customers = new HashSet&lt;Customer&gt;();
          this.Employees = new HashSet&lt;Employee&gt;();
      }
  
      [DataMember]
      public int AddressID { get; set; }
  
      [DataMember]
      public int AddressType { get; set; }
  
      [DataMember]
      public string Street { get; set; }
  
      [DataMember]
      public string Suburb { get; set; }
  
      [DataMember]
      public string City { get; set; }
  
      [DataMember]
      public string PostalCode { get; set; }
  
      [DataMember]
      public string Country { get; set; }
  
      [DataMember]
      public AddressType AddressType1 { get; set; }
  
      [DataMember]
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public ICollection&lt;Order&gt; Orders { get; set; }
  
      [DataMember]
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public ICollection&lt;Customer&gt; Customers { get; set; }
  
      [DataMember]
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public ICollection&lt;Employee&gt; Employees { get; set; }
  }
  </code></pre>
  
  <p>These two tables in the database are connected by a CustomerAddress table which EF has turned into public ICollection Customers { get; set; } and public ICollection Addresses { get; set; }.</p>
  
  <p>My goal is to display a customer's address in my MVC web application. However, I am having a lot of trouble trying to write a LINQ query or equivalent to retrieve the address. So far I have tried:</p>
  
  <pre><code>// Return the details for current customer (this function works as expected)
  public Customer CurrentCustomer(string UserName)
  {
      Customer data = (from c in db.Customers
                       where c.UserName == UserName
                       select c).SingleOrDefault(); 
  
      return data;
  }
  
  // Return the address details for current customer (this function does not work)
  public Address GetAddress(string UserName)
  {
      // function to retrieve the CustomerID of the current customer
      var customerID = CurrentCustomer(UserName).CustomerID;
  
      Address data = db.Customers.Find(customerID).Addresses.FirstOrDefault();
  
      return address;
  }
  </code></pre>
  
  <p>Thank you in advance for your help. I am struggling to query two tables without a foreign key relationship.</p>