How to handle the problem of the Linq Mysql GroupBy statement, linqgroupby

How to handle the problem of the Linq Mysql GroupBy statement, linqgroupby

The statement is as follows:

                var resumeList = db.ChannelResume.Where(model);                var groupValues = resumeList.GroupBy(t => new {t.AgentId, t.AgentName});                var statistics = groupValues.Select(c => new ResumeStatisticsViewModel()                {                    Date = dateSpan,                    AgentId = c.Key.AgentId,                    AgentName = c.Key.AgentName,                    TotalCount = c.Count(),                    ValidCount = c.Count(k => k.Status == (int)ResumeStatus.Valid)                });                try                {                    //statistics = statistics.OrderBy(c => c.AgentName);                    var tmp = statistics.ToList();                                    }                catch (Exception ex)                {                    Console.WriteLine(ex.Message);                    throw;                }

As shown above, an error is returned when the Select statement is directly used for the GroupBy object. In the SQL statement parsed by linq, the group conditions AgentId and AgentName are not used as fields in the temporary table, as a result, an error is reported during the execution of the entire SQL statement: "Unknown column 'groupby1. k1 'in 'field list '"

I found two addresses online:

Http://bugs.mysql.com/bug.php? Id = 46742

Http://stackoverflow.com/questions/15083627/linq-mysql-group-by-year-month-day-select-year-month-day-count

However, no solution is available.

Later, I tried to perform a foreach loop directly on the groupValues variable.

                var resumeList = db.ChannelResume.Where(model);                var groupValues = resumeList.GroupBy(t => new {t.AgentId, t.AgentName});                foreach (var item in groupValues)                {                    var viewModel = new ResumeStatisticsViewModel()                    {                        Date = dateSpan,                         AgentId = item.Key.AgentId,                         AgentName = item.Key.AgentName,                        TotalCount=item.Count(),                        ValidCount = item.Count(t => t.Status == (int)ResumeStatus.Valid)                    };                    result.Add(viewModel);                }                return new PagedList<ResumeStatisticsViewModel>(result, m.Page, m.PageSize);

In this way, no error is reported.

It seems that there is a bug in the combination of the Linq Mysql GroupBy statement and Select statements.

Later, the linq statement does have performance problems. If it is not too cool to split and write code, it should be really split to keep the database operations in the Code as short as possible.

 

How to operate mysql by group statements in java

First, you need to load the driver package of the corresponding database in the project, and then query the database. The Code will be provided to you later.
String SQL = "";
Class. forName ("com. mysql. jdbc. Driver ");
Connection con = DriverManager. getConnection ("jdbc. mysql // localhost: 3306/<Your Database Name>", "username", "pwd ");
PreparedStatement pstmt = con. prepareStatement (SQL );
ResultSet rs1_pstmt.exe cuteQuery ();
While (rs. next ()){
System. out. println (rs. getInt (1) + "" + rs. getInt (2 ));
}

Mysql group

It can be used for union, but fields other than group by that used separately must use the aggregate function if they appear in select. Otherwise, they are invalid.