在linq中使用group by选择数据 [英] Select data using group by in linq

问题描述

大家好，



我想使用linq选择group by子句的所有列。在mysql中，查询类似于：

Hi All,

I want to select all columns with group by clause using linq. In mysql, query is like:

select *,count(ContactNumber) as TotalCount from (select SMSBodyText,SentDateTime, ContactNumber from `ars_tmc`.`sms`
where ModuleID=24 and SMSTypeID=1  order by SentDateTime desc) as a group by ContactNumber order by SentDateTime desc;

和输出为：

and output is:

SMSBodyText           SentDateTime       ContactNumber   TotalSMS
Hi Dipali test msg-3  11-10-2014 11:42    919425001480     2
hi                    10-10-2014 07:29    918378803988     1

我的linq是：

my linq is:

var SubQuery = (from sms in UtilService.DtSMSInbox.AsEnumerable()
                            orderby sms.Field<DateTime>("SentDateTime") descending
                            select sms);
            var MainQuery = (from sms1 in SubQuery
                             let SentDateTime = sms1.Field<DateTime>("SentDateTime")
                             let SMSBodyText = sms1.Field<string>("SMSBodyText")
                             group sms1 by (sms1.Field<string>("ContactNumber")) into grouping

                             select new
                             {
                                 ContactNumber = grouping.Key,
                                 count = grouping.Count(),
                                 items=grouping,
                             });

但我无法选择所有列。请帮我解决这个问题。



提前的Thanx

but i m not able to select all columns. Please help me with the solution.

Thanx in Advance

推荐答案

首先，SQL语句看起来有点奇怪。 GROUP BY中的想法是定义用于定义组中唯一数据组合的所有列。然后，您可以再次在SELECT子句中选择这些列，并在其他列上使用聚合。因此，星号（*）在查询中没有意义。



因此要包含所有需要的列，请在两个SQL语句中使用列名而不是星号在LINQ查询中。



请参阅 SQL GROUP BY Statement [ ^ ]和如何：处理查询中的复合键 [ ^ ]

First of all the SQL statement looks a bit odd. The idea in GROUP BY is that you define all the columns that are used to define a unique combination of data in a group. Then again you can select these columns in the SELECT clause and use aggregates on other columns. Because of this an asterisk (*) doesn't make sense in the query.

So to include all desired columns, use column names instead of asterisk in both SQL statement and in the LINQ query.

See SQL GROUP BY Statement[^] and How to: Handle Composite Keys in Queries[^]

这篇关于在linq中使用group by选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！