[Solved]-Group By that chains back and forth across two fields-LINQ,C#

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

yields ids of:

1|2|6|
3|7|  
4|