Linq for grouping by field1, count field2 and filtering by count between values of combined collection
I am having trouble getting the correct answer to my linq query. I have been resisting doing this with foreach loops because I am trying to understand linq better.
I have the 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; }
}
}
The problem is that I need to filter the results using the MinOccurences and MaxOccurences filter property, and at the end I want the "event" objects where the IDs are 1,2,3 and 4.
Thanks in advance for your help.
source to share
To access it event.ID
at the end of processing, you need to pass it on as the first request. Alter select
:
// ...
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 this, your final request (including the attachment 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);
The result 1
, 2
. 3
and are 4
not counted because they do not filter past occurrences.
source to share
I have run your program in linqpad.
My understanding is that you want to filter using filter.MinOccurences and filter.MaxOccurences on the result counter counts.
You can add additional filters using the Where clause.
if (filter.MinOccurences.HasValue)
summary = summary.Where (x=> x.Occurences >= filter.MinOccurences);
if (filter.MaxOccurences.HasValue)
summary = summary.Where (x=> x.Occurences <= filter.MaxOccurences);
source to share