LINQ计数查询返回1而不是0 [英] LINQ count query returns a 1 instead of a 0
本文介绍了LINQ计数查询返回1而不是0的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下观点:-
CREATE VIEW tbl_adjudicator_result_view
AS
SELECT a.adjudicator_id, sar.section_adjudicator_role_id, s.section_id, sdr.section_dance_role_id, d.dance_id, c.contact_id,
ro.round_id, r.result_id, c.title, c.first_name, c.last_name, d.name, r.value, ro.type
FROM tbl_adjudicator a
INNER JOIN tbl_section_adjudicator_role sar on sar.section_adjudicator_role2adjudicator = a.adjudicator_id
INNER JOIN tbl_section s on sar.section_adjudicator_role2section = s.section_id
INNER JOIN tbl_section_dance_role sdr on sdr.section_dance_role2section = s.section_id
INNER JOIN tbl_dance d on sdr.section_dance_role2dance = d.dance_id
INNER JOIN tbl_contact c on a.adjudicator2contact = c.contact_id
INNER JOIN tbl_round ro on ro.round2section = s.section_id
LEFT OUTER JOIN tbl_result r on r.result2adjudicator = a.adjudicator_id AND r.result2dance = d.dance_id
当我直接对数据库运行以下查询时,在没有结果的count列中得到0
When I run the following query directly against the db I get 0 in the count column where there is no result
select adjudicator_id, first_name, COUNT(result_id)
from tbl_adjudicator_result_view arv
where arv.round_id = 16
group by adjudicator_id, first_name
但是,当我使用LINQ查询时,我总是在Count列中获得1
However when I use LINQ query I always get 1 in the Count Column
var query = from arv in db.AdjudicatorResultViews
where arv.round_id == id
group arv by new { arv.adjudicator_id, arv.first_name} into grp
select new AdjudicatorResultViewGroupedByDance
{
AdjudicatorId = grp.Key.adjudicator_id,
FirstName = grp.Key.first_name,
Count = grp.Select(p => p.result_id).Distinct().Count()
};
在View/Linq查询中我需要更改什么.
What do I need to change in the View / Linq query.
推荐答案
您在LINQ查询中所做的与在SQL中所做的不一样. COUNT(result_id)
不计算result_id
的不同值-它计算非空值.
You're not doing the same thing in the LINQ query as in the SQL. COUNT(result_id)
does not count distinct values of result_id
- it counts non-null values.
尝试以下方法:
Count = grp.Select(p => p.result_id).Where(x => x != null).Count()
这篇关于LINQ计数查询返回1而不是0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文