Group By In LINQ

[Chris]

Trying to get my head around linq. Here is what I have:

Tables:
OrderHeader
OrderLineItem
OrderSubLineIte m

The result set I need is:
OrderHeader.Des cription, Count(OrderSubL ineItem) as CountAllItems

What I wanted to do is
var Result = from Item in DB.OrderHeader
where Item.OrderHeadI D = ID
select new {Item.Descripti on,
Item.OrderLineI tem.OrderSubLin eItem.Count}

I can't drill down from OrderLineItem to OrderSubLineIte m though.

I am guaranteed that all LineItems have SubLineItems.

Also, I would like to return a dictonary of this and seem to have
having problems doing the ToDicontary when I do a select new at the
bottom of my LINQ.

Thanks
Chris

[Frans Bouma [C# MVP]]

Chris wrote:

Trying to get my head around linq. Here is what I have:

Tables:
OrderHeader
OrderLineItem
OrderSubLineIte m

The result set I need is:
OrderHeader.Des cription, Count(OrderSubL ineItem) as CountAllItems

What I wanted to do is
var Result = from Item in DB.OrderHeader
where Item.OrderHeadI D = ID
select new {Item.Descripti on,
Item.OrderLineI tem.OrderSubLin eItem.Count}

I can't drill down from OrderLineItem to OrderSubLineIte m though.

If you need a given resultset, it's not about what you want to do, it's
about what you have to do. You can't 'drill down' to them as that
requires an m:1/1:1 relationship, and OrderLineItem has a 1:n
relationship with OrderSubLineIte m.

You could try:
var q = from osli in DB.OrderSubLine Item
group osli by osli.OrderLineI tem.OrderHeader .Description into g
select new { g.Key, CountAllItems = g.Count() };

Now you can drill 'up' as all relationships are m:1.

You can also create a subquery inside the projection, which is
effectively a scalar, though that leads to less efficient SQL.

I am guaranteed that all LineItems have SubLineItems.

Also, I would like to return a dictonary of this and seem to have
having problems doing the ToDicontary when I do a select new at the
bottom of my LINQ.

the select new creates an anonymous type. The ToDictionary() method
will return a dictionary with values of that anonymous type. This means
that you should do:

var dict = q.ToDictionary( v=>v.Key);

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Chris]

On Oct 14, 3:02*am, "Frans Bouma [C# MVP]"
<perseus.usenet NOS...@xs4all.n lwrote:

Chris wrote:

Trying to get my head around linq. *Here is what I have:

Tables:
OrderHeader
OrderLineItem
OrderSubLineIte m

The result set I need is:
OrderHeader.Des cription, Count(OrderSubL ineItem) as CountAllItems

What I wanted to do is
var Result = from Item in DB.OrderHeader
* * * * * * * * * where Item.OrderHeadI D = ID
* * * * * * * * * select new {Item.Descripti on,
Item.OrderLineI tem.OrderSubLin eItem.Count}

I can't drill down from OrderLineItem to OrderSubLineIte m though.

* * * * If you need a given resultset, it's not about what you want to do, it's
about what you have to do. You can't 'drill down' to them as that
requires an m:1/1:1 relationship, and OrderLineItem has a 1:n
relationship with OrderSubLineIte m.

* * * * You could try:
var q = from osli in DB.OrderSubLine Item
* * * * group osli by osli.OrderLineI tem.OrderHeader .Description into g
* * * * select new { g.Key, CountAllItems = g.Count() };

Now you can drill 'up' as all relationships are m:1.

You can also create a subquery inside the projection, which is
effectively a scalar, though that leads to less efficient SQL.

I am guaranteed that all LineItems have SubLineItems.

Also, I would like to return a dictonary of this and seem to have
having problems doing the ToDicontary when I do a select new at the
bottom of my LINQ.

* * * * the select new creates an anonymous type. The ToDictionary() method
will return a dictionary with values of that anonymous type. This means
that you should do:

var dict = q.ToDictionary( v=>v.Key);

* * * * * * * * FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website:http://www.llblgen.com
My .NET blog:http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

Thanks for the information. Once I realized I needed to start at the
bottom and work up, the rest came together.

Chris