linq to sql中group by子句的问题 [英] Issue with group by clause in linq to sql
本文介绍了linq to sql中group by子句的问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在linq中对sql进行此查询。请帮忙。我是linq的新手,并且对group by子句有疑问。 PFA图像显示来自sql server的查询这是sql查询
select count(USERID),d.DEPTNAME 来自 USERS u
加入部门d on u.DEPTID = d.DEPTID
group by u.DEPTID,d.DEPTNAME
解决方案
试试这个:
1)
var qry1 = < span class =code-sdkkeyword> from d 部门
选择 new {
deptname = d.deptname,
ucount =( from u < span class =code-keyword> 用户其中 u.deptid == d.deptid 选择 u).Count()
};< br $> b $ b
或
2)
< span class =code-keyword> var qry2 = 来自 u 用户 group u by u.deptid into grp
select new {Deptid = grp.Key,UsersCount = grp.Count()};
< br $> b $ b或
3)
var qry3 = 来自 u usrs
join d 在 depts on u.DeptId等于d.DeptId
group d by d.DeptName into grp
选择 new {
DeptName = grp.Key,
UsersCount = grp.Count()
};
如需了解更多信息,请参阅:101 LINQ样本 [ ^ ]
一个完整的 LinqPad [ ^ ]示例:
void Main()
{
List< Department> depts = new 列表< Department>
{
new 部门( 1 , A),
new 部门( 2 , B),
< span class =code-keyword> new 部门( 3 , C)
};
列表<用户> usrs = new 列表<用户>
{
new 用户( 1 ),新用户( 1 ),
新用户( 2 ), new 用户( 2 ),
new 用户( 2 ), new 用户( 1 ),
new 用户( 1 ), new 用户( 3 ),
new 用户( 3 ), new 用户( 3 ),
new 用户( 3 ), new 用户( 3 ),
new 用户( 3 ),新用户( 3 ),
new 用户( 3 ), new 用户( 3 ),
new 用户( 3 )
};
var qry1 = 来自 d 在中设置
选择 new {
DeptName = d.DeptName,
UsersCount =( from u in usrs where u.DeptId == d.DeptId select u).Count()
};
qry1.Dump();
var qry2 = 来自 u in usrs group u by u.DeptId into grp
选择 new {DeptId = grp.Key,UsersCount = grp.Count()};
qry2.Dump();
var qry3 = 来自 u in usrs
join d 在 depts on u.DeptId等于d。 DeptId
group d由d.DeptName 改为 grp
选择 new {
DeptName = grp.Key,
UsersCount = grp.Count()
};
qry3.Dump();
}
// 在此处定义其他方法和类
class 部门
{
私人 int did = 0 ;
private string dna = string .Empty;
public 部门( int _did, string _dna)
{
did = _did;
dna = _dna;
}
public int DeptId
{
get { return did;}
set {did = value ;}
}
public string DeptName
{
get { return dna;}
set {dna = value ;}
}
}
class 用户
{
private int did = 0 ;
public 用户( int _did)
{
做= _did;
}
public int DeptId
{
get { return did;}
set {did = value ;}
}
}
结果qry1
DeptName UsersCount
A 4
B 3
C 10
和qry2
< pre lang =text> DeptId UsersCount
1 4
2 3
3 10
和qry3
DeptName UsersCount
A 4
B 3
C 10
I want to make this query in linq to sql. Please help. I am new to linq and having problem to with the group by clause. PFA the image is showing the query from the sql server Here is the sql query
select count(USERID), d.DEPTNAME from USERS u
join department d on u.DEPTID = d.DEPTID
group by u.DEPTID, d.DEPTNAME
解决方案
Try this:
1)
var qry1 = from d in department select new { deptname = d.deptname, ucount = (from u in users where u.deptid == d.deptid select u).Count() };
or
2)
var qry2 = from u in users group u by u.deptid into grp select new { Deptid = grp.Key, UsersCount = grp.Count() };
or
3)
var qry3 = from u in usrs join d in depts on u.DeptId equals d.DeptId group d by d.DeptName into grp select new { DeptName = grp.Key, UsersCount = grp.Count() };
For further information, please seee: 101 LINQ Samples[^]
[EDIT]
A complete LinqPad[^] example:
void Main() { List<Department> depts = new List<Department> { new Department(1, "A"), new Department(2, "B"), new Department(3, "C") }; List<User> usrs = new List<User> { new User(1), new User(1), new User(2), new User(2), new User(2), new User(1), new User(1), new User(3), new User(3), new User(3), new User(3), new User(3), new User(3), new User(3), new User(3), new User(3), new User(3) }; var qry1 = from d in depts select new { DeptName = d.DeptName, UsersCount = (from u in usrs where u.DeptId == d.DeptId select u).Count() }; qry1.Dump(); var qry2 = from u in usrs group u by u.DeptId into grp select new { DeptId = grp.Key, UsersCount = grp.Count()}; qry2.Dump(); var qry3 = from u in usrs join d in depts on u.DeptId equals d.DeptId group d by d.DeptName into grp select new { DeptName = grp.Key, UsersCount = grp.Count() }; qry3.Dump(); } // Define other methods and classes here class Department { private int did =0; private string dna = string.Empty; public Department(int _did, string _dna) { did = _did; dna = _dna; } public int DeptId { get{return did;} set{did = value;} } public string DeptName { get{return dna;} set{dna = value;} } } class User { private int did = 0; public User(int _did) { did = _did; } public int DeptId { get{return did;} set{did = value;} } }
Result ofqry1
DeptName UsersCount A 4 B 3 C 10
andqry2
DeptId UsersCount 1 4 2 3 3 10
andqry3
DeptName UsersCount A 4 B 3 C 10
这篇关于linq to sql中group by子句的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文