Summary query in DLinq

[Andrus]

DLinq objects:

class Item {
string ItemCode {get;set;} // primary key
}

class Stock { // primary key is (ItemCode,StockId)
string ItemCode {get;set;}
string Quantity { get; set; }
string StockId { get; set; }
}

class ItemsInStock {
string ItemCode {get;set;} // primary key
string SumQuantity { get; set; }
}

How to create IQueryable<ItemsInStock> which returns item code and sum of
items in all stocks even when there is no items in stock.

In sql I can write

SELECT
Item.ItemCode,
COALESCE(sum(Stock.Quantity),0) as SumQuantity
FROM Item
LEFT JOIN Stock ON Item.ItemCode= Stock.ItemCode
GROUP BY 1
INTO CURSOR ItemsINStock

How to convert this query to IQuearyable<ItemsInStock> ?

Andrus.

 

[Marc Gravell]

IIRC, DefaultIfEmpty...

http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx

You might also just be able to do it by joining appropriately; the
following via LINQ-to-SQL (on Northwind) works as a sub-query, but the
result is the same: we get all products, and the count each (even if
zero):

var qry = from product in ctx.Products
join orderline in ctx.Order_Details
on product.ProductID equals
orderline.ProductID
into joined
select new
{
Product = product.ProductName,
Count = joined.Count()
};

TSQL:
SELECT [t0].[ProductName] AS [Product], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t1]
WHERE [t0].[ProductID] = [t1].[ProductID]
) AS [Count]
FROM [dbo].[Products] AS [t0]

Marc

 

[Andrus]

You might also just be able to do it by joining appropriately; the

following via LINQ-to-SQL (on Northwind) works as a sub-query, but the
result is the same: we get all products, and the count each (even if
zero):

var qry = from product in ctx.Products
join orderline in ctx.Order_Details
on product.ProductID equals
orderline.ProductID
into joined
select new
{
Product = product.ProductName,
Count = joined.Count()
};

TSQL:
SELECT [t0].[ProductName] AS [Product], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t1]
WHERE [t0].[ProductID] = [t1].[ProductID]
) AS [Count]
FROM [dbo].[Products] AS [t0]

This query executes separate query for every product.
For 100 products it executes 101 sql queries in server.
So this is probably terribly slow.

Andrus.

 

[Marc Gravell]

No; it executes 1 TSQL statement; I included this single statement (via
LINQ-to-SQL) in my reply. If DbLinq does 101 queries, then that is an
issue for DbLinq.

Marc

 

[Michel Walsh]

It is one SQL statement, but it MAY imply that the table implied in the
sub-query will be touch once for each and every row of the table of the main
clause.


There is a LOGICAL difference between:


--------------------
SELECT a.f1, SUM(b.f2)
FROM a INNER JOIN b ON a.f1=b.f1
-------------------

and

--------------------
SELECT a.f1 (SELECT SUM(b.f2)
FROM b
WHERE b.f1=a.f1)
FROM a
-------------------

mainly if and a.f1 and b.f1 have common dup. Example right here:


a.f1 a.f4
----------------
a hello
a world
b !!!

b.f1 b.f2
----------------
a 10
a 20
b 30


The query you produced (subquery making a sum) return

a 30
b 30


while the sum over the join returns:

a 60
b 30



Surprised? well, since the join produced:

a.f1 a.f4 b.f1 b.f2
a hello a 10
a hello a 20
a world a 10
a world a 20
b !!! b 30


so the sum is as announced.


So, having established that the two queries are not the same, they don't
have the same query plan. Your query, which is probably what is wanted, but
may have to "touch" the table B for EACH and EVERY row of table A.

But.

It may happen that the sum over the join is still ok, given that, somehow,
we are sure there is not common dup on both tables (say, f1 is a primary key
for table A, as example), then, the sum over the join is ok... and MAY be
optimized radically differently (meaning faster) than the sub-query with a
sum, such, as example, by running concurrently over the indexes.


So, to conclude, while your query works, it is not logically equivalent as a
query of a sum over a join. Since the two queries are not equivalent, MS SQL
Server would NOT make them equivalent, so, won't come with the same plan of
execution. (And yes, I lied, since MS SQL Server 2005 MAY translate your
sub-query as a join in some circumstances).



Hoping it may help, ... somehow

Vanderghast, Access MVP

 

[Michel Walsh]

I missed the GROUP BY clause in the first query. Should be:


SELECT a.f1, SUM(b.f2)
FROM a INNER JOIN b ON a.f1=b.f1
GROUP BY a.f1

 

[Michel Walsh]

The second query will output

a 30
a 30
b 30


(was missing one row in the result).