Group By In LINQ

[Chris]

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

Tables:
OrderHeader
OrderLineItem
OrderSubLineItem

The result set I need is:
OrderHeader.Description, Count(OrderSubLineItem) as CountAllItems

What I wanted to do is
var Result = from Item in DB.OrderHeader
where Item.OrderHeadID = ID
select new {Item.Description,
Item.OrderLineItem.OrderSubLineItem.Count}

I can't drill down from OrderLineItem to OrderSubLineItem 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]]

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

Tables:
OrderHeader
OrderLineItem
OrderSubLineItem

The result set I need is:
OrderHeader.Description, Count(OrderSubLineItem) as CountAllItems

What I wanted to do is
var Result = from Item in DB.OrderHeader
where Item.OrderHeadID = ID
select new {Item.Description,
Item.OrderLineItem.OrderSubLineItem.Count}

I can't drill down from OrderLineItem to OrderSubLineItem 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 OrderSubLineItem.

You could try:
var q = from osli in DB.OrderSubLineItem
group osli by osli.OrderLineItem.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]

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

        You could try:
var q = from osli in DB.OrderSubLineItem
        group osli by osli.OrderLineItem.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.



        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