c# - 更高效的 LINQ 查询

Question

谁能帮我把这个查询循环变成一个高效的 Linq 查询？我正在将其加载到 TreeView 中，因此必须附加每个项目。包含也非常低效。延迟加载项目也不起作用。事实上，这个查询访问数据库的次数比它应该的多很多

    public IQueryable<Client> GetTopLevelData(Guid agentGuid, int year)
    {
        var clients = from client in ObjectContext.Clients
                      join cbc in ObjectContext.Client_Bucket_Client on client.Client_GUID equals cbc.Client_GUID
                      join acb in ObjectContext.Agent_Client_Bucket on cbc.Client_Bucket_GUID equals acb.Client_Bucket_GUID
                      where acb.Agent_GUID == agentGuid
                      select client;

        foreach (Client c in clients)
        {
            var transactions = ObjectContext.Transactions.Where(t => t.Client_GUID == c.Client_GUID && t.Year == year);
            foreach (Transaction t in transactions)
            {
                t.Forms.Attach(ObjectContext.Forms.Where(f => f.Transaction_GUID == t.Transaction_GUID && f.Year == year)); //.OrderByDescending(fo => fo.Create_Date));
            }
            c.Transactions.Attach(transactions);
        }

        return clients;
    }

Answer 1

对于那些关心的人来说，这是最终有效的代码。

 var clients =
    from client in ObjectContext.Clients
    join cbc in ObjectContext.Client_Bucket_Client on client.Client_GUID equals cbc.Client_GUID
    join acb in ObjectContext.Agent_Client_Bucket on cbc.Client_Bucket_GUID equals acb.Client_Bucket_GUID
    where acb.Agent_GUID == agentGuid
    select client;

        var clientInfos =
            from c in clients
            select new
            {
                Client = c,
                TransactionInfos = ObjectContext.Transactions
                    .Where(t => t.Client_GUID == c.Client_GUID && t.Year == year)
                    .Select(t => new
                    {
                        Transaction = t,
                        ToAttach = ObjectContext.Forms.Where(f => f.Transaction_GUID == t.Transaction_GUID && f.Year == year) //.OrderByDescending(fo => fo.Create_Date);
                    })
            };

        // Looping over this query will hit the database *once*
        foreach (var info in clientInfos)
        {
            foreach (var transactionInfo in info.TransactionInfos)
            {
                transactionInfo.Transaction.Forms.Attach(transactionInfo.ToAttach);
            }

            var tt = info.TransactionInfos.ToList(); //.Select(t => t.Transaction);

            var trans = tt.Select(t => t.Transaction);

            info.Client.Transactions.Attach(trans);
        }

        // Return a queryable object; constructing a new query from this will hit the database one more time
        return clients;

我必须枚举底部的列表才能让 Linq 编译器获取表单。