How to find overlapping in collection using LINQ ( not duplicates but to find overlaps)

Question

List<StoreDetailDto> items = new List<StoreDetailDto>();

items.Add(new StoreDetailDto { StoreNumber = 2, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});
items.Add(new StoreDetailDto { StoreNumber = 3, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});
items.Add(new StoreDetailDto { StoreNumber = 6, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});
items.Add(new StoreDetailDto { StoreNumber = 7, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});
items.Add(new StoreDetailDto { StoreNumber = 9, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});

items.Add(new StoreDetailDto { StoreNumber = 6, WarehouseNumber = 4202, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});
items.Add(new StoreDetailDto { StoreNumber = 7, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});
items.Add(new StoreDetailDto { StoreNumber = 9, WarehouseNumber = 4203, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});
items.Add(new StoreDetailDto { StoreNumber = 9, WarehouseNumber = 4207, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1"});

I have collection above where there is a overlapping between store 6, 9 but store 7 is not overlap it's a duplicate. I am finding overlaps as such that same store with different warehouse.

in order to acheive that I am doing below:

var overlapDupStores = items.GroupBy(
    u => u.StoreNumber,
    u => { return u; },
    (key, g) => g.ToList())
  .ToList()
  .Where(cnt => cnt.Count() > 1);


foreach (var dpovl in overlapDupStores)
{
    var stores = dpovl.Where(g => g.IsResolved != true).GroupBy(u => new { u.StoreNumber, u.WarehouseNumber }).ToList();

    if (stores.Count() > 1)
    {
        response.OverlappingStores.AddRange(stores.SelectMany(gr => gr).ToList());
    }
}

I am first finding stores which are duplicates , it will have object inside object with duplicates of stores with count = 2 and then grouping by storenumber and warehouse number for different store and warehouse count will still be 2 but for same store and warehouse which is duplicate it will be count = 1 so i am finding count > 1 so i can find duplicates and check Isresolved not to pull same store again once it is resolved.

Below is the fiddle:

https://dotnetfiddle.net/TknnkJ

@Adriani6 it's not a duplicate of that.. it's opposite of what is being asked previously, that one was find duplicates and this one find overlapping ones — VR1256, Aug 13 '18 at 16:10
Using the Linq Except operator might yield a better solution for this problem. — Phill, Aug 13 '18 at 16:20
@Adriani6 thank you, do you suggest any solution for the question please ?, also I think you downvoted my question — VR1256, Aug 13 '18 at 17:04
@GertArnold I edited the question, that requirement was tough to achieve with equality operator, but based on the below answer I edited it and wanted to acheive same thing on how he is doing below since it look clean way to do it — VR1256, Oct 17 '18 at 17:23

Jimi · Accepted Answer · 2018-11-06T04:47:09.263

Try this one, using LINQ's GroupBy().Select().Where() to group and filter the list of objects based on the criteria that I assume you require, given the output you showed.

A description:

GroupBy() => Groups the objects using the StoreNumber property.
Select() => Selects the objects where the IsResolved property is false
Where() => Filters the objects that have more than 1 elements with different WarehouseNumber
SelectMany()=> Flattens the Grouping, to have all the matching objects in a sequential view

OrderBy() could be added, if required, to order the list on StoreNumber ThenBy() WarehouseNumber.

The Duplicates filter may not be correct, depending on the use case. It might be necessary to adapt it.

var Duplicates = 
    items.GroupBy(store => store.StoreNumber)
         .Select(grp => grp.Where(store => store.IsResolved == false))
         .Where(stores => stores.Count() > 1 && stores.Select(w => w.WarehouseNumber).Distinct().Count() == 1)
         .SelectMany(stores => stores)
         .ToList();

var Overlapping = 
    items.GroupBy(store => store.StoreNumber)
         .Select(grp => grp.Where(store => store.IsResolved == false))
         .Where(store => store.Count() > 1 && store.Select(w => w.WarehouseNumber).Distinct().Count() > 1)
         .SelectMany(stores => stores)
         .ToList();

Overlapping.ForEach(ovr =>
    Console.WriteLine($"{ovr.StoreNumber} " +
                      $"{ovr.WarehouseNumber} " +
                      $"{ovr.IsResolved} " +
                      $"{ovr.StoreName} " +
                      $"{ovr.WarehouseName}"));

This Overlapping List prints:

6 4201 False StoreEx WarehouseEx1
6 4202 False StoreEx WarehouseEx1
9 4201 False StoreEx WarehouseEx1
9 4203 False StoreEx WarehouseEx1
9 4207 False StoreEx WarehouseEx1

This Duplicates List prints:

7 4201 False StoreEx WarehouseEx1
7 4201 False StoreEx WarehouseEx1

score 1 · Answer 2 · answered Nov 02 '18 at 05:50

I think these queries do the job:

var overlaps =
    items
        .GroupBy(x => new { x.StoreNumber, x.WarehouseNumber })
        .GroupBy(x => x.Key.StoreNumber)
        .Where(x => x.Skip(1).Any())
        .Where(x => !x.Any(y => y.Any(z => z.IsResolved)))
        .SelectMany(x => x.SelectMany(y => y))
        .ToList();

var duplicates =
    items
        .GroupBy(x => new { x.StoreNumber, x.WarehouseNumber })
        .GroupBy(x => x.Key.StoreNumber)
        .Where(x => !x.Skip(1).Any())
        .Where(x => x.First().Skip(1).Any())
        .Where(x => !x.Any(y => y.Any(z => z.IsResolved)))
        .SelectMany(x => x.SelectMany(y => y))
        .ToList();

I used this source data:

var items = new List<StoreDetailDto>()
{
    new StoreDetailDto { StoreNumber = 2, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
    new StoreDetailDto { StoreNumber = 3, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
    new StoreDetailDto { StoreNumber = 6, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
    new StoreDetailDto { StoreNumber = 7, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
    new StoreDetailDto { StoreNumber = 9, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
    new StoreDetailDto { StoreNumber = 6, WarehouseNumber = 4202, IsResolved = true, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
    new StoreDetailDto { StoreNumber = 7, WarehouseNumber = 4201, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
    new StoreDetailDto { StoreNumber = 9, WarehouseNumber = 4203, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
    new StoreDetailDto { StoreNumber = 9, WarehouseNumber = 4207, IsResolved = false, StoreName = "StoreEx", WarehouseName = "WarehouseEx1" },
};

It's worth noting that if 7 were to have a third record with a new WarehouseNumber that it would appear in the overlaps result and would include the duplicate there.

score 0 · Answer 3 · answered Aug 13 '18 at 19:42

If I understand you correctly. The following will give you want you want.

   var warehouseCountsByStore =
            from item in items
            group item by new { item.StoreNumber, item.WarehouseNumber } into store
            where store.Count() != 0
            select new
            {
                store.Key.StoreNumber,
                store.Key.WarehouseNumber,
                Count = store.Count()
            };

    var storesWithDuplicateWarehouses =
        from warehouse in warehouseCountsByStore
        where warehouse.Count != 1
        select new { warehouse.StoreNumber };

    var storesWithMultipleWarehouse =
        from item in items
        group item by new { item.StoreNumber } into store
        where store.Count() > 1
              && storesWithDuplicateWarehouses.Any(x => x.StoreNumber != store.Key.StoreNumber)
        select new
        {
            store.Key.StoreNumber
        };

    Console.WriteLine("Duplicates");
    foreach (var store in storesWithDuplicateWarehouses)
        Console.WriteLine(store.StoreNumber);

    Console.WriteLine("Overlap");
    foreach (var store in storesWithMultipleWarehouse)
        Console.WriteLine(store.StoreNumber);

I have created a fiddle https://dotnetfiddle.net/TknnkJ it should show the overlap with all the columns like I have expected behaviour on the fiddle, any help appreciated and it is based on IsResolved Flag. — VR1256, Oct 17 '18 at 15:34

How to find overlapping in collection using LINQ ( not duplicates but to find overlaps)

3 Answers3