Okay - contrary to what I thought, you can do this with a single recursive CTE.
Not that it's pretty...
Please note that all testing was done on DB2, which does not have a way to return arrays in-row. I don't know if SQL Server can (so you may have to parse results). I do NOT recommend attempting to do this with Linq - if anything, this should be run on the database directly.
WITH Mappings(beginId, storedId, groupId, storedPath, groupPath) as ( SELECT a.StoredMessageId, a.StoredMessageId, a.MessageGroupId, a.StoredMessageId + "|", a.MessageGroupId + "|" FROM MessageGrouping as a WHERE NOT EXISTS (SELECT 1 FROM MessageGrouping as b WHERE b.StoredMessageId < a.StoredMessageId AND b.MessageGroupId = a.MessageGroupId) AND NOT EXISTS (SELECT 1 FROM MessageGrouping as b WHERE b.MessageGroupId < a.MessageGroupId AND b.StoredMessageId = a.StoredMessageId) UNION ALL SELECT a.beginId, a.storedId, b.MessageGroupId, a.storedPath, a.groupPath + b.MessageGroupId + "|" FROM Mappings as a JOIN MessageGrouping as b ON b.StoredMessageId = a.storedId AND b.MessageGroupId <> a.groupId AND a.groupPath NOT LIKE "%" + b.MessageGroupId + "%" UNION ALL SELECT a.beginId, a.StoredMessageId, a.groupId, a.storedPath + b.StoredMessageId + "|", a.groupPath FROM Mappings as a JOIN MessageGrouping as b ON b.MessageGroupId = a.groupId AND b.StoredMessageId <> a.storedId AND a.storedPath NOT LIKE "%" + b.StoredMessageId + "%"), Result_Rows (ids, num) as ( SELECT storedPath, ROW_NUMBER() OVER(PARTITION BY beginId ORDER BY LENGTH(storedPath) DESC) FROM Mappings) SELECT ids FROM Result_Rows WHERE num = 1
1|2|6| 3|7| 4|
- Group By that chains back and forth across two fields
- Linq group by multiple fields across different tables
- Proper way to query DataContext using Group by that has fields with Nulls
- How do I group by two fields and return the original objects that match?
- How to include fields that are not the Key or aggregate in Linq Group by results?
- LINQ Group By Multiple fields -Syntax help
- How are people unit testing code that uses Linq to SQL
- Select multiple fields group by and sum
- Is there an IEnumerable implementation that only iterates over it's source (e.g. LINQ) once?
- Linq Group on Multiple Fields - VB.NET, Anonymous, Key
- Is there a good source that gives an overview of linq optimizations?
- Best way to handle redundant code that has repeated logic?
- EF Code First: Methods that can translate to SQL
- LINQ Source Code Available
- How can I switch that code to use LINQ
- How does this linq code that splits a sequence work?
- Using Linq to group a list of objects that contains primitives data into a new grouped list of objects
- How do I group on one of two possible fields using LINQ?
- C# Code Contracts -- How to ensure that a collection of items contains items with unique properties?
- .NET 4 Code Contracts: "requires unproven: source != null"
- Is there a way to speed up this code that finds data changes in two XML files?
- linq - how do you do a query for items in one query source that are not in another one?
- Parallel Linq - return first result that comes back
- Linq to XML pulling back an attribute that exists on a different level
- How to group a List<T> that has a property of List<string> by that nested List?
- How to code the partial extensions that Linq to SQL autogenerates?
- LINQ Group by performing join across multiple tables/classes
- How can I modify this C# code so that Visual Studio recognizes that I'm not an idiot?
- Group to key-value pair where value is a list of elements that share the same key
- How to group by multiple fields and get the count of another field
- Linq two select statements, second one uses first ones result,
- Creating a feed of events ordered by Date
- Create a reusable "meta-predicate" function accepting lambda operand parameters
- C# Linq Subset Data
- Entity MVC adding a new row and grabbing the id used
- Analyzing a Linq expression
- Python: LINQ capable class supporting simultaneously laziness and fluent design
- Is this possible to change or set the Padding Color of a Windows Form?
- How to get an specific node in xml with namespaces?
- C# - Search in List with array in WHERE clause
- Unable to join a table and view - type inference failed in the call to 'join'
- Assigning Alternate Color to a List Elements
- How to convert to List<ExpandoObject> properly
- Get the next to last element from a string split using linq
- Left outer join linq query object reference not set to an instance of an object
- apply a collection of courses to a collection of students
- How to add to linq2sql entity set for master detail if I can't explicitly define the detail?
- Passing DataContext into Action()
- Group and count list of list items
- Linq continuous date takewhile