查询由ICollections连接的多个表

Question

我正在做这个项目，我们得到了一个数据库，首先连接EF数据库。

我正试图检索给定客户的地址。

这是客户级：

[DataContract]
public partial class Customer
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Customer()
    {
        this.Orders = new HashSet<Order>();
        this.WishLists = new HashSet<WishList>();
        this.Addresses = new HashSet<Address>();
        this.Contacts = new HashSet<Contact>();
    }

    [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<Order> Orders { get; set; }

    [DataMember]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public ICollection<WishList> WishLists { get; set; }

    [DataMember]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public ICollection<Address> Addresses { get; set; }

    [DataMember]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public ICollection<Contact> Contacts { get; set; }
}

地址类：

[DataContract]
public partial class Address
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Address()
    {
        this.Orders = new HashSet<Order>();
        this.Customers = new HashSet<Customer>();
        this.Employees = new HashSet<Employee>();
    }

    [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<Order> Orders { get; set; }

    [DataMember]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public ICollection<Customer> Customers { get; set; }

    [DataMember]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public ICollection<Employee> Employees { get; set; }
}

数据库中的这两个表由一个CustomerAddress表连接，该表的EF已转换为公共ICollection客户{ get；set；}和公共ICollection地址{ get；set；}。

我的目标是在我的MVC web应用程序中显示客户的地址。然而，我在试图编写LINQ查询或等效检索地址时遇到了很多困难。到目前为止，我已经尝试过：

// 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;
}

提前谢谢你的帮助。我很难查询两个没有外键关系的表。

Answer 1

您不需要先用Username找到客户，然后再传入以查找其地址。您可以通过一个简单的查询找到它。

要只获取客户地址，请将方法更改为：

    public List<Address> GetAddresses(string UserName)
    {
        var addresses = db.Customers.Where(p=> p.UserName == UserName).SelectMany(a=> a.Addresses).ToList();

        return addresses;
    }

它返回基于客户UserName的地址列表。

您可以使用下面的方法只返回客户的一个地址：

public Address GetAddress(string UserName)
{
    var address = db.Customers.Where(p=> p.UserName == UserName).Select(a=> a.Addresses.FirstOrDefault()).FirstOrDefault();

    return address;
}

希望能帮上忙。

Answer 2

必须在EF集合中使用虚拟，以便按外键为导航属性提供延迟加载。

[DataMember]
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Address> Addresses { get; set; }

之后，您可以像这样使用查询

public Address GetAddress(string UserName)
{
   Address data = db.Customers.Find(c => c.UserName == UserName)?.Addresses?.FirstOrDefault();

   return address;
}