Linq GroupBy gives Could not format node 'New' for execution as SQL exception

Hi,

Unfortunately, I am the only person in the company who hand much experience with Linq and so I have no-where else to turn when I get in a jam :S

I have a query where one of the columns must show a single row from a joined table when only one row exists, but must show a different value when there are multiple joined rows.

I originally had this column in a joined table so the query is separate to the main query but I will eventually need to integrate the two together.

Anyway - On with the code at hand:

        private class WaitingReport
        {
            public int RunId { get; set; }

            public int PackageId { get; set; }
            public string DecoDescription { get; set; }
            public string WaitingOnText { get; set; }
            public string WaitingOnLink { get; set; }

        }
        private class RunComponent
        {
            public int RunId { get; set; }
            public string ComponentName  { get; set; }
        }

        private void PopulateWaitingTable()
        {
            WaitingCarmaTable.Reset();


            CarmaContext db = new CarmaContext();

            IQueryable<WaitingReport> iqWaitingReport =
                RunProperty.QueryByNameAndNotValue(PropertyFacade.RUN_USER_INPUT_URL, "empty", db)
                    .Join(
                        db.Runs,
                        rp => rp.run_id,
                        r => r.run_id,
                        (rp, r) => new {r.run_id, rp.property_value, r.deco_id, r.package_id, r.action_id}
                    )
                    .Join(
                        db.Actions,
                        n => n.action_id,
                        a => a.action_id,
                        (n, a) => new WaitingReport
                                      {
                                          RunId = n.run_id,
                                          PackageId = n.package_id.GetValueOrDefault(0),
                                          DecoDescription = Deco.GetDecoDescription(n.deco_id,db),
                                          WaitingOnText = a.action_name,
                                          WaitingOnLink = n.property_value
                                      }
                    );

            IQueryable<RunComponent> iqComponent = iqWaitingReport
                .Join(
                    db.Runs.Where(r => _showAll || r.contact.ToLower().Equals(WebInterfaceUtils.GetUserName().ToLower())),
                    rp => rp.RunId,
                    r => r.run_id,
                    (rp, r) => r)
                .Join(
                    db.Deliveries,
                    r => r.package_id,
                    d => d.package_id,
                    (r, d) => new {r.run_id, d.component_id}
                )
                .Join(
                    db.Components,
                    n => n.component_id,
                    c => c.component_id,
                    (n, c) => new RunComponent { RunId = n.run_id, ComponentName = c.name }
                )
                .GroupBy(rc => rc.RunId)
                .Select(rcg => new RunComponent
                                   {
                                       RunId = rcg.Key, 
                                       ComponentName = rcg.Count() > 1 
                                       ? Segment.QueryByRunId(rcg.Key,db).segment_name
                                       : rcg.Max().ComponentName
                                   });
//...

I have an extension method to convert these into DataTables but any kind of query activation triggers the error Could not format node 'New' for execution as SQL

I have found that the final .Select() method is what throws the error but I cannot see why.

Any ideas?

I'll closely monitor this thread and I am willing to make any changes to narrow down the reason for the error

TIA ^_^

Tags: C#, .NET (.NET4), LINQ