1

I am writing a join query in LINQ with multiple tables. In the query in one portion I have implemented outer join. The query working fine when there is data in both agentdeployment and agentlookup table but it is giving error when there is no data in agentlookup table. My query is like this:

this.db.Workflows.Where(e => e.WorkflowId == processFlowId).Join(this.db.WorkflowNodes,
                   wf => wf.WorkflowId,
                   node => node.WorkflowId,
                   (wf, node) => new
                   {
                       Workflow = wf,
                       WorkfloNode = node
                   }
               ).Join(this.db.NodeConfigurations,
                   wNode => wNode.WorkfloNode.WorkflowNodeId,
                   config => config.NodeId,
                   (p, v) => new
                   {
                       Workflow = p.Workflow,
                       WorkfloNode = p.WorkfloNode,
                       NodeConfiguration = v
                   }).Join(this.db.AgentDeployments,
                   wfData => wfData.Workflow.WorkflowId,
                   deplData => deplData.WorkflowId,
                   (workflowData, depl) => new
                   {
                       Workflow = workflowData.Workflow,
                       WorkfloNode = workflowData.WorkfloNode,
                       NodeConfiguration = workflowData.NodeConfiguration,
                       DeployedItems = depl
                   }).GroupJoin(this.db.AgentLookupMappings, wData => wData.DeployedItems.AgentDeploymentId, lk => lk.AgentDeploymentId,
                         (e1, e2) => new
                         {
                             Workflow = e1.Workflow,
                             WorkfloNode = e1.WorkfloNode,
                             NodeConfiguration = e1.NodeConfiguration,
                             DeployedItems = e1.DeployedItems,
                             LookUp = e2
                         })
               .GroupBy(e => new { e.Workflow.WorkflowId, e.Workflow.WorkflowName },
                    (key, g) => new
                    {
                        WorkflowId = key.WorkflowId,
                        WorkflowName = key.WorkflowName,
                        Groups = g.ToList()
                    }).AsEnumerable().Select(e => new WorkflowData
                    {
                        WorkflowId = e.WorkflowId,
                        WorkflowName = e.WorkflowName,
                        DeploymentDetails = e.Groups.DistinctBy(d => d.DeployedItems.AgentDeploymentId)
                        .Select(t => new WorkflowDeploymentDetails 
                        { 
                            DeployedAt = t.DeployedItems.DeployedAt, 
                            DeployedVersionNumber = t.DeployedItems.DeployedVersionNumber, 
                            AgentDeploymentId = t.DeployedItems.AgentDeploymentId,
                            AgentLookupMappings=e.Groups.SelectMany(p1 => p1.LookUp.DefaultIfEmpty(), (x, y) => new LookupMapping {
                                CategoryName = y.CategoryName,
                                LookupTypeName = y.LookupTypeName,
                                AgentLookupMappingId=y.AgentLookupMappingId
                            }).DistinctBy(d2=>d2.AgentLookupMappingId).ToList()
                        }).ToList(),
                        Nodes = e.Groups.DistinctBy(kk => kk.WorkfloNode.WorkflowNodeId).Select(k => new WorkflowNodeItem
                        {
                            WorkflowNodeId = k.WorkfloNode.WorkflowNodeId,
                            NodeType = k.WorkfloNode.NodeType,
                            NodeText = k.WorkfloNode.NodeText,
                            NodeConfigList = e.Groups.Where(ee => ee.NodeConfiguration.NodeId == k.WorkfloNode.WorkflowNodeId).Select(f => new NodeConfigData { ConfigKey = f.NodeConfiguration.ConfigKey, ConfigVal = f.NodeConfiguration.ConfigVal }).ToList()
                        })
                        .ToList()
                    }).ToList();

I am getting null exception error in this section

e.Groups.SelectMany(p1 => p1.LookUp.DefaultIfEmpty(), (x, y) => new LookupMapping {
                                    CategoryName = y.CategoryName,
                                    LookupTypeName = y.LookupTypeName,
                                    AgentLookupMappingId=y.AgentLookupMappingId
                                })

How to bypass the error

Thanks Utpal Maity

U.Savas
  • 129
  • 11
Utpal
  • 805
  • 4
  • 15
  • 44
  • Consider using a stored proc instead of that LINQ code in listing 1. Apart from being hard to read any ORM code that complex should have the DB do the hard work rather than the code. Stored proc's can still be called and mapped by EF whilst letting the DB do what it does best. Plus its way too easy to create performance issues if you aren't 100% sure how the ORM works. –  Jul 05 '22 at 12:13
  • Have you tried to use Query syntax? Query will be much readable. – Svyatoslav Danyliv Jul 05 '22 at 12:13
  • Apart from that, only you can tell what's null in your code. We can't debug it. That's why questions like this are often closed as a duplicate of [this one](https://stackoverflow.com/q/4660142/861716). – Gert Arnold Jul 05 '22 at 14:00
  • @GertArnold in my question I am clearly written where I am getting exception which I found after debugging. – Utpal Jul 06 '22 at 04:22
  • Sure, but we can't see which object is null in your code. Only you can. – Gert Arnold Jul 06 '22 at 06:32
  • When you say "agentlookup" do you mean to say `db.AgentLookupMappings`? – NetMage Jul 06 '22 at 19:53
  • 2
    Try changing `AgentLookupMappingId=y.AgentLookupMappingId` to `AgentLookupMappingId=y?.AgentLookupMappingId` – NetMage Jul 06 '22 at 19:54
  • @NetMage This is also what Microsoft suggest using in the related documentation page: https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins. Interestingly enough, I had a similar code which is written in Query Syntax in .net 3.1. After I migrated the codebase to net6.0 I got the following error: "Value cannot be null. Parameter name: source" wherever I used the DefaultIfEmpty method. PS: I disabled nullable in all project files. – U.Savas Jan 22 '23 at 09:47

0 Answers0