Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# LINQ .NET4
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 ^_^
Posted 15-Nov-12 0:37am
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
Greysontyrus at 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 at 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 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I have a solution but it does return much more from the db than I would like:
 
            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?
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 390
1 Sergey Alexandrovich Kryukov 329
2 BillWoodruff 210
3 Afzaal Ahmad Zeeshan 204
4 CPallini 185
0 OriginalGriff 5,515
1 DamithSL 4,451
2 Maciej Los 3,902
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2014
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