更高效的LINQ查询 [英] More Efficient LINQ Query

问题描述

有人可以帮我做这个查询循环成一个高效的Linq查询？我装入所以每个项目必须附加一个TreeView这一点。包括也非常低效的。延迟加载的项目将不能工作。正因为如此，这个查询访问数据库的许多更多的时间比它应该

 公开的IQueryable<客户> GetTopLevelData（GUID agentGuid，年整型）
 {从客户
 VAR的客户=在ObjectContext.Clients 
加入CBC在ObjectContext.Client_Bucket_Client上client.Client_GUID等于cbc.Client_GUID 
加盟ACB在ObjectContext.Agent_Client_Bucket上cbc.Client_Bucket_GUID等于acb.Client_Bucket_GUID 
，其中acb.Agent_GUID == agentGuid 
选择客户端; 
 
的foreach（在客户端C）
 {
变种交易= ObjectContext.Transactions.Where（T => t.Client_GUID == c.Client_GUID&放大器;& T公司.Year ==年）; 
的foreach（事务T的交易）
 {
 t.Forms.Attach（ObjectContext.Forms.Where（F => f.Transaction_GUID == t.Transaction_GUID&放大器;&安培; F。今年==年））; //.OrderByDescending(fo = GT; fo.Create_Date））; 
} 
 c.Transactions.Attach（交易）; 
} 
 
回报客户; 
} 
  

解决方案

 公开的IQueryable<客户> GetTopLevelData（GUID agentGuid，年整型）
 {
 VAR的客户=从客户
在ObjectContext.Clients 
加入CBC在ObjectContext.Client_Bucket_Client上client.Client_GUID等于cbc.Client_GUID 
加入ACB在ObjectContext.Agent_Client_Bucket上cbc.Client_Bucket_GUID等于acb.Client_Bucket_GUID 
，其中acb.Agent_GUID == agentGuid 
选择客户端;从C在客户
 
变种clientInfos = 
 
选择新的
 {
客户端= C，
 TransactionInfos = ObjectContext.Transactions 
。凡（T => t.Client_GUID == c.Client_GUID&放大器;&安培; t.Year ==年）
。选择（T =>新建
 {
 =交易T，
 ToAttach = ObjectContext.Forms.Where（F => f.Transaction_GUID == t.Transaction_GUID&放大器;&安培; f.Year ==年）//.OrderByDescending(fo => fo.Create_Date） 
}）
}; 
 
 //循环执行这个查询会打到数据库* *一次
的foreach（在clientInfos VAR资讯）
 {
的foreach（在info.TransactionInfos VAR transactionInfo） 
 transactionInfo.Transaction.Forms.Attach（transactionInfo.ToAttach）; 
 
 info.Client.Transactions.Attach（info.TransactionInfos.Select（T => t.Transaction））; 
} 
 
 //返回可查询的对象;从这个构造一个新的查询将访问数据库一次
回报客户; 
} 
  

Can someone help me make this query loop into an efficient Linq query? I am loading this into a TreeView so each item must be attached. Includes are also very inefficient. Lazy loading the items will not work either. As it is, this query hits the database many more times than it should

    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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！