Subquery in select Linq

Question

My query is as below

var result = from cr in cumulativeresult
    orderby cr.Days
    select new
    {
        Days = cr.Days,
        Com = (from cr1 in cumulativeresult
            where cr1.Days < cr.Days
            group cr1 by 1 into cr1grp
            select
            new
            {
                Count = cr1grp.Count() + cr.Com 
            }),
        ComPercent = Math.Round((double.Parse((
            from cr1 in cumulativeresult
            where cr1.Days < cr.Days
            group cr1 by 1 into cr1grp 
            select cr1grp.Count() + cr.Com).ToString()) / ComCount) * 100, 2)
    };

But the Com and ComPercent are not coming as values. How will I be able to retrieve the value within them? My loop for retrieving them is as follows

Update

foreach (var item in result)
{
    dt.Rows.Add(item.Days, item.Com, item.ComPercent);
}

I do not want to do any processing on my foreach loop, I want the value in my linq query itself.

what do u mean by "not coming as number"?u are using toString() — user786, May 07 '16 at 07:12
"I do not want to do any processing on my foreach loop," what processing? u need to iterate over your result — user786, May 07 '16 at 07:25
@Hitesh as posted, `Com` may contains multiple 'counts', how would you expect them to be displayed in a column?? — har07, May 07 '16 at 07:48
no it wont, I have written group by 1 and the output is Count(), so it would give only 1 result. However, I am ok if Take(1) is done on the subquery result — Hitesh, May 07 '16 at 07:54
Ah, I didn't notice the *group by 1* part. See my answer below for a possible solution. Side note: Please use *@har07* if you want me to get notified about your comment — har07, May 09 '16 at 05:52

har07 · Accepted Answer · 2016-05-09T05:50:27.833

Compiler doesn't know your subquery will always return one row, since the return type is still IEnumerable (or IQueryable). This can be solved by using First() or Single() (or the corresponding ..OrDefault() version), however, it is more common to just use Count() without grouping, for example :

Com = (from cr1 in cumulativeresult
       where cr1.Days < cr.Days
       select cr1
      ).Count() + cr.Com,

You might also want to consider moving the subquery into a let clause, so it can be reused to calculate Com and ComPercent :

var result = from cr in cumulativeresult
             let totalCount = 
                      (from cr1 in cumulativeresult
                       where cr1.Days < cr.Days
                       select cr1
                      ).Count() + cr.Com 
             orderby cr.Days
             select new
             {
                Days = cr.Days,
                Com = totalCount,
                ComPercent = Math.Round((double)totalCount / ComCount * 100, 2)
             };

Calling .Count() is so much easier than using a "group" clause and then calling ".Single()". The T-SQL statement that is generated is also simpler. Thanks for the tip! — humbads, Jan 03 '18 at 21:09

score 1 · Answer 2 · answered May 07 '16 at 12:45

To answer your question, you need to select the .First of what you, but not Linq, knows is a single grouping of 1.

I've adjusted your query in a couple of other ways as well:

To get some output I had to change the < to <=.
I used a cast instead of reparsing as double.

This is the whole LinqPad query:

var cumulativeresult = new[] { new { Days = 0, Com = 0 }, new { Days = 1, Com = 1 } };
var ComCount = cumulativeresult.Count();
var result = from cr in cumulativeresult
             orderby cr.Days
             select new
             {
                 Days = cr.Days,
                 Com = (from cr1 in cumulativeresult
                        where cr1.Days <= cr.Days
                        group cr1 by 1 into cr1grp
                        select
                          new
                          {
                              Count = cr1grp.Count() + cr.Com
                          }
                           ).First().Count,
                 ComPercent = Math.Round((//double.Parse(
                           ((double)(
                           from cr1 in cumulativeresult
                           where cr1.Days <= cr.Days
                           group cr1 by 1 into cr1grp
                           select cr1grp.Count() + cr.Com).First())//.ToString())
                           / ComCount) * 100, 2
                           )
             };

var dt = new DataTable();
dt.Columns.AddRange(new[] {new DataColumn("Days"),new DataColumn("Com"),new DataColumn("ComPercent")  });
foreach (var item in result)
{
    dt.Rows.Add(item.Days, item.Com, item.ComPercent);
}
dt.Dump();

Subquery in select Linq

2 Answers2