Click here to Skip to main content
11,414,234 members (71,442 online)

Greysontyrus asked:

Open original thread
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#, .NET4, LINQ

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. 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.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).



Advertise | Privacy | Mobile
Web03 | 2.8.150427.2 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100