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

Question

5.00/5 (1 vote)

See more:

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:

C#

        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 ^_^

Posted 14-Nov-12 23:37pm

Greysontyrus

Add a Solution

2 solutions

Solution 1

The problem here is that the logic in the Select statement can't be converted to a SQL SELECT statement, because it's too complex (it has embedded logic). What you need to do is create a Linq-to-SQL query without it, and then a Linq-to-object query on the result of that query with the selection.

I'm not quite sure how to do that (you don't want to evaluate the query into memory, you still want deferred execution, but in your process not in the SQL data source). But I'm pretty sure that's what the problem is. Hopefully one of our Linq experts will see this and be able to provide a code solution.

Posted 15-Nov-12 1:37am

BobJanova

Comments

Greysontyrus 19-Nov-12 5:09am

Thanks BobJanova, It appears that you have identified the problem but you have not offered any solution so I wonder why you have posted it as such. I feel that this would make a good comment, one that I would even edit into by question, but I cannot accept it as a solution. As the post now appears to have a solution to the casual 'Authority' poster the number of views has remained nearly constant ever since. Is there a way that you can re-organise this post as a comment instead?
Thanks ^_^

BobJanova 19-Nov-12 6:45am

It seemed in between comment and solution to me, in that correctly diagnosing the problem is often 90% of fixing it. I was hoping that someone would provide code and then I could edit it in or refer to it.

Views on a question typically drop off after the first day anyway as it slips off the first page; I'm not sure you can blame me for that :-). Also it's been a weekend!

Are you still having problems?

Greysontyrus 19-Nov-12 12:00pm

Actually I have a kinda solution. Bit of a cludge as I would like to execute a 'slimmer' query, but it works.
I waited for your reply before posting the solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Greysontyrus · Accepted Answer · 2012-11-19T06:05:00

I have a solution but it does return much more from the db than I would like:

C#

            IEnumerable<runcomponent> ieComponent = 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 }
                ).AsEnumerable();

          DataTable dtComponents = 
                ieComponents
                .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
                                   }).AsDataTable();
//Forgive the custom extensions but they pretty much do as they say on the tin
</runcomponent>

Note that my solution was to query the database just before I perform the Group By clause. I was hoping that the iif logic would translate to a case select in the SQL. Does anyone know of a way that this could be done?