C# search a list using groupby and where

Question

I am trying to search for an empty time slot searching multiple customers by slot number. If the time is available for that customer it should return the first customer found.

Sample data (Each customer has a unique slot number)

customer 1: slotnumber 0, time 0
customer 1: slotnumber 0, time 1
customer 1: slotnumber 0, time 2
customer 1: slotnumber 0, time 4

customer 2: slotnumber 1, time 0
customer 2: slotnumber 1, time 1

I have the following class:

public class CustomerSlot
{
    public int customerid { get; set; }  
    public int slotnumber { get; set; }  
    public int time { get; set; }
}

Then I have a list of customersslots:

List<CustomerSlot> lstCustomerSlots = new List<CustomerSlot>();

Now lets say I want to find the first slot with time 3 available using the above data.

This is where i need help. I dont quite have the correct syntax to group by the customer and return a customer that has that time slot available:

CustomerSlot timeSpaceFound = lstCustomerSlots
                                  .Where(t => t.time != 3)   // Search for time 3
                                  .GroupBy(c => c.customerid) // Search an entire customer
                                  .OrderBy(c => c.customerid) // Start searching by the order of first customerid
                                  .FirstOrDefault();

Any help setting up the above lambda would be appreciated. Have not found any examples searching for a solution online. Thank you.

Your question is a bit confusing. Do you want the first slot per customer? Or just the first arbitrary records that is not equal to 3? — maccettura, May 25 '18 at 18:39
Thank you. Correct the first time slot for a customer. But return only the first customer found. — Robert Smith, May 25 '18 at 18:40
If you filter out the entries that have a time of 3 first, then all of your groups will look like they have the time slot available. You should group by customer first, then filter the customers that are already using that slot. — Jonathon Chase, May 25 '18 at 18:41
Well if you are querying to get the first time slot _per customer_ why is `timeSpaceFound` a _single_ `CustomerSlot` — maccettura, May 25 '18 at 18:41
No sorry, I am just looking for the very first customer found. It should return the first customer with the empty time slot. Sorry for the confusion. In the above example it should return customer 1, since its the first one found (order by customerid) that has the free time slot of 3. — Robert Smith, May 25 '18 at 18:43

NetMage · Answer 1 · 2018-05-25T20:12:32.683

You need to group the times by customer and then find the lowest customer id without the timeslot:

var timeSpaceFound = lstCustomerSlots.GroupBy(c => c.customerid)
                                     .Where(cg => !cg.Any(c => c.time == 3))
                                     .OrderBy(cg => cg.Key)
                                     .FirstOrDefault()
                                     ?.Key;

I prefer using an extension method MinBy:

public static T MinBy<T, TKey>(this IEnumerable<T> src, Func<T,TKey> keySelector, Comparer<TKey> keyComparer) => src.Aggregate((a, b) => keyComparer.Compare(keySelector(a), keySelector(b)) < 0 ? a : b);
public static T MinBy<T, TKey>(this IEnumerable<T> src, Func<T,TKey> keySelector) => src.Aggregate((a, b) => Comparer<TKey>.Default.Compare(keySelector(a), keySelector(b)) < 0 ? a : b);

Then you just do

var timeSpaceFound = lstCustomerSlots.GroupBy(c => c.customerid)
                                     .Where(cg => !cg.Any(c => c.time == 4))
                                     .MinBy(cg => cg.Key)
                                     ?.Key;

That should be more efficient as Aggregate will only make one pass through the list.

If you prefer receiving the CustomerSlot instead of the customerid, you need to get the first in the group (unless you need a particular timeslot):

var timeSpaceFound = lstCustomerSlots.GroupBy(c => c.customerid)
                                     .Where(cg => !cg.Any(c => c.time == 4))
                                     .MinBy(cg => cg.Key)
                                     .FirstOrDefault();

Thanks NetMage! Is there a way to return a customer object? (instead of: var timeSpaceFound =... have: CustomerSlot timeSpaceFound = ... ) — Robert Smith, May 25 '18 at 19:10
@RobertSmith To return a `CustomerSlot`, just replace `?.Key` with `FirstOrDefault()`. — NetMage, May 25 '18 at 20:11
NetMage, I am trying your first example (the one without the extension method) because I am having problems with Jonathon's solution (see his comments above) But for your first solution I am getting an error: Cannot implicitly convert type System.Linq.IGrouping< to... — Robert Smith, May 29 '18 at 14:01
@RobertSmith The rest of that error message is important - I don't get that error on LINQ to Objects. — NetMage, May 29 '18 at 18:09

Jonathon Chase · Accepted Answer · 2018-05-29T16:39:06.360

1

If I understand correctly, you want to select from your list the first customer that does not have any time slot entry already there for 3.

CustomerSlot timeSpaceFound = lstCustomerSlots
                                  .GroupBy(c => c.customerid) // Group entries by customer
                                  .Where(g => g.All(c => c.time != 3)) // Remove all groups that have a time entry of 3
                                  .OrderBy(g => g.Key) // Start searching by the order of first customerid
                                  .FirstOrDefault() // Select the first group
                                  ?.FirstOrDefault(); // Select the first CustomerSlot entry in the group.

edited May 29 '18 at 16:39

answered May 25 '18 at 18:56

Jonathon Chase

9,396
21
39

1

`!Any()` should be more efficient than `All(!)`. – NetMage May 25 '18 at 18:57
@NetMage Really? I thought their implementations were basically identical for this case, but that's something I'll definitely be looking into. – Jonathon Chase May 25 '18 at 19:00
Thank you Jonathon, that was an excellent solution! I did change the .All to .Any as suggested by NetMage. – Robert Smith May 25 '18 at 19:29
That's fine @RobertSmith, but I'm still uncertain there is any performance cost or gain between the two after reading [this SO post](https://stackoverflow.com/questions/9027530/linq-not-any-vs-all-dont) – Jonathon Chase May 25 '18 at 19:30
1

@JonathonChase Consider looking for `time` `0` free - `!Any` can stop after the first test in each group (assuming they are in `time` order) and `All` will test every member of each group. On average, `Any` should do half the work of `All`, depending on the slot you are looking for, independent of the `time` order (if you are always searching for a high end slot, not so much if they are ordered.) – NetMage May 25 '18 at 20:15
Well, correction. It really depends on the `time` being used or not used for customers being more prevalent, than possibly the order of the `time` values. Overall, I still think `Any` is more likely to shortcut sooner. – NetMage May 25 '18 at 20:17
@NetMage I think that's where my confusion is on this. If we're checking in order, the predicate should hit it's condition on the same item and end execution. In this case, I think it boils down to personal preference and what seems more readable. That said, this may just be specific with LINQ to Objects. LINQ to SQL may have very different results. – Jonathon Chase May 25 '18 at 20:46
@NetMage I'm glad you brought it up in any case, always good to dive into the internals and make sure we know what we know. – Jonathon Chase May 25 '18 at 20:52
Jonathon, I continued testing and ran into an issue. If no time slots are returned, I am getting this error: Value cannot be null.\r\nParameter name: source – Robert Smith May 29 '18 at 13:38
@RobertSmith Use a null conditional operator before the last FirstOrDefault. `FirstOrDefault()?.FirstOrDefault();` – Jonathon Chase May 29 '18 at 16:38

C# search a list using groupby and where

2 Answers2