linq c＃with group sum join [试图将sql查询转换为linq查询] [英] linq c# with group sum join [Trying to convert sql query to linq query]

问题描述

选择app.Application_Category为appcat，

SUM（Total_Expected_Availability）为tot_exp_avl，

SUM（Planned_Outage）为tot_pld，

SUM（ Unplanned_Outage）为tot_unpld，

SUM（Unplanned_Outage- Unplanned_SignOff）为tot_unpld_sgn，

app.MSL，

app.ESL，

（总和（Total_Expected_Availability-Planned_Outage-（Unplanned_Outage + Unplanned_SignOff））/ SUM（Total_Expected_Availability-Planned_Outage））* 100 perc_achd
来自App_Master_2 app的


内连接app.Application_Category上的Outage_Metrics outmet = outmet.Application_Category和app.Application_ID = outmet.Application_ID

其中outmet.Month ='9'和outmet.Year ='2013'

按app.Application_Category分组，app.MSL，app.ESL





i尝试这样但显示错误



maindashboardreport =（来自lMOutage_Metrics中的outMet

加入lstApp_M中的app aster_2

on outMet.Application_ID等于app.Application_ID

其中outMet.Application_Category == app.Application_Category && outMet.Month == monthValue && outMet.Year == yearValue

将app {app.Application_Category，app.MSL，app.ESL，outMet.Total_Expected_Availability，outMet.Planned_Outage，outMet.Unplanned_Outage，outMet.Unplanned_SignOff}分组应用到g

select新的
{

g.Key.Application_Category，

g.Sum（t => t.Total_Expected_Availability）。GetValueOrDefault（），

g.Sum（t => t.Planned_Outage）.GetValueOrDefault（），

g.Sum（t => t.Unplanned_Outage）。GetValueOrDefault（），

（g.Sum（t => t.Unplanned_Outage）.GetValueOrDefault（） - g.Sum（t => t.Unplanned_SignOff）.GetValueOrDefault（ ）），

g.Key.MSL.GetValueOrDefault（），

g.Key.ESL.GetValueOrDefault（）

}）。Distinct （）。ToList（）;



请帮我解决这个问题..

Select app.Application_Category as appcat,
SUM(Total_Expected_Availability) as tot_exp_avl,
SUM(Planned_Outage) as tot_pld,
SUM(Unplanned_Outage) as tot_unpld,
SUM(Unplanned_Outage- Unplanned_SignOff) as tot_unpld_sgn,
app.MSL,
app.ESL,
(Sum(Total_Expected_Availability-Planned_Outage-(Unplanned_Outage+Unplanned_SignOff))/SUM(Total_Expected_Availability-Planned_Outage))*100 as perc_achd
from App_Master_2 app
inner join Outage_Metrics outmet on app.Application_Category=outmet.Application_Category and app.Application_ID=outmet.Application_ID
where outmet.Month='9' and outmet.Year='2013'
Group by app.Application_Category,app.MSL,app.ESL


i tried like this but it shows error

maindashboardreport = (from outMet in lstOutage_Metrics
join app in lstApp_Master_2
on outMet.Application_ID equals app.Application_ID
where outMet.Application_Category == app.Application_Category && outMet.Month == monthValue && outMet.Year == yearValue
group app by new { app.Application_Category, app.MSL, app.ESL, outMet.Total_Expected_Availability, outMet.Planned_Outage, outMet.Unplanned_Outage, outMet.Unplanned_SignOff } into g
select new
{
g.Key.Application_Category,
g.Sum(t => t.Total_Expected_Availability).GetValueOrDefault(),
g.Sum(t => t.Planned_Outage).GetValueOrDefault(),
g.Sum(t => t.Unplanned_Outage).GetValueOrDefault(),
(g.Sum(t => t.Unplanned_Outage).GetValueOrDefault() - g.Sum(t => t.Unplanned_SignOff).GetValueOrDefault()),
g.Key.MSL.GetValueOrDefault(),
g.Key.ESL.GetValueOrDefault()
}).Distinct().ToList();

pls help me to fix this..

推荐答案

示例Linq查询使用Join，group，Sum。



var maindashboardreport =（来自lMOutage_Metrics中的outMet

加入lstApp_Master_2中的应用程序

on outMet.Application_ID等于app.Application_ID

其中outMet.Application_Category == app.Application_Category && outMet.Month == monthValue && outMet.Year == yearValue

group new

{

app.Application_Category，

app.MSL，

app.ESL，

outMet.Total_Expected_Availability，

outMet.Planned_Outage，

outMet.Unplanned_Outage，

outMet.Unplanned_SignOff

}新的

{

app.Application_Category，

app.MSL，

app.ESL

}进入g


选择新的Application_Avail

{

Application_Category = g.Key.Application_Category，

tot_exp_avl = g.Sum（t => t.Total_Expected_Availability）。GetValueOrDefault（），// sum（t => t.total_expected_availability）.getvalueordefault（），

tot_pld = g.Sum（t => t.Planned_Outage）.GetValueOrDefault （），

tot_unpld = g.Sum（t => t.Unplanned_Outage）.GetValueOrDefault（），

tot_unpld_sgn =（g.Sum（t => t .Unplanned_Outage）。GetValueOrDefault（） - g.Sum（t => t.Unplanned_SignOff）。GetValueOrDefault（）），

MSL = g.Key.MSL.GetValueOrDefault（），

ESL = g.Key.ESL.GetValueOrDefault（）

}）。Distinct（）。ToList（）;

sample Linq query which using Join, group, Sum.

var maindashboardreport = (from outMet in lstOutage_Metrics
join app in lstApp_Master_2
on outMet.Application_ID equals app.Application_ID
where outMet.Application_Category == app.Application_Category && outMet.Month == monthValue && outMet.Year == yearValue
group new
{
app.Application_Category,
app.MSL,
app.ESL,
outMet.Total_Expected_Availability,
outMet.Planned_Outage,
outMet.Unplanned_Outage,
outMet.Unplanned_SignOff
} by new
{
app.Application_Category,
app.MSL,
app.ESL
} into g

select new Application_Avail
{
Application_Category = g.Key.Application_Category,
tot_exp_avl = g.Sum(t => t.Total_Expected_Availability).GetValueOrDefault(),// sum(t => t.total_expected_availability).getvalueordefault(),
tot_pld = g.Sum(t => t.Planned_Outage).GetValueOrDefault(),
tot_unpld = g.Sum(t => t.Unplanned_Outage).GetValueOrDefault(),
tot_unpld_sgn = (g.Sum(t => t.Unplanned_Outage).GetValueOrDefault() - g.Sum(t => t.Unplanned_SignOff).GetValueOrDefault()),
MSL = g.Key.MSL.GetValueOrDefault(),
ESL = g.Key.ESL.GetValueOrDefault()
}).Distinct().ToList();

select * from PersonInfo
where code
in
(select a.Code from PersonInfo as a ,TradeElamieh as b
where a.Code=b.person1 and b.Actual=1 and (a.Investor_ID like'1338%' or a.Investor_ID like'1339%')
 and b.date>'2012-01-01' and a.Investor_ID IS NOT NULL)

这篇关于linq c＃with group sum join [试图将sql查询转换为linq查询]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！