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

Re: Group By In LINQ

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]

Re: Group By In LINQ

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