更加高效的LINQ查询

  • Z时代
  • 2024-01-10
  • 分类：问答

有人可以帮助我使此查询循环为有效的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;
    }

回答：

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;
    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);
        info.Client.Transactions.Attach(info.TransactionInfos.Select(t => t.Transaction));
    }
    // Return a queryable object; constructing a new query from this will hit the database one more time
    return clients;
}

以上是 更加高效的LINQ查询 的全部内容， 来源链接： utcz.com/qa/403405.html

回到顶部