CODEWITHSUNDEEP
c#listlinq
Jeremy Hodge
Jeremy Hodge

Reputation: 662

Perform a lookup between C# Lists and return matched where value is minimum

I have two lists:

var qtys = new List<InventoryQuantity>()
{
    new InventoryQuantity() { WarehouseId = 1, QuantityInWarehouse = 0 },
    new InventoryQuantity() { WarehouseId = 2, QuantityInWarehouse = 452 },
    new InventoryQuantity() { WarehouseId = 3, QuantityInWarehouse = 184 },
    new InventoryQuantity() { WarehouseId = 4, QuantityInWarehouse = 328 },
    new InventoryQuantity() { WarehouseId = 5, QuantityInWarehouse = 0 },
};

var times = new List<WarehouseTransitTime>()
{
    new WarehouseTransitTime() { WarehouseId = 1, TransitDays = 1 },
    new WarehouseTransitTime() { WarehouseId = 2, TransitDays = 4 },
    new WarehouseTransitTime() { WarehouseId = 3, TransitDays = 2 },
    new WarehouseTransitTime() { WarehouseId = 4, TransitDays = 3 },
    new WarehouseTransitTime() { WarehouseId = 5, TransitDays = 5 },
};

class InventoryQuantity
{
    public int WarehouseId { get; set; }
    public int QuantityInWarehouse { get; set; }
}

class WarehouseTransitTime
{
    public int WarehouseId { get; set; }
    public int TransitDays { get; set; }
}

I need to return the WarehouseId from qtys where the Quantity > 0 and the WarehouseId equals the minimum transit days WarehouseId in times.

I know I can do something like below but seems clunky and there must be an elegant solution.

    public int NearestWarehouse()
    {
        var withQty = qtys.Where(i => i.QuantityInWarehouse > 0);
        var orderedTransit = times.OrderBy(tt => tt.TransitDays).ToList();
        //loop and compare
    }

Example data:

qtys

WarehouseId | Quantity
1           | 0
2           | 452
3           | 184
4           | 328
5           | 0

times

WarehouseId | TransitTime
1           | 1
2           | 4
3           | 2
4           | 3
5           | 5

Expected output would be 3, because warehouse 3 has inventory and the shortest transit time (2)

Upvotes: 0

Views: 116

Answers (4)

Milan Raval
Milan Raval

Reputation: 1882

You can do it like this..

   var withQty = (from q in qtys
                       join t in times on q.WarehouseId equals t.WarehouseId
                       where q.QuantityInWarehouse > 0
                       select new { q.WarehouseId, t.TransitDays })
                       .OrderBy(item => item.TransitDays).FirstOrDefault();


   return withQty?.WarehouseId ?? 0;

Upvotes: 1

Enigmativity
Enigmativity

Reputation: 117057

It seems to me that the cleanest and simplest query is this:

var query =
    from q in qtys
    where q.QuantityInWarehouse > 0
    join t in times on q.WarehouseId equals t.WarehouseId
    orderby t.TransitDays
    select q.WarehouseId;

var warehouseId = query.FirstOrDefault();

This gives me 3.

Upvotes: 2

Kenneth K.
Kenneth K.

Reputation: 3039

What you want is a group join:

Functional Syntax

var query1 = qtys.Where(q => q.QuantityInWarehouse > 0)
                 .GroupJoin(times, q => q.WarehouseId, t => t.WarehouseId, (q, t) => new { q.WarehouseId, TransitDays = t.DefaultIfEmpty().Min(grp => grp?.TransitDays) })
                 .OrderBy(g => g.TransitDays)
                 .FirstOrDefault();

Query Syntax

var query2 = from q in qtys
             join t in times on q.WarehouseId equals t.WarehouseId into grp
             where q.QuantityInWarehouse > 0
             select new
             {
                 q.WarehouseId,
                 TransitDays = grp.DefaultIfEmpty().Min(g => g?.TransitDays)
             };

var result = query2.OrderBy(g => g.TransitDays)
                   .FirstOrDefault();

A group join will join two lists together on their corresponding keys--similar to a database join--and the associated values to those keys will be grouped into an enumerable. From that enumerable, you can derive the minimum value that you care about, TransitDays in this case.

There is no equivalent to "first or default" in query syntax. The easiest approach is just to apply the same OrderBy and FirstOrDefault against the query variable, as demonstrated above.

Upvotes: 2

Alb
Alb

Reputation: 529

Well you mention an AND relation between the two, right?

I was thinking of databases with a forignkey... but Linq prety much does it if your lists aren't to big:

keys = qtys.Where(i => i.QuantityInWarehouse > 0).Select(i => i.WarehouseId).ToList();
// get the smallest not a list
var result = times.Where(tt => keys.Contains(tt.wharehouseId)).orderby(tt => tt.Transitdays).FirstOrDefault();

Otherwise you could have Dictionary with ID as key...

Upvotes: 1

Related Questions