SQL to LINQ translation help!

[Laeg]

I have a table of CartItems, with the ProductID bought and then an Extra field which is true or false. I want to see a grouping of a count of each ProductID but within that count I want to know the count of ProductIDs with extras.

create table test(CartItemID int, ProductID int, Extra int);

insert into test (CartItemID, ProductID, Extra) values (100, 1, 1);
insert into test (CartItemID, ProductID, Extra) values (200, 2, 0);
insert into test (CartItemID, ProductID, Extra) values (300, 2, 0);
insert into test (CartItemID, ProductID, Extra) values (400, 1, 1);

select ProductID, count(ProductID) as Total, COUNT(CASE WHEN Extra = 1
                  THEN 'HasExtra'
                  ELSE NULL END) AS ExtraCount
from CartItems
group by ProductID;

produces ...

ProductID | Total | ExtraCount
1 2 2 <-- True As 2 instances of a ProductID 1 purchase also had Extra (CartItemID 100, 400)
2 2 0 <-- False As 0 instances of a ProductID 2 purchase had Extra

How can I convert the below to Linq, I've made a few attempts at it with no joy. 1 aggregate function count I can do but not two.

select ProductID, count(ProductID) as Total, COUNT(CASE WHEN Extra = 1
                  THEN 'HasExtra'
                  ELSE NULL END) AS ExtraCount
from CartItems
group by ProductID;

Thanks for replies

 

[AlexCuse]

I think you need something like this:

var set = new List<Test>()
{
	new Test(){CartItemId = 1, ProductId = 1, Extra = false},
	new Test(){CartItemId = 2, ProductId = 1, Extra = true},
	new Test(){CartItemId = 3, ProductId = 2, Extra = false},
	new Test(){CartItemId = 4, ProductId = 2, Extra = false}
};

var result = from t in set
		group t by t.ProductId into g
		select new {Id = g.Key, Total = g.Count(), ExtraCount = g.Count(p => p.Extra)};

assuming a class that looks like this:

class Test
{
	public Int32 CartItemId { get; set; }
	public Int32 ProductId { get; set; }
	public bool Extra { get; set; }
}

Note that you take the total count for "Total", then the count where a condition is met (in this case, Extra = true) for "HasExtra".

Hope it helps,

Alex

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page