Converting SQL to LINQ with left join and group by

Question

I have done this query by SQL, and want to translate to LINQ

SELECT
ENT_DESC,
CAT_DESC,
PART_NUM, 
PART_DESC,  
GROUP_CONCAT(PART_OPTIONS.CODE, CHAR(13)) AS CODE,
PART_OPTIONS.PROMPT AS PROMPT,
PART_OPTIONS.DESCRIPTION AS DESCRIPTION,
PART_OPTIONS.PRICE AS PRICE,
ATTRIBUTE_DATA.NOTE,
ATTRIBUTE_DATA.DESCRIPTION AS ATTR_DESC 
FROM ITEM_DATA 
LEFT JOIN PART_OPTIONS ON  ITEM_DATA.ID = PART_OPTIONS.PART_ID
LEFT JOIN ATTRIBUTE_DATA ON  ITEM_DATA.ID = ATTRIBUTE_DATA.PART_ID
GROUP BY PART_OPTIONS.PART_ID

I know how to do this without GROUP_CONCAT,

from c in ITEM_DATA
join a in ATTRIBUTE_DATA on c.ID equals a.PART_ID into at
from a in at.DefaultIfEmpty()
join p in PART_OPTIONS on c.ID equals p.PART_ID into pa
from p in pa.DefaultIfEmpty()
select new { 
        ENT_DESC = c.ENT_DESC,
        CAT_DESC = c.CAT_DESC,
        PART_NUM = c.PART_NUM,
        PART_DESC = c.PART_DESC,
        CODE = p.CODE,
        PROMPT = p.PROMPT,
        DESCRIPTION = p.DESCRIPTION,
        PRICE = p.PRICE,
        NOTE = a.NOTE,
        ATTR_DESC = a.DESCRIPTION,
        }

and also I have done GROUP_CONCAT separately

from c in ITEM_DATA
join a in ATTRIBUTE_DATA on c.ID equals a.PART_ID into at
from a in at.DefaultIfEmpty()
join p in PART_OPTIONS on c.ID equals p.PART_ID into pa
from p in pa.DefaultIfEmpty()
group p by p.PART_ID into ps
select new { 
        CODE = ps.Select(g=>g.CODE),
        }

but after that "c" and "a" are gone and i don"t know how to get access to ITEM_DATA and ATTRIBUTE_DATA tables.

Perhaps my [SQL to LINQ recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would prove helpful? — NetMage, May 07 '18 at 19:04
Specifically, don't `group p` but rather `group new { c, a, p }`. — NetMage, May 07 '18 at 19:13

Converting SQL to LINQ with left join and group by

0 Answers0