LINQ EF-如何通过优化的查询从数据库中收集特定客户的所有帐户，卡，贷款等

Question

我正在尝试从数据库中收集特定客户的所有信息，但是我不确定如何在优化查询中收集所有信息，而不查询数据库几个时代。

我有以下表：https：//i.stack.imgur.com/qhjuj.png

我想要的是将1个客户与提供的客户ID匹配。然后收集与该帐户相关的所有卡，帐户，贷款和权限。

我已经设法做到了，但是对我的_context使用了几个查询。我想学习加入多个表时如何进行优化查询。

与LINQ更有经验的人能否提供一个查询的示例，以收集与客户ID的" 1"？

的客户相关的所有卡，帐户，贷款和允许输入的典范？

我很高兴能获得一些技巧并为此提供帮助，知道如何进行优化的查询是一项非常重要的技能。多谢！： - (

我尝试过的示例：

model.Customer = await _context.Customers.SingleOrDefaultAsync(c => c.CustomerId == request.CustomerId);
        model.Accounts = await (from acc in _context.Accounts
                                join disp in _context.Dispositions on acc.AccountId equals disp.AccountId
                                where disp.CustomerId == request.CustomerId
                                select acc).ToListAsync();
        model.Cards = await (from card in _context.Cards
                             join disp in _context.Dispositions on card.DispositionId equals disp.DispositionId
                             where disp.CustomerId == request.CustomerId
                             select card).ToListAsync();

这是我正在尝试填充数据的视图模型：

 public class GetCustomerDetailsViewmodel
{
    public Customer Customer { get; set; }
    public List<Account> Accounts { get; set; } = new List<Account>();
    public decimal TotalBalance { get; set; }
    public List<Card> Cards { get; set; } = new List<Card>();
    public List<PermenentOrder> PermantentOrders { get; set; } = new List<PermenentOrder>();
    public List<Loan> Loans { get; set; } = new List<Loan>();
}

客户有一个处置列表，客户和卡之间的链接表。

**Customers**
PK CustomerId
public virtual ICollection<Disposition> Dispositions { get; set; }
 **Cards**:
PK public int CardId { get; set; }
FK public int DispositionId { get; set; }
public virtual Disposition Disposition { get; set; }
**Dispositions**:
PK public int DispositionId { get; set; }
FK public int CustomerId { get; set; }
   public int AccountId { get; set; }
public virtual Account Account { get; set; }
public virtual Customer Customer { get; set; }
public virtual ICollection<Card> Cards { get; set; }
**Accounts**:
PK public int AccountId { get; set; }
public virtual ICollection<Disposition> Dispositions { get; set; }
public virtual ICollection<Loan> Loans { get; set; }
public virtual ICollection<PermenentOrder> PermenentOrder { get; set; }
public virtual ICollection<Transaction> Transactions { get; set; }
**Loans**
PK public int LoanId { get; set; }
public virtual Account Account { get; set; }
**PermenentOrder**
PK public int OrderId { get; set; }
FK public int AccountId { get; set; }
public virtual Account Account { get; set; }

Answer 1

我认为您可以使用此信息：

model.Customer = await _context.Customers
    .Include(x => x.Dispositions)
    .ThenInclude(x => x.Cards)
    // and other Entities you need, use Include or if entities are in Accounts
    // or Cards can use ThenInclude
    .SingleOrDefaultAsync(c => c.CustomerId == request.CustomerId);

Answer 2

最简单的查询将是单独查询的，而不是随着语句的增长，linq变得越来越低效率。

。

带有懒惰的加载，

您可以做一些简单的事情，例如

var model = new GetCustomerDetailsViewmodel();
model.Customer = context.Customers.SingleOrDefault(c => c.CustomerId == id);
if (model.Customer != null)
{
     model.Accounts = model.Customer.Dispositions.Select(x => x.Account).ToList();
     model.Cards = model.Customer.Dispositions.SelectMany(x => x.Cards).ToList();
     model.PermantentOrders = model.Accounts.SelectMany(x => x.PermenentOrder).ToList();
}

没有懒惰的加载，

您需要将所有内容加载到单个查询中，当心这可能不是有效的查询。LINQ绝不是关于效率的，而是方便和易于写作的。

var customerProfile = context.Customers.Where(x => x.CustomerId == id).Select(x => new
        {
            Customer = x,
            Accounts = x.Dispositions.Select(d => d.Account),
            Cards = x.Dispositions.SelectMany(d => d.Cards).ToList(),
            PermanentOrders = x.Dispositions.SelectMany(d => d.Account.PermenentOrder),
        }).FirstOrDefault();
        if (customerProfile != null)
        {
            var model = new GetCustomerDetailsViewmodel();
            model.Customer = customerProfile.Customer;
            model.Accounts = customerProfile.Accounts.ToList();
            model.Cards = customerProfile.Cards.ToList();
            model.PermantentOrders = customerProfile.PermanentOrders.ToList();
        }