Ever since I have been using Linq2SQL, I am always impressed by the speed and the functionality of it. However; using SQL for 10+ years I always find my self challenged by converting SQL to
LINQ2SQL. The simple select statements and left, right, inner joins are pretty straight forward but what about something more complicated. For example.
Suppose I have two tables (Customers and Sales). I want to know how many orders there has been by each customer. For example:
[code language=”sql”]
CREATE TABLE Customers
(
id int primary key,
Customer varchar(50)
);
INSERT INTO Customers
(id,Customer)
VALUES
(1001,’Fred Flinstone’),
(1002,’Barney Rubble’),
(1003,’Pebbles Flinstone’),
(1004,’Betty Rubble’),
(1005,’Bamm-Bamm Rubble’),
(1006,’Mr. Slate’),
(1007,’Sam Slagheap’);
CREATE TABLE SALES
(custid int,
ordernum int,
amount decimal(9,2));
Insert Into Sales
(Custid, ordernum, amount)
VALUES
(1001,1234,5.25),
(1001,1317,13.75),
(1001,1318,9.76),
(1002,1235,3.56),
(1002,1267,2.45),
(1002,1269,104.56),
(1002,1270,34.56),
(1003,999,17.22),
(1003,1400,18.06),
(1005,1405,.45),
(1006,234,17.45),
(1007,239,15.45),
(1007,1304,18.00),
(1007,1305,5.13),
(1007,1411,4.89)
[/code]
Result I want is
| 1001 | Fred Flinstone | 3 |
| 1002 | Barney Rubble | 4 |
| 1003 | Pebbles Flinstone | 2 |
| 1004 | Betty Rubble | 0 |
| 1005 | Bamm-Bamm Rubble | 1 |
| 1006 | Mr. Slate | 1 |
| 1007 | Sam Slagheap | 4 |
[SQL CODE]
SELECT cust.*, ss.OrderCount FROM Customers cust
LEFT JOIN (
SELECT count(custid) AS OrderCount, Custid FROM Sales GROUP BY Custid) ss ON cust.id = ss.Custid
[LINQ2SQL]
From cs In Customers
Group Join rr In (
(From Sales In Sales
Group Sales By Sales.CustID Into g = Group
Select
CustID,
OrderCount = CType(g.Count(),Int64?))
) On New With { .CustID = cs.CustID } Equals New With { .CustID = CInt(rr.CustID) } Into rr_join = Group
From rr In rr_join.DefaultIfEmpty()
Select
CustID = CType(cs.CustID,Int32?),
cs.Name,
Column1 = If(CType(rr.OrderCount,Int64?) is Nothing,0,rr.OrderCount)
Looking for quality web hosting? Look no further than Arvixe Web Hosting!
