Linq-to-Entities Query from SQL

I have an EDMX with an Agent, Client, Transaction and Form. Agent's have Clients which have Transactions and Transactions have forms.

I want to use projection to get all of the entities for an agent at once to put in a hierarchichal treeview. The treeview needs to only show Transactions and Forms by the year selected.

My query in SQL would look something like this:

SELECT c.*, t.*, f.* FROM Client c
INNER JOIN Client_Bucket_Client cbc on cbc.Client_GUID = c.Client_GUID
INNER JOIN Agent_Client_Bucket acb on acb.Client_Bucket_GUID = cbc.Client_Bucket_GUID
LEFT OUTER JOIN [Transaction] t on t.Client_GUID = c.Client_GUID
LEFT OUTER JOIN [Form] f on f.Transaction开发者_如何学运维_GUID = t.Transaction_GUID
WHERE f.Year = 2011 AND t.Year = 2011 AND acb.Agent_GUID = 'A29B6E94-3F1B-E011-B68A-001F290A2D4A'
ORDER BY c.Last_Change_Date desc, c.File_Under

Right now my linq looks like this:

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
                  orderby client.Last_Change_Date descending, client.File_Under
                  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)
                })
        };

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

But I get an error :

Load operation failed for query 'GetTopLevelData'. An error occurred while executing the command definition. See the inner exception for details. Inner exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Here is the Stack Trace:

at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at RealForms.Web.Data.RealFormsService.GetTopLevelData(Guid agentGuid, Int32 year) in C:\Users\Bryan\Documents\Visual Studio 2010\Projects\RealForms\RealForms.Web.Data\RealFormsService.cs:line 590 at GetTopLevelData(DomainService , Object[] ) at System.ServiceModel.DomainServices.Server.ReflectionDomainServiceDescriptionProvider.ReflectionDomainOperationEntry.Invoke(DomainService domainService, Object[] parameters) at System.ServiceModel.DomainServices.Server.DomainOperationEntry.Invoke(DomainService domainService, Object[] parameters, Int32& totalCount) at System.ServiceModel.DomainServices.Server.DomainService.Query(QueryDescription queryDescription, IEnumerable1& validationErrors, Int32& totalCount) at System.ServiceModel.DomainServices.Hosting.QueryProcessor.Process[TEntity](DomainService domainService, DomainOperationEntry queryOperation, Object[] parameters, ServiceQuery serviceQuery, IEnumerable1& validationErrors, Int32& totalCount) at System.ServiceModel.DomainServices.Hosting.QueryOperationBehavior1.QueryOperationInvoker.InvokeCore(Object instance, Object[] inputs, Object[]& outputs)

I'm finding that this error only occurs on certain data. It seems something is null somehwere??

If you want to achieve JOIN in SQL why don't you use JOIN in LINQ?

http://msdn.microsoft.com/en-us/library/bb534675.aspx

This is how you might join clients with transactions:

var clientTransactions = clients
       .Join(ObjectContext.Transactions, c => c.Client_GUID, t => t.Client_GUID, 
                    (c,t) => new {Client = c, Transaction = t});

It's hard to predict which SQL query produces your linq so I would prefer using this syntax for JOIN.

P.S.: You're getting a timeout - it's definitely depends on data, especially on data volume. Try profiling which queries your LINQ sends to SQL server.

I have solved my problem with the following code. I'm so new to Linq-to-Entities I'm not sure if this is the best or preferred way of doing it, but it worked for me.

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
                  orderby client.Last_Change_Date descending, client.File_Under
                  select client;

    var clientTransactions = clients
                .Join(ObjectContext.Transactions, c => c.Client_GUID, t => t.Client_GUID,
                            (c, t) => new { Transaction = t });

    var clientForms = clientTransactions
                .Join(ObjectContext.Forms, t => t.Transaction.Transaction_GUID, f => f.Transaction_GUID,
                            (t, f) => new { Form = f });

    foreach (var client in clients)
    {
        var clientTrans = from trans in clientTransactions where trans.Transaction.Client_GUID == client.Client_GUID && trans.Transaction.Year == year select trans;
        foreach (var trans in clientTrans)
        {
            client.Transactions.Attach(trans.Transaction);

            var transForms = from forms in clientForms where forms.Form.Transaction_GUID == trans.Transaction.Transaction_GUID && forms.Form.Year == year select forms;
            foreach (var form in transForms)
            {
                trans.Transaction.Forms.Attach(form.Form);                                                             
            }
        }
    }

    return clients;
}

I researched how to nest joins in Linq but was unable to get anything to work, so here I pull the child transaction and form records then load the projected clients object with the results. I'm no longer getting timeouts or (Nullable1 forMergeOption) errors.

Thanks everyone for your help!