linq - ef6 使用网桥查询多对多

Question

我如何查询这种多对多关系？我从 ACCOUNT 开始，并希望返回与其关联的 ExecutingBroker.Firm。

我从 Account 开始，然后我想钻到 MANAGER，然后到 MAPPING_MANAGER，然后到 EXECUTINGBROKER。

到目前为止，这是我的查询...

            var student = dbEF.Accounts        
                        .Where(x => x.AccountNumber == acctNum)
                        .Select(x => new DTOCrmDetails()
                        {
                            AccountNumber = x.AccountNumber,
                            AccountName = x.AccountName,
                            DateOpened = x.DateOpened,

                            CommissionId = x.CommissionId,
                            Commission = x.Commission,

                            ManagerID = x.ManagerID,
                            ManagerName = x.Manager.ManagerName,
                            Manager = x.Manager,

                            Employees = x.Manager.Employees,

                            WireInstructionsUSD = x.Manager.WireInstructionsUSDs

                        }).FirstOrDefault();  

下面是从现有数据库的 ef 生成的代码。

    public partial class Manager
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Manager()
    {
        this.Accounts = new HashSet<Account>();
        this.Employees = new HashSet<Employee>();
        this.WireInstructionsUSDs = new HashSet<WireInstructionsUSD>();
        this.Mapping_ManagersExecutingBrokers = new HashSet<Mapping_ManagersExecutingBrokers>();
    }

    public int ManagerID { get; set; }
    public string ManagerName { get; set; }
    public string Strategy { get; set; }
    public string ManagerShortCode { get; set; }
    public Nullable<int> WireInstructionsUsdID { get; set; }
    public Nullable<int> WireInstructionsForeignID { get; set; }
    public string MEtradingPlatform { get; set; }
    public string EtradingCostResp { get; set; }
    public string NotesManager { get; set; }
    public bool MainStrategy { get; set; }
    public string PathPayments { get; set; }
    public string PathEtrading { get; set; }
    public string LEI { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Account> Accounts { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Employee> Employees { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<WireInstructionsUSD> WireInstructionsUSDs { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Mapping_ManagersExecutingBrokers> Mapping_ManagersExecutingBrokers { get; set; }
}

}

{
using System;
using System.Collections.Generic;

public partial class Mapping_ManagersExecutingBrokers
{
    public int Mapping_ManagersExecutingBrokersId { get; set; }
    public Nullable<int> ManagerID { get; set; }
    public Nullable<int> ExecutingBrokersId { get; set; }

    public virtual ExecutingBroker ExecutingBroker { get; set; }
    public virtual Manager Manager { get; set; }
}

}

    public partial class ExecutingBroker
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public ExecutingBroker()
    {
        this.Mapping_ManagersExecutingBrokers = new HashSet<Mapping_ManagersExecutingBrokers>();
    }

    public int ExecutingBrokersId { get; set; }
    public string Firm { get; set; }

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

标签： linqentity-framework-6

Answer 1

您必须通过Mapping_ManagersExecutingBrokers，因为您已经以这种方式对其进行了建模。请记住，您有一组公司，因为它是多对多的关系。

.Select(account => new { Firms = account.Manager.Mapping_ManagersExecutingBrokers
                                .Select(meb => meb.ExecutingBroker.Firm) });