join table and get the record which has minimum value?

Question

I have the following collection Model: Hotel

public class Hotel {
int HotelId {get;set;}
decimal Price {get;set;}
int vendorId {get;set;}
int vendorHotelId {get;set;}
}

The records will be like this

HotelId Price VendorId VendorHotelId
1        100    1         0
2        200    2         0
3        300    1         0
4        400    2         1

If the VendorHotelId is equal to HotelId then I need to select the record which has the cheapest price in LINQ.

I want the result like this

 HotelId Price VendorId VendorHotelId
    1        100    1         0
    2        200    2         0
    3        300    1         0

Can anyone help me to solve this query?

`hotels.GroupBy(h => h.vendorHotelId).Select(g => g.OrderBy(h => h.Price).First());` will get you the cheapest hotel in each group defined by the vendorHotelId, assuming `hotels` is a collection of all hotels. — willaien, Mar 05 '19 at 15:01
as per the requirement hotelId should be equal to VendorHotelId. none of the rows in the expected result has the same hotelId and VendorHotelId — Matt.G, Mar 05 '19 at 15:09
@Matt.G Because I took the hotels with vendorhotelid equal to hotelid from the source and picked only the hotel with the cheapest price, so it is removed from the source — Harry, Mar 05 '19 at 15:11
how does removing from the source set VendorHotelId to 0 in the results? — Matt.G, Mar 05 '19 at 15:14
in the results given in the post, VendorHotelId is 0 for all 3 rows and HotelId is 1, 2 & 3. how does it satisfy the condition HotelId == VendorHotelId? — Matt.G, Mar 05 '19 at 15:18
@Matt.G If you look at the source the fourth record which has vendorhotelid as 1 which is equal to first record since it has the hotelid as 1, so both has compared and only the first record has been picked because it has the cheapest price — Harry, Mar 05 '19 at 15:21
it confused me that the expected results had 2 rows extra, which is not shown in the first set of data. — Matt.G, Mar 05 '19 at 15:25
I'm afraid, there might not be a direct way to achieve this. We need to use `Common Table Expression`. See this [answer](https://stackoverflow.com/a/11929928/9534819) — Matt.G, Mar 05 '19 at 15:26
If the VendorHotelId is equal to HotelId then I need to select the record which has the cheapest price in LINQ. *Do you mean: I need to group all records that share the same VendorHotelId and then select the record with the cheapest price @Harry — Stemado, Mar 05 '19 at 17:07
@NetMage Hotel. Where(a => a.HotelId == a.VendorHotelId) .OrderBy(a => a.Price).GroupBy(a => a.VendorHotelId).Select(b => b.FirstOrDefault()).ToList() — Harry, Mar 05 '19 at 17:08
@Stemado : Can you please verify my linq query which I posted above — Harry, Mar 05 '19 at 17:09
@Harry https://dotnetfiddle.net/xofGJz please look at dotnetfiddle and see my comments (just press the "Run" button at the top and see the output) — Stemado, Mar 05 '19 at 18:11
@Harry you should update your answer, not post comments to clarify your question. — NetMage, Mar 05 '19 at 18:47

NetMage · Answer 1 · 2019-03-05T19:06:36.713

You can use a conditional expression to group based on your condition, then get the minimum price from each group, which will be the one hotel if no matching vendorHotelId exists.

var ans = (from h in hotels
           let hasVendor = h.vendorHotelId > 0
           group h by hasVendor ? h.vendorHotelId : h.HotelId into hg
           let hmin = hg.OrderBy(h => h.Price).First()
           select new {
               HotelId = hmin.HotelId,
               Price = hmin.Price,
               vendorId = hmin.vendorId
           })
          .ToList();

Update: Since you seem to be using fluent syntax based on your comment, here is a translation:

var ans2 = hotels.Select(h => new { h, hasVendor = h.vendorHotelId > 0 })
                 .GroupBy(hv => hv.hasVendor ? hv.h.vendorHotelId : hv.h.HotelId, hv => hv.h)
                 .Select(hg => hg.OrderBy(h => h.Price).First())
                 .Select(hmin => new {
                     HotelId = hmin.HotelId,
                     Price = hmin.Price,
                     vendorId = hmin.vendorId
                 })
                 .ToList();

NB: Somewhere someone should write an article on the advantages of conditional GroupBy expressions for unusual groupings.

join table and get the record which has minimum value?

1 Answers1