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

Question

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?

But does it really though? Look at the extra data you have downloaded, and the data you saved. You have downloaded extra: `,`. You have saved on not downloading:`1`. Maybe a couple of bytes saved, a tiny percentage of the overall. And it's loaded more of the job onto the DB. Overall while I'm not knocking your approach (I was going to recommend the same) it feels like a premature optimization to jump to "this is always best done in the db". There are other strategies a client could employ to mean this isn't done in-mem, depending on the use case — Caius Jard, Jan 28 '22 at 07:18
You can describe a use case, and list "other strategy" in the answer. In the end, someone will benefit from it too :) EDIT: If you consider sending an array(via JSON), the extra ", "(comma), you have a problem with will be transmitted anyway. — AMunim, Jan 28 '22 at 07:36

AMunim · Accepted Answer · 2022-08-25T10:34:12.047

UPDATE: As of EF7 preview 7, now you simply use string.Join normally for example:

_context.MyTable
    .GroupBy(keySelector => keySelector.MyKey, elemSelector => elemSelector.StringProp)
    .Select(elem => string.Join(',', elem))
   //.FirstOrDefaultAsync(cancellationToken), if (keyselector => 1) i.e. only 1 group so you get all rows

Old answer
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
       }

Note, EFCore 7 string.Join() only works when targeting SQL 2017+. On older versions you will get a runtime exception! — LMK, Jul 05 '23 at 23:41

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

1 Answers1