将此连接SQL查询转换为linq

Question

如何将此sql查询转换为linq

 select sum(a.Count),b.PeriodDesc from dbo.tbl_Sample a,dbo.tbl_Period b 
 where a.PeriodID=b.PeriodID group by PeriodDesc 

tbl_Period -PeriodID, PeriodDesc
tbl_Sample - ID,Count,PeriodID

Answer 1

让我们假设YourContext是生成上下文，并且您在tbl_Period和tbl_Sample之间有导航属性，那么查询将看起来像:

   using(var db = new YourContext())
   {
          var result = db.tbl_Sample.GroupBy(p=>p.tbl_Period.PeriodDesc)
          .Select(p => new {p.Key, p.Count()})
          //whateve you want to do ...
   }

Answer 2

请检查

var result = (from a in tbl_Sample
group a by a.PeriodDesc into abc                         
join b in tbl_Period on abc.FirstOrDefault().PeriodID equals b.PeriodID    
 select new ModelName {
   SumofCount = abc.sum(m => m.Count), 
   PeriodDesc = b.PeriodDesc 
 }).tolist();

Answer 3

试试这个

var joinedTables = from p in listPeriod
join s in listSample on p.PeriodID equals s.PeriodID
select new { p.PeriodDesc, s.Count };
var result = from p in joinedTables
group p by p.PeriodDesc into g
select new { PeriodDesc = g.Key, Sum = g.Sum(p=>p.Count)};

您必须使用正确的集合(或表实体)更改listPeriod和listSample