none
How can I do this with LINQ? RRS feed

  • Question

  • I have two tables one called product and the other called product price history. How can I list all products with their lowest price?  Note, Widget3 has no price and should be included in the output.

    ProductID  Product
    1              Widget1
    2              Widget2
    3              Widget3

    PriceHistoryID  ProductID Price Date
    1                     1             100    10/10/2009
    2                     1             200    9/10/2008
    3                     1             300    8/1/2006
    4                     2             10      8/9/2007
    5                     2             20      8/10/2007

    This is the output that I want:

    Product   LowestPrice  Date
    Widget1  100              10/10/2009
    Widget2  10                8/9/2007
    Widget3  NULL            NULL

    Tuesday, October 27, 2009 4:06 AM

Answers

  • from p in dc.Products
    from ph in
      (
        from ph in dc.PriceHistories
        where ph.ProductID == p.ProductID
        orderby ph.Price
        select new { Price = (decimal?)ph.Price, Date = (DateTime?)ph.Date }
      ).DefaultIfEmpty().Take(1)
    orderby p.Product
    select new { p.Product, ph.Price, ph.Date }

    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Marked as answer by terghost Tuesday, October 27, 2009 5:08 AM
    Tuesday, October 27, 2009 4:20 AM
    Answerer

All replies

  • from p in dc.Products
    from ph in
      (
        from ph in dc.PriceHistories
        where ph.ProductID == p.ProductID
        orderby ph.Price
        select new { Price = (decimal?)ph.Price, Date = (DateTime?)ph.Date }
      ).DefaultIfEmpty().Take(1)
    orderby p.Product
    select new { p.Product, ph.Price, ph.Date }

    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Marked as answer by terghost Tuesday, October 27, 2009 5:08 AM
    Tuesday, October 27, 2009 4:20 AM
    Answerer
  • KristoferA,  it works great!  Thanks alot!
    Tuesday, October 27, 2009 5:08 AM
  • Ok. Note that the left join (DefaultIfEmpty) and top1 (Take(1)) hoola-hoops is only needed if you're getting more than column from the price history.

    If you only needed product details and the lowest price (without the date for the lowest price) then it could instead be written as the more brief:

    from p in dc.Products
    select new { p.Product, LowestPrice = (decimal?)p.PriceHistories.Min(lp => lp.Price) }

    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Tuesday, October 27, 2009 7:08 AM
    Answerer