CODEWITHSUNDEEP
c#linqvisual-studio-2012entity-framework-6sql-server-ce-4
Steven Wood
Steven Wood

Reputation: 2775

Group by with return everything from one group but only the first from others

First things first - I am connecting to an SqlServerCe database using C# in visual studio 2012. I am using Entity framework 6 and Linq to perform this function. So - On to the question.

I have a table as follows

ItemGroup
(
  ID INT PRIMARY KEY,
  ItemID INT,
  GroupID INT
)

There are two tables that link to this (Items and Groups) via their IDs and the two foreign key columns in the Item Group table.

Each Item can be part of one groups and each group can have many items.

If an Item has a group ID of 0 then it is considered to not be part of a group

the result of this is that there are about 3000 groups each with 2 -> ~30 items, but there is one group that has about 4000 items.

My problem is that I have a list of items, and I want to return only one from each group unless the item is part of group 0 (ie no group). In the case of group 0 I want to return all items that match.

for example:

**Group 0**

*Item 1,
Item 2,
Item 3,*

**Group 1**

*Item 4,
Item 5*

**Group 2**

*Item 6,
Item 7,
Item 8*

**Group 3**

*Item 9*

I have list of the following items:

*Item1, Item2, Item4, Item5, Item6, Item7*

In this case I want to output all the items from my list that are in group 0 so:

*Item1, Item2*

Item 4 is part of group 1 so we want to display that, but as item 5 is part of the same group we do not want that, so the remainder of my list would be displayed as follows:

*Item4, Item6*

Giving a full list of:

*Item1, Item2, Item4, Item6*

I have tried several approaches, mainly through the use of a Union whereby I get all those records that are part of group 0 first, then do a group by first on the other records then union them together to get the final results.

However this seems tremendously inefficient and takes an age to perform - not to mention the Linq statement is very difficult to follow.

Can someone point me in a direction that I might be able to follow in order to perform this function?

Upvotes: 0

Views: 269

Answers (2)

Peter Duniho
Peter Duniho

Reputation: 70652

You want to use SelectMany(), conditionally returning all or just one of the grouped sequences depending on the group ID:

var result = (from item in data
             group item by item.Group)
             .SelectMany(group => group.Key == 0 ? group : group.Take(1));

Upvotes: 1

gmail user
gmail user

Reputation: 2783

This code will give you results for the non zero group. Similarly you can figure out the other group. I hope this helps.

var query1 = from t in context.Table1
                     where t.GroupID != 0
                     group t by t.GroupID into g
                     select new
                     {
                         ID = g.Key,
                         Groups = g.Take(1)
                     };


        Console.WriteLine("items with non 0 group");
        foreach (var item in query1)
        {

            foreach (var g in item.Groups)
            {
                Console.WriteLine(" ID " + g.ID + " " + "Group ID " + g.GroupID  + " " + " Item ID " + g.ItemID);    
            }



        }

Input data 

ID  ItemID  GroupID

 1    1      0
 2    2      0
 3    3      0
 4    4      1
 5    5      1
 6    6      2
 7    7      2
 8    8      2

Output generated

items with non 0 group

ID 4 Group ID 1  Item ID 4
ID 6 Group ID 2  Item ID 6

Upvotes: 0

Related Questions