Linq Count where IDs are different

Question

I have the following DB-Structure: Please ignore where it says NULL

So now I want to use Linq to count where the DateAndTimeDateType.Day Field equals a given value and where the EventID is different.

Example:

So from DateAndTimeID 11 to 15, the day is 22.03.2019 and the EventID is the same for all of them. Now I want to count where the ID is different. So in this case the result should be 1.

At the moment I'm just counting where the Day is the same like shown in following code:

@Model.DateAndTimes
    .Where(f => f.DateAndTimeDateType.Value.DayOfYear == renderedDayWithTime.DayOfYear)
    .Count()

@OwenPauling I was just about to give this link :) and another for the GroupBy linq method. — pix, Mar 19 '19 at 13:09
This is not a duplicate (at least not of the specified question) as the operation here is of filtering and there is no need of grouping.. — Gilad Green, Mar 19 '19 at 13:15

Gilad Green · Accepted Answer · 2019-03-19T13:14:03.540

3

After filtering for the date you could project the EventID and then use .Distinct:

var count = @Model.DateAndTimes
    .Where(f => f.DateAndTimeDateType.Value.DayOfYear == renderedDayWithTime.DayOfYear)
    .Select(f => f.EventID)
    .Distinct()
    .Count();

Note that if indeed you want to do this for all/a range of DayOfYear values then the way to go is to first GroupBy and then for each group have a distinct count

In addition, if indeed you have date from more than a single year you probably want to use .Date instead of .DatOfYear. This will give you the dates without the times.

edited Mar 19 '19 at 13:14

answered Mar 19 '19 at 13:08

Gilad Green

36,708
7
61
95

I guess OP want to compare Date not only year. please correct me if I am wrong. This statement *So from DateAndTimeID 11 to 15, the day is 22.03.2019 and the eventid is the same for all of them* – Prasad Telkikar Mar 19 '19 at 13:12
@PrasadTelkikar - possibly true. Added a sentence about it. Thanks – Gilad Green Mar 19 '19 at 13:14

Prasad Telkikar · Answer 2 · 2019-03-19T13:11:52.280

2

You can use something like

var givenDateTime = DateTime.Now; //Your date and time
var count = @Model.DateAndTimes
            .Where(f => f.DateAndTimeDateType.Value.Date == givenDateTime.Date)
            .Select(x => x.EventId)
            .Distinct()
            .Count();

Here we consider that, Your f.DateAndTimeDateType.Value is of type DateTime and you want to check only date.

edited Mar 19 '19 at 13:11

answered Mar 19 '19 at 13:08

Prasad Telkikar

15,207
5
21
44

Linq Count where IDs are different

2 Answers2