LINQ query with SELECT and two GROUP-BY condition

Question

What's the equivalent LINQ instruction for a Datatable of the following SQL query:

SELECT code_direction, count(TP) AS CN 
FROM table1 
WHERE cod_time = 'A011' 
GROUP BY TP,code_direction;

and how to get the result into a new datatable?

I tried to convert it but I there're some errors. Someone could take a look on this:

    var query = from t in table1.AsEnumerable()
                group t by new { t.TP, t.code_direction }
                    into grp
                    select new
                    {
                        grp.Key.code_direction,
                        CN = grp.Count(t.TP)
                    };
    foreach (var x in query)
    {
        Console.Write(x.code_direction);
        Console.Write(x.CN);
    }

I need to cycle the result so I think to move result to a new datatable and interact to it — Alessandro, Dec 12 '11 at 18:19
Why not just go straight through the results, and skip the datatable part? — Jon Hanna, Dec 12 '11 at 18:24
possible duplicate of [Group By Multiple Columns - LINQ](http://stackoverflow.com/questions/847066/group-by-multiple-columns-linq) — Sergey Kalinichenko, Dec 12 '11 at 18:29
What do you mean, "cycle the result"? DataTable is older technology. It seems unlikely that you need to use it. — John Saunders, Dec 12 '11 at 18:31
At StackOverflow, when you say "some errors" there's always someone who goes: what errors? I think it just happened. — Gert Arnold, Dec 12 '11 at 18:54
If you work with DataSets (for whatever reason) then returning a DataTable from your query is a reasonable requirement imho. The only problem with this question is that it is tagged as _linq-to-sql_ when in fact it should be _linq-to-dataset_. — afrischke, Dec 20 '11 at 09:42

afrischke · Accepted Answer · 2011-12-20T09:37:58.620

As far as your first question goes. The LINQ equivalent of the SQL query is:

var query = from t in table1.AsEnumerable()
            where t.cod_time == "A011"
            group t by new { t.TP, t.code_direction }
                into grp
                select new
                {
                    grp.Key.code_direction,
                    CN = grp.Count()
                };

Note that you don't have to pass any argument to grp.Count(). (For the obvious reason that in SQL COUNT(TP) is the same as COUNT(*), i.e. just count the number of rows. The story would be different if you'd use COUNT(DISTINCT TP) or similar.)

As far as the second question goes, if your query just returned an IEnumerable<T> where T is DataRow (i.e. a query like table1.AsEnumerable().Where(r => r.cod_time == "A011")) then you could just the DataTableExtensions.CopyToDataTable extension method. As your query returns an anonymous type however, you will have to follow these instructions found on MSDN.

RichardScannell · Answer 2 · 2012-05-29T15:14:55.557

I Have been using LINQ to work on a JSON object returned from a remote sharepoint web service. I have posted this because most of the answers I found online were slightly different from what I needed.

a json list of daily activities is returned from a remote sharepoint list & is then summarised using LINQ

The simplified version of a custom object definition is shown below( & which is defined in the models area of an MVC application)

public class MyCustomObjectList
{
 public string eventdate { get; set; }
 public string userid { get; set; }
 public string action { get; set; }
}

The JSON object is serialised into a MyCustomObjectList array.

var customobject = serializer.Deserialize<MyCustomObjectList>(jsonobject);

I wanted to work out how many actions of each type happened on a given day. NB eventdate is stored as a string in format yyyy-mm-dd hh:MM:ss. This was to simplify conversions between c#, JSON & Jquery ( where required I create DateTime objects elsewhere in the code using the eventdate.

Some will argue this is inefficient, but I prefer to split processes into a sequential set of really simple operations, for the sake of easier debugging & to help other people follow my code. Thats why there are 2 Linq queries .

querya strips out the time component from the eventdate This ensures our later grouping happens by day, & not by second. To be doubly sure that there is no caching, I create it in a new field called actionday. I also rename action to activity, because intellisense was getting confused!! The other columns are copied as is.

var querya =
from c in customobject.rows
select new {  actionday =  c.eventdate.Substring(0, 10), activity =  c.action, c.userid,
c.eventdate };

/* queryb produces a grouped count of querya, grouped on actionday & activity, creating new columns actionkey,ActionCount,Dte,action & DetailList ( which is a summary for debugging purposes) */

var queryb= 
from p in querya group p by new { p.actionday, p.activity} into idGroup
 actionkey = idGroup.Key,
  ActionCount = idGroup.Count(),
  Dte = idGroup.Key.actionday,
  action = idGroup.Key.activity,
  DetailList = idGroup
};

Here’s a version that sumarises by 3 columns

 var queryc = from p in querya
 group p by new { p.actionday, p.userid, p.activity} into idGroup
        select new
         {
            actionday = idGroup.Key,
            ActionCount = idGroup.Count(),
            userid = idGroup.Key.userid,
            Dte = idGroup.Key.actionday,
            action = idGroup.Key.activity,
            DetailList = idGroup
};

LINQ query with SELECT and two GROUP-BY condition

2 Answers2