how to get this data via linq

Question

public class Employee
{
    public int ID { get; set; }
    public string Name { get; set; }
}

public class LoginAudit
{
    public int ID { get; set; }
    public DateTime LoginTime { get; set; }
}



static void Main(string[] args)
{

    List<Employee> AllEmployees = new List<Employee>() 
    {
        new Employee() { ID = 1, Name = "John"},
        new Employee() { ID = 2, Name = "Pete"},
        new Employee() { ID = 3, Name = "Sue"},
        new Employee() { ID = 4, Name = "Chris"},
        new Employee() { ID = 5, Name = "Kate"},
        new Employee() { ID = 6, Name = "Smith"},
        new Employee() { ID = 7, Name = "Bil"},
        new Employee() { ID = 8, Name = "Shan"},
        new Employee() { ID = 9, Name = "Joe"},
        new Employee() { ID = 10, Name = "Lure"}
    };


    List<int> SomeEmployeesIDs = new List<int>() 
    {
        1, 2, 5, 6, 10
    };

    List<LoginAudit> EmployeeLogins = new List<LoginAudit>() 
    {
        new LoginAudit() { ID = 1, LoginTime = new DateTime(2015,1,11)},
        new LoginAudit() { ID = 5, LoginTime = new DateTime(2015,2,2)}
    };

}

I want to select everything from employees from AllEmployees meeting following conditions.
1. Have ID in SomeEmployeesIDs AND
2. Either haven't login in all ( meaning have no entry in EmployeeLogins) OR if their last login was over 7 days ago)

Based on this, I should be be getting Employee with IDs 1, 5, 6 and 10. Can someone help me writing Linq query to retrieve this data?

whoever voted down, can you let me know the reason. So I can lear from it — paul deter, Feb 02 '15 at 23:02
Probably because you haven't shown what attempt you've made to solve the problem yourself. — Erik, Feb 02 '15 at 23:04
i suspect (wasnt me) because you dont show anything you tried — pm100, Feb 02 '15 at 23:04
you have the criteria, and you know you need LINQ. So have you made any attempt to solve this? have you searched relevant topics? there are thousands of examples on filtering data using LINQ. — Selman Genç, Feb 02 '15 at 23:04
I am new to linq and I just don't know who how to a AND/OR with linq. I didn't find any examples but I just don't know enough about linq — paul deter, Feb 02 '15 at 23:06

score 2 · Answer 1 · answered Feb 02 '15 at 23:05

2

I think you need something like this:

var result = AllEmployees.Where( x=> SomeEmployeesIDs.Contains(x.ID) &&                     
                                ((!EmployeeLogins.Any(y=>y.ID==x.ID) ||
                                   EmployeeLogins.Any(y=> y.ID==x.ID && 
                                   (y.LoginTime-DateTime.Now).Days>7)));

answered Feb 02 '15 at 23:05

Christos

53,228
8
76
108

i think this is the best way to find your data – ako Feb 02 '15 at 23:10
I actually upvoted it but I am not getting desired results with it – paul deter Feb 02 '15 at 23:18
1

Ineffficient if EmployeeLogins is large since you need to enumerate it twice. – Tim Schmelter Feb 02 '15 at 23:23

score 2 · Accepted Answer · edited May 23 '17 at 12:27

2

You can use this query:

var q = from emp in AllEmployees
        join eId in SomeEmployeesIDs on emp.ID equals eId
        where !EmployeeLogins
            .Any(el => el.ID == emp.ID && (DateTime.Now - el.LoginTime).Days <= 7)
        select emp;

This condition in Any covers both cases and the Join is an efficient way to take only employees which ID is in the other collection.

If you don't like query syntax you could use this (if the collection is small use Contains):

var q = AllEmployees
   .Where(emp => SomeEmployeesIDs.Contains(emp.ID)
          && !EmployeeLogins
            .Any(el => el.ID == emp.ID 
                 && (DateTime.Now - el.LoginTime).Days <= 7));

edited May 23 '17 at 12:27

Community

1
1

answered Feb 02 '15 at 23:07

Tim Schmelter

450,073
74
686
939

As long as `SomeEmployeesIDs` is unique `join` is a good idea. – Magnus Feb 02 '15 at 23:17
@pauldeter: if you insist on method syntax i avoid `Join`. Have a look. – Tim Schmelter Feb 02 '15 at 23:22
is the one with method syntax is performance wise bad compared to one with join? – paul deter Feb 02 '15 at 23:27
@pauldeter: no, not really. `Join` is more efficient if the collections are large, but normally it shouldn't make a difference. But as Magnus has mentioned, `Join` would "create" multiple rows if `SomeEmployeesIDs` contained duplicates. `.Where(emp => SomeEmployeesIDs.Contains(emp.ID))` doesn't have this pitfall. – Tim Schmelter Feb 02 '15 at 23:30

Cyral · Answer 3 · 2015-02-02T23:17:14.233

2

Use a query like this:

var employees = AllEmployees.Where(
    e => SomeEmployeesIDs.Contains(e.ID) &&
    (EmployeeLogins.All(l => l.ID != e.ID) || 
    EmployeeLogins.Where(l => l.ID == e.ID).Any(l => (DateTime.Now - l.LoginTime).Days > 7)));

edited Feb 02 '15 at 23:17

answered Feb 02 '15 at 23:09

Cyral

13,999
6
50
90

Inefficient since you enumerate EmployeeLogins twice. – Tim Schmelter Feb 02 '15 at 23:25

how to get this data via linq

3 Answers3