Linq查询按字段1分组,对字段2进行计数,并按连接集合的值之间的计数进行过滤 [英] Linq query to group by field1, count field2 and filter by count between values of joined collection
问题描述
我无法正确设置linq查询.我一直拒绝使用foreach循环来这样做,因为我试图更好地理解linq.
I'm having trouble with getting a my linq query correct. I've been resisting doing this with foreach loops because I'm trying to better understand linq.
我在LinqPad中有以下数据.
I have following data in LinqPad.
void Main()
{
var events = new[] {
new {ID = 1, EventLevel = 1, PatientID = "1", CodeID = "2", Occurences = 0 },
new {ID = 2, EventLevel = 2, PatientID = "1", CodeID = "2", Occurences = 0 },
new {ID = 3, EventLevel = 1, PatientID = "2", CodeID = "1", Occurences = 0 },
new {ID = 4, EventLevel = 3, PatientID = "2", CodeID = "2", Occurences = 0 },
new {ID = 5, EventLevel = 1, PatientID = "3", CodeID = "3", Occurences = 0 },
new {ID = 6, EventLevel = 3, PatientID = "1", CodeID = "4", Occurences = 0 }
};
var filter = new FilterCriterion();
var searches = new List<FilterCriterion.Occurence>();
searches.Add(new FilterCriterion.Occurence() { CodeID = "1", MinOccurences = 2, MaxOccurences = 3 });
searches.Add(new FilterCriterion.Occurence() { CodeID = "2", MinOccurences = 2, MaxOccurences = 3 });
filter.Searches = searches;
var summary = from e in events
let de = new
{
PatientID = e.PatientID,
CodeID = e.CodeID
}
group e by de into t
select new
{
PatientID = t.Key.PatientID,
CodeID = t.Key.CodeID,
Occurences = t.Count(d => t.Key.CodeID == d.CodeID)
};
var allCodes = filter.Searches.Select(i => i.CodeID);
summary = summary.Where(e => allCodes.Contains(e.CodeID));
// How do I find the original ID property from the "events" collection and how do I
// eliminate the instances where the Occurences is not between MinOccurences and MaxOccurences.
foreach (var item in summary)
Console.WriteLine(item);
}
public class FilterCriterion
{
public IEnumerable<Occurence> Searches { get; set; }
public class Occurence
{
public string CodeID { get; set; }
public int? MinOccurences { get; set; }
public int? MaxOccurences { get; set; }
}
}
我遇到的问题是需要通过MinOccurences和MaxOccurences过滤器属性来过滤结果,最后我想要ID为1,2、3和4的事件"对象.
The problem I have is that need to filter the results by the MinOccurences and MaxOccurences filter property and in the end I want the "events" objects where the IDs are 1,2,3 and 4.
如果能提供帮助,请先感谢.
Thanks in advance if you can provide help.
推荐答案
要在处理结束时访问event.ID
,您需要在第一个查询中传递它.将select
更改为此:
To access event.ID
at the end of processing you need to pass it with your first query. Alter select
to this:
// ...
group e by de into t
select new
{
PatientID = t.Key.PatientID,
CodeID = t.Key.CodeID,
Occurences = t.Count(d => t.Key.CodeID == d.CodeID),
// taking original items with us
Items = t
};
这样做,您的最终查询(包括事件过滤器)可能如下所示:
Having done that, your final query (including occurrences filter) might look like this:
var result = summary
// get all necessary data, including filter that matched given item
.Select(Item => new
{
Item,
Filter = searches.FirstOrDefault(f => f.CodeID == Item.CodeID)
})
// get rid of those without matching filter
.Where(i => i.Filter != null)
// this is your occurrences filtering
.Where(i => i.Item.Occurences >= i.Filter.MinOccurences
&& i.Item.Occurences <= i.Filter.MaxOccurences)
// and finally extract original events IDs
.SelectMany(i => i.Item.Items)
.Select(i => i.ID);
这将产生1
,2
作为结果. 3
和4
被遗漏了,因为它们没有得到过去过滤的结果.
This produces 1
, 2
as result. 3
and 4
are left out as they don't get past occurrences filtering.
这篇关于Linq查询按字段1分组,对字段2进行计数,并按连接集合的值之间的计数进行过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!