Select fields in a matching array and parent with C# Linq

Question

I have an object that I need to get some fields from just the first one matched. The key thing is "prices" will either be sorted in ascending or descending "base" order. Find the first matching "base" and also make sure "availability.amount" qualifies, then finally return the "base", "priceTypeID" and "priceLevelID". Here is short snippet of the code that doesn't give me what I need; basically, one result gives me 3 matches while another doesn't give me the "priceLevelID".

The criteria I have below should return "base"=4000, "priceTypeID"="45082" and "priceLevelID"="2650"

public class PriceInfo
{
    public class Availability
    {
        public int amount { get; set; }
    }

    public class Price
    {
        public int @base { get; set; }
        public string priceTypeID { get; set; }
    }

    public class PriceLevel
    {
        public string priceLevelID { get; set; }
        public Availability availability { get; set; }
        public List prices { get; set; }
    }

    public class ZonePrice
    {
        public List priceLevels { get; set; }
    }

    public class OfferPrice
    {
        public List zonePrices { get; set; }
    }

    public class RootObject
    {
        public List offerPrices { get; set; }
    }
}

class Program
{
    static void Main(string[] args)
    {
        var json = @"{
            ""offerPrices"": [
                {
                    ""zonePrices"": [
                        {
                            ""priceLevels"": [
                                {
                                    ""priceLevelID"": ""1653"",
                                    ""availability"": {
                                        ""amount"": 296
                                    },
                                    ""prices"": [
                                        {
                                            ""base"": 2000,
                                            ""priceTypeID"": ""45082""
                                        }
                                    ]
                                },
                                {
                                    ""priceLevelID"": ""1029"",
                                    ""availability"": {
                                        ""amount"": 300
                                    },
                                    ""prices"": [
                                        {
                                            ""base"": 3000,
                                            ""priceTypeID"": ""45082""
                                        }
                                    ]
                                },
                                {
                                    ""priceLevelID"": ""2650"",
                                    ""availability"": {
                                        ""amount"": 400
                                    },
                                    ""prices"": [
                                        {
                                            ""base"": 4000,
                                            ""priceTypeID"": ""45082""
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            ]
        }";

        var obj = JsonConvert.DeserializeObject(json);

        var queryResult1 = obj.offerPrices[0].zonePrices[0].priceLevels.Where(w => w.availability.amount >= 2 &&
                                w.prices.OrderByDescending(o => o.@base).Any(w2 => w2.@base > 1000 && w2.@base < 300000)).FirstOrDefault();

        var queryResult2 = obj.offerPrices[0].zonePrices[0].priceLevels.Select(o => new { o.prices, o.priceLevelID, o.availability.amount }).
                                Select(o2 => o2.prices.OrderByDescending(o3 => o3).Where(w => w.@base > 1000 && w.@base < 300000).
                                Select(s => new { o2.priceLevelID, s.priceTypeID, s.@base, o2.amount }).First()).
                                Where(w2 => w2.amount > 2).First();

user_141172 · Answer

It's not clear to me what your selection criteria are, but here's how I'd think about this query.

First, select the leaf nodes since you do your filtering based on them. Here, I make an assumption about ordering based on your example queries:

var ordered = obj.offerPrices
  .SelectMany(o => o.zonePrices)
  .SelectMany(z => z.priceLevels)
  .Select(l => new 
  {
    comparisonPrice = l.prices.MaxBy(p => p.@base).First(), // MaxBy from morelinq. Requires First() because there can be multiple equal base prices
    l.priceLevelID,
    l.availability
  })
  .OrderByDescending(l => l.comparisonPrice.@base);

Next, filter to results in the desired price range:

var minBase = 1000;
var maxBase = 300000;

var meetsPriceRange = ordered
    .Where(o => o.comparisonPrice.@base > minBase && 
                o.comparisonPrice.@base < maxBase);

Finally, ensure the required minimum availability and pick our winner:

var minAvailable = 2;
var meetsMinimumAmount = ordered
  .Where(o => o.availability.amount >= minAvailable)
  .Select(o => new {
    o.comparisonPrice.@base,
    o.comparisonPrice.priceTypeID,
    o.priceLevelID
  })
  .First();

Output:

base: 4000
priceTypeId: 45082
priceLevelID: 2650

user_11392290 · Answer

try this

var basePriceItem = obj.offerPrices.SelectMany(o => o.zonePrices)
.SelectMany(z => z.priceLevels).SelectMany(s => s.prices).OrderByDescending(o => o.@base)
.FirstOrDefault(b => b.@base > 1000 && b.@base < 300000);

var item = obj.offerPrices.SelectMany(o => o.zonePrices)
.SelectMany(z => z.priceLevels)
.FirstOrDefault(o => o.prices.Any(p => p.priceTypeID == basePriceItem.priceTypeID && p.@base == basePriceItem.@base));

output

{
  "priceLevelID": "2650",
  "availability": {
    "amount": 400
  },
  "prices": [
    {
      "base": 4000,
      "priceTypeID": "45082"
    }
  ]
}

user_259769 · Answer

It seems to me that you're asking for this:

    var query =
        from x in obj.offerPrices[0].zonePrices[0].priceLevels
        where x.availability.amount > 2
        from y in x.prices
        where y.@base > 1000
        where y.@base < 300000
        orderby y.@base descending
        select new { x.priceLevelID, y.priceTypeID, y.@base, x.availability.amount };
        
    var result = query.FirstOrDefault();

That gives me:

    var query =
        obj
            .offerPrices[0]
            .zonePrices[0]
            .priceLevels
            .Where(x => x.availability.amount > 2)
            .SelectMany(x =>
                x
                    .prices
                    .Where(y => y.@base > 1000)
                    .Where(y => y.@base < 300000)
                    .OrderByDescending(y => y.@base),
                (x, y) => new { x.priceLevelID, y.priceTypeID, y.@base, x.availability.amount });

Select fields in a matching array and parent with C# Linq

3 Answers