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<ItemsInStockwhich 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]

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
"Marc Gravell" <ma**********@gmail.comwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...

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]

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).