LINQ: Get an item that is not in the records - C#

Question

This is an extended question back from my POST

class EmployeeSchedule
{
   public int Id { get; set; }
   public string Name { get; set; }
   public DateTime WorkDate { get; set; }
   public bool isOff { get; set; }
}

Now in this case i would like to find an Id whose WorkDate is not found in 4/12/2016 from the List as my criteria.

List<Staff> workers = new List<Staff>()
{
    new Staff { Id = 1, Name = "Emp 1", WorkDate = Convert.ToDateTime("4/11/2016"), IsOff = false},
    new Staff { Id = 1, Name = "Emp 1", WorkDate = Convert.ToDateTime("4/12/2016"), IsOff = false},
    new Staff { Id = 1, Name = "Emp 1", WorkDate = Convert.ToDateTime("4/13/2016"), IsOff = false},
    new Staff { Id = 1, Name = "Emp 1", WorkDate = Convert.ToDateTime("4/14/2016"), IsOff = false},
    new Staff { Id = 1, Name = "Emp 1", WorkDate = Convert.ToDateTime("4/15/2016"), IsOff = false},
    new Staff { Id = 1, Name = "Emp 1", WorkDate = Convert.ToDateTime("4/16/2016"), IsOff = false},
    new Staff { Id = 1, Name = "Emp 1", WorkDate = Convert.ToDateTime("4/17/2016"), IsOff = false},
    new Staff { Id = 2, Name = "Emp 2", WorkDate = Convert.ToDateTime("4/11/2016"), IsOff = false},
    // new Staff { Id = 2, Name = "Emp 2", WorkDate = Convert.ToDateTime("4/12/2016"), IsOff = false},
    new Staff { Id = 2, Name = "Emp 2", WorkDate = Convert.ToDateTime("4/13/2016"), IsOff = false},
    new Staff { Id = 2, Name = "Emp 2", WorkDate = Convert.ToDateTime("4/14/2016"), IsOff = false},
    new Staff { Id = 2, Name = "Emp 2", WorkDate = Convert.ToDateTime("4/15/2016"), IsOff = false},
    new Staff { Id = 2, Name = "Emp 2", WorkDate = Convert.ToDateTime("4/16/2016"), IsOff = false},
    new Staff { Id = 2, Name = "Emp 2", WorkDate = Convert.ToDateTime("4/17/2016"), IsOff = false},
};

So far i have tried this code:

var notInDate = workers.Where(x => !workers.Any(y => Convert.ToDateTime("4/12/2016") != x.WorkDate));

foreach (var item in notInDate)
{
    Console.WriteLine(item.Id + " " + item.WorkDate.Date);
}

Which has the output of: ID :1 WorkDate: 4/12/2016 12:00:00 AM

And this one with this output:

var notInDate = workers.Where(x => workers.Any(y => Convert.ToDateTime("4/12/2016") != x.WorkDate));

ID :1 WorkDate: 4/11/2016 12:00:00 AM
ID :1 WorkDate: 4/13/2016 12:00:00 AM
ID :1 WorkDate: 4/14/2016 12:00:00 AM
ID :1 WorkDate: 4/15/2016 12:00:00 AM
ID :1 WorkDate: 4/16/2016 12:00:00 AM
ID :1 WorkDate: 4/17/2016 12:00:00 AM
ID :2 WorkDate: 4/11/2016 12:00:00 AM
ID :2 WorkDate: 4/13/2016 12:00:00 AM
ID :2 WorkDate: 4/14/2016 12:00:00 AM
ID :2 WorkDate: 4/15/2016 12:00:00 AM
ID :2 WorkDate: 4/16/2016 12:00:00 AM
ID :2 WorkDate: 4/17/2016 12:00:00 AM

The second example just removed the record whose WorkDate is 4/12/2016

I would like to get whose Id = 2 because that Id do not have the WorkDate of 4/12/2016

From what I see in the data posted there is an ID of 1 that also has a WorkDate of 4/12/2016, do you not want that one, just the one with an ID of 2? — davidallyoung, Apr 13 '16 at 04:12
@DavidY yes because `Id = 2` doest have a record of a given WorkDate criteria. — Shift 'n Tab, Apr 13 '16 at 04:21

Joachim Isaksson · Accepted Answer · 2016-04-13T04:20:19.747

4

If you want all entries from people that did not work on that day, you need to group by id (to get a group per person) and filter on individual groups that don't contain the date. Something like;

var notInDate = 
         // Create a group per employee
  workers.GroupBy (x => x.Id)
         // Keep only groups that didn't work
         .Where (x => !x.Any (y => y.WorkDate == Convert.ToDateTime ("4/12/2016")))
         // Get only the key for each group
         .Select (x => x.Key);

edited Apr 13 '16 at 04:20

answered Apr 13 '16 at 04:11

Joachim Isaksson

176,943
25
281
294

It is still a list of `Id = 2` but i achieved the result by editing your answer to this `var notInDate = // Create a group per employee workers.GroupBy(x => x.Id) // Keep only groups that didn't work .Where(x => !x.Any(y => y.WorkDate == Convert.ToDateTime("4/14/2016"))) // Flatten the groups back to rows .Select(x => x.Key);` – Shift 'n Tab Apr 13 '16 at 04:16
I would like to accept this answer please update it with the code i suggested – Shift 'n Tab Apr 13 '16 at 04:18

Hari Prasad · Answer 2 · 2016-04-13T04:28:44.853

2

You could do this, use GroupBy to group on Id and then validate whether staff worked on that date using Any extension..

var notInDate = workers.GroupBy(g=>g.Id)
                       .Where(x => !x.Any(y => DateTime.ParseExact("4/12/2016","M/d/yyyy", null) == y.WorkDate))
                       .FirstOrDefault();  // Get the first matching staff.
if(notInDate != null)
{
    id = notInDate.Key; // StaffId
}

edited Apr 13 '16 at 04:28

answered Apr 13 '16 at 04:10

Hari Prasad

16,716
4
21
35

sir it yield different result but i approved the answer of Joachim – Shift 'n Tab Apr 13 '16 at 04:27
it works, but i prefer the answer above sir big thanks! – Shift 'n Tab Apr 13 '16 at 04:33
No worries, I'm happy as it helped you. – Hari Prasad Apr 13 '16 at 04:34

LINQ: Get an item that is not in the records - C#

2 Answers2