Case statement in NHibernate / EF Linq
What will be the equivalent of following SQL statement in Linq? I'm specifically looking on how to replace CASE statements in Linq expression.
SELECT a.app_id AS Id,
a.开发者_如何学编程app_nm AS [Name],
COUNT(CASE
WHEN l.level_nm = 'ERROR' THEN l.log_id
ELSE NULL
END) AS Error,
COUNT(CASE
WHEN l.level_nm = 'FATAL' THEN l.log_id
ELSE NULL
END) AS Fatal
FROM info_log l
INNER JOIN application_info a
ON a.app_id = l.app_id
WHERE l.level_nm IN ( 'ERROR', 'FATAL' )
GROUP BY a.app_id,
a.app_nm
This should help you out
var query = from l in context.info_log
from a in context.application_info
where l.app_id == a.app_id
where l.level_nm == "ERROR" || l.level_nm == "FATAL"
group l by new { a.app_id, a.app_nm } into lg
select new
{
Id = lg.Key.app_id,
Name = lg.Key.app_nm,
Error = lg.Where(x => x.level_nm == "ERROR").Count(),
Fatal = lg.Where(x => x.level_nm == "FATAL").Count(),
};
精彩评论