Sql – How to query the range of date which one product has not been ordered? (LINQ)

linqlinq-to-sqlvb.net

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.

Best Answer

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)

Related Solutions

C# – Unable to cast object of type ‘System.Data.Linq.DataQuery`1[System.Int32]’ to type ‘System.IConvertible’

Your query:

from p in db.Product
where p.IDNumber == 200900110
select p.ProductID

does not return a single result but a list of results. In your case this will be a list containing a single product id. You should modify it to this:

(from p in db.Product
 where p.IDNumber == 200900110
 select p.ProductID).Single()

If you run your code in the debugger and you hover your mouse over the idNum variable, you'll see that it is a DataQuery instance.

Sql – Linq to Sql – Double Group By with Parent Table Relation

Here's a query that will give you a list of Users, Products, and the Count:

from op in OrderProducts
group op by new {op.Order.User, op.Product} into g
select new
{
  g.Key.User.UserName,
  g.Key.Product.ProductName,
  Count=g.Sum(op => op.ProductQty)
}

It won't give you results in the exact format you want, but it will get you most of the way there using only Linq.

Also take note that this won't pull any extra data - (Users or Products with counts of 0). This may or may not be what you're looking for.

[Edit] Here's another query to take a look at (two actually):

from p in Products
from u in Users
let i = (from op in OrderProducts
         where op.Order.User == u && op.Product == p
         select op.ProductQty)
let quant = i.Count() == 0 ? 0 : i.Sum(v=>v)
select new {p.ProductName, u.UserName, Quantity = quant}
//group new {u.UserName, Quantity = quant} by p into g
//select new {g.Key.ProductName, Users = g.AsEnumerable()}

As is, this should give you straight results (table of ProductName, UserName, Quantity). The commented out portion will instead group by Product, which (depending on how you render your results) might be what you want.

Best Answer

Related Solutions

C# – Unable to cast object of type ‘System.Data.Linq.DataQuery`1[System.Int32]’ to type ‘System.IConvertible’

Sql – Linq to Sql – Double Group By with Parent Table Relation

Related Topic