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