[SOLVED] string.Join in Entity framwork, LINQ to SQL. Without Client-side evaluation

Issue

If you have a table, similar to here:

DataTypeID, DataValue
1,"Value1"
1,"Value2"
2,"Value3"
3,"Value4"

and want output like this:

DataTypeID,DataValues
1,"Value1,Value2"
2,"Value3"
3,"Value4"

Most questions suggest like this to use toList() or AsEnumerable() and then, string.Join(", ", DataValues) on client-side. This might work if the data is not huge but it defeats the purpose of using EF. How can I do this without loading all the data in-memory?

Solution

Well, as per this this issue, string.Join() is yet to be implemented(as of now) and IEnumerable.Aggregate will not translate either.
In the meanwhile, you can create a view and write your SQL there.
For example, to group by id and string.Join(", ", Names);

CREATE VIEW V_Name AS
SELECT ID,  
Names=STUFF  
(  
    (  
      SELECT DISTINCT ' || '+ CAST(Child.Name AS VARCHAR(MAX))  
      FROM Child,MainTable
      WHERE Main.ID= t1.ID --this line is imp...
      AND Child.ID=MainTable.ID
      FOR XMl PATH('')  
    ),1,1,''  
)  
FROM MainTable t1 
GROUP BY t1.IDReview 

OR

CREATE VIEW V_Name AS
SELECT ID, STRING_AGG(Name, ', ') AS Names
FROM MainTable
LEFT JOIN ChildTable ON MainTable.ID = ChildTable.ID
GROUP BY ID

Now, in your C# you can simply join this with your ID, just like you normally would with an IQueryable:

       from data in _dbcontext.sometable
       join groupedAndJoinedNames in _dbcontext.viewname
                on data.ID equals groupedAndJoinedNames.ID
       select new
       {
            Names = groupedAndJoinedNames.Names
       }

Answered By – AMunim

Answer Checked By – Jay B. (BugsFixing Admin)