Linq to Sql:一些语句的写法
1:Sum,Group by的写法:
Code
var q = from p in db.ReimbursementSub
where (p.Reimbursement.State ==1)
group p by new { p.Employee.CNName ,p.Project.ProjectName } into g
select new { CNName = g.Key.CNName, ProjectName = g.Key.ProjectName, TotalMoney= g.Sum(p => p.Money) };
return q.AsQueryable();
生的的sql语句:
SELECT SUM([t0].[Money]) AS [TotalMoney], [t2].[CNName], [t3].[ProjectName]
FROM [dbo].[ReimbursementSub] AS [t0]
LEFT OUTER JOIN [dbo].[Reimbursement] AS [t1] ON [t1].[ReimbursementGUID] = [t0].[ReimbursementGUID]
INNER JOIN [dbo].[Employee] AS [t2] ON [t2].[EmployeeID] = [t0].[EmployeeID]
INNER JOIN [dbo].[Project] AS [t3] ON [t3].[ProjectID] = [t0].[ProjectID]
WHERE ([t0].[ReimbursementGUID] IS NULL) OR ([t1].[State] = 1)
GROUP BY [t2].[CNName], [t3].[ProjectName]
var q = from p in db.ReimbursementSub
where (p.Reimbursement.State ==1)
group p by new { p.Employee.CNName ,p.Project.ProjectName } into g
select new { CNName = g.Key.CNName, ProjectName = g.Key.ProjectName, TotalMoney= g.Sum(p => p.Money) };
return q.AsQueryable();
生的的sql语句:
SELECT SUM([t0].[Money]) AS [TotalMoney], [t2].[CNName], [t3].[ProjectName]
FROM [dbo].[ReimbursementSub] AS [t0]
LEFT OUTER JOIN [dbo].[Reimbursement] AS [t1] ON [t1].[ReimbursementGUID] = [t0].[ReimbursementGUID]
INNER JOIN [dbo].[Employee] AS [t2] ON [t2].[EmployeeID] = [t0].[EmployeeID]
INNER JOIN [dbo].[Project] AS [t3] ON [t3].[ProjectID] = [t0].[ProjectID]
WHERE ([t0].[ReimbursementGUID] IS NULL) OR ([t1].[State] = 1)
GROUP BY [t2].[CNName], [t3].[ProjectName]
2:SQL里面in的写法:
Code
string ProjectIDs = db.EmpHaveProj.SingleOrDefault(e => e.EmployeeID == EmployeeID.ToInt()).ProjectIDs;
--ProjectIDs=",1,4,7,6,16,21,22,31,32," 相当于In的范围
string[] lst = ProjectIDs.Split(',');
var proj = from o in db.Project
where (lst.Contains(o.ProjectID.ToString()))
select new { o.ProjectID, o.ProjectName };
return proj;
生的的sql语句:
SELECT [t0].[ProjectID], [t0].[ProjectName]
FROM [dbo].[Project] AS [t0]
WHERE (CONVERT(NVarChar,[t0].[ProjectID])) IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)
string ProjectIDs = db.EmpHaveProj.SingleOrDefault(e => e.EmployeeID == EmployeeID.ToInt()).ProjectIDs;
--ProjectIDs=",1,4,7,6,16,21,22,31,32," 相当于In的范围
string[] lst = ProjectIDs.Split(',');
var proj = from o in db.Project
where (lst.Contains(o.ProjectID.ToString()))
select new { o.ProjectID, o.ProjectName };
return proj;
生的的sql语句:
SELECT [t0].[ProjectID], [t0].[ProjectName]
FROM [dbo].[Project] AS [t0]
WHERE (CONVERT(NVarChar,[t0].[ProjectID])) IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)