CODEWITHSUNDEEP
c#sqllinq
Matt Hudson
Matt Hudson

Reputation: 7358

More Efficient LINQ Query

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;
    }

Upvotes: 2

Views: 1468

Answers (2)

Matt Hudson
Matt Hudson

Reputation: 7358

For those that care this was the final code that worked.

 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;

I had to enumerate the list at the bottom to get the Linq compiler to grab the Forms.

Upvotes: 2

Timwi
Timwi

Reputation: 66604

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;
}

Upvotes: 1

Related Questions