How can I query the range of date which one product has not been ordered? (LINQ)

Question

I have three tables,

• Products(ProductID, ProductName)
• Orders(OrderID, OrderDate)
• OrderDetails(ProductID, OrderID) Junction table between Products and Orders.

For example, there are Three ProductID in Products table A01, A02, A03

Four OrderID in Orders table 1. 01/01/2009, 2. 03/01/2009, 3. 05/01/2009, 4. 07/01/2009.

OrderDetails data as below:

A01 -- 01/01/2009

A02 -- 01/01/2009

A01 -- 03/01/2009

A02 -- 03/01/2009

A01 -- 05/01/2009

A03 -- 05/01/2009

A01 -- 07/01/2009

then I want to display the date that A03 is not ordered from range 02/01/2009 till 08/01/2009 which could have the result like this.

DateMissingOrder for Product A03 in range of date 02/01/2009 to 08/01/2009:

03/01/2009

07/01/2009

How can I do this in LINQ to SQL Visual Basic? May someone help me with some clue please.. Thank in advance.

Answer 1

Try something like this:

Select all the dates in the range

Dim DatesInRange = (From o in dc.Orders _
                   Where (o.OrderDate >= StartDate and _
                          o.OrderDate <= EndDate)
                   Select o.OrderDate).Distinct

Select all the dates in the range for A03 orders

Dim Dates = (From o in dc.OrdersDetails _
            Where (o.Order.OrderDate >= StartDate and _
                   o.Order.OrderDate <= EndDate) and _
                   o.Order.Product.ProductName = "A03" _
            Select o.Order.OrderDate).Distinct

Return the dates missing from the second list

Dim MissingDates = DatesInRange.Except(Dates)

Answer 2

THANK ALL OF YOU! I would recommend this is the one of the best programming question and answer website. God bless all your members!