Get all objects that contains a collection of sub objects with condition on this sub object

Question

I have a database with no contraint and no foreign key defined between table. I use Linqpad but in my Linqpad I have no navigation properties on this database.

I would like to do this

Order_Sub_Items.GroupBy(x => x.Order_Item_Id)
.Where(y => y.Items.Sub_Item_Id != y.Items.First().Sub_Item_Id)

So, in English: I would like to get all my Order_Sub_Items where for a same Order_Item_Id I have differents Sub_Item_Id.

So the idea is to group by Order_Sub_Items then for each items inside my group I compare the Sub_Item_Id to the Sub_Item_Id of my first element.

The query is not working because I cannot access Items. Why ? When I run only the first part:

Order_Sub_Items.GroupBy(x => x.Order_Item_Id)

in Linqpad I have no problem to see the result on 2 columns. Key and Items.

I have a database with no contraint and no foreign key defined between table. I use Linqpad. The database is not a SQL server. — Bastien Vandamme, May 16 '17 at 13:51
What is the type of `Items` property in your model? is a collection or is an scalar property? — ocuenca, May 16 '17 at 13:55
After you grouped by - there is no `Items` property in `Where`, `y` is now `IGrouping`, which represents a group. — Evk, May 16 '17 at 14:02
You should probably use `Items.Select(...)`. I reckon it's a collection of some sort — nocodename, May 16 '17 at 14:02
Group has `Key` property (by which you grouped) and is itself IEnumerable, so you can do things like `y.First()` or `y.Count()` with it. — Evk, May 16 '17 at 14:19
So I guess this is something specific to Linqpad. in Linqpad I can navigate into my items. So when grouping I can access each element of the group. — Bastien Vandamme, May 16 '17 at 14:28
_"The query is not working because I cannot access Items"_ -- the "Items" in LinqPad is not the same as the "Items" in your object. It's just the group for the given key (note the result of `ToString()` displayed, a type name: `Grouping\`2`). If you want to filter further on the group, you need to construct a query to do that (I'm not convinced the condition you're using is ideal, but given that condition, use of `Any()` as in your posted answer seems as reasonable as anything). See marked duplicates for similar explanations. — Peter Duniho, May 16 '17 at 16:26

score 0 · Accepted Answer · answered May 16 '17 at 15:02

I think I found my solution

Order_Sub_Items
.GroupBy(x => x.Order_Item_Id)
.ToList()
.Where(x => x.Any(y => y.Sub_Item_Id != x.First().Sub_Item_Id)

or from picture

Order_Sub_Items.Take(100)
.GroupBy(x => x.Order_Item_Id.Value)
.ToList()
.Where(x => x.Any(y => y.Sub_Item_Id.Substring(0, 11) != x.First().Sub_Item_Id.Substring(0, 11)))

321X · Answer 2 · 2017-05-16T15:12:06.620

When you do a GroupBy operation with Linq you will get the following result:

Type: System.Collections.Generic.IEnumerable<IGrouping<TKey, TElement>> An IEnumerable<IGrouping<TKey, TElement>> in C# or IEnumerable(Of IGrouping(Of TKey, TElement)) in Visual Basic where each IGrouping<TKey, TElement> object contains a collection of objects of type TElement and a key.

https://msdn.microsoft.com/en-us/library/bb534304(v=vs.110).aspx

So, you are looking at some Linqpad feature I guess, to at least display it's containing N items of type Grouping'2 (where the 2 is referring to number of parameters)

The result of IGrouping<TKey, TElement> will provide you an collection where you can do your work on: Enumerable operations and the Key contains the object you grouped on.

---edit---

I see in your own answer you used Any() - That's one of the many Enumerable operations.

Get all objects that contains a collection of sub objects with condition on this sub object

2 Answers2