1

With Linq you can group and sum or group and count. Is it possible to group an concat strings?

Here is an example:

var list = (from x in Context.tblProduct
            group x by new { x.OrderNo, x.Color } into groupedByColorCode
            select new
                {
                    OrderNo = groupedByColorCode.Key.OrderNo,
                    ProductRef = groupedByColorCode.FirstOrDefault().ProductRef,
                    Color = groupedByColorCode.Key.Color,
                    Size = groupedByColorCode.Concat(bcc => bcc.Size).ToString(), // This line doesn't work
                    TotalQuantity = groupedByColorCode.Sum(bcc => bcc.OriQty).ToString()
                });

What should I write in place of 

Size = groupedByColorCode.Concat(bcc => bcc.Size).ToString()
Bastien Vandamme
  • 17,659
  • 30
  • 118
  • 200

2 Answers2

4

Simply use String.Join overload that accepts an IEnumerable<string>:

var size = String.Join("", groupedByColorCode.Select(bcc => bcc.Size));

Edit: Oh:I get it! I never ever use Linq for SQL queries, so I wasn't catching up with the problem. If this gets translated into SQL, there is no way you can do that on the database (if you're not on PostgreSQL or some other smart db and you don't want to mess with SQL itself). SQL can do Sum, Avg, etc, but no Concat (usually). You'll have to retrieve the colorcodes and join them after the roundtrip on the db, with the provided method.

Alberto Chiesa
  • 7,022
  • 2
  • 26
  • 53
  • My var list is of type Lis<'a> 'a is anonymous type and correspond to my select New definition. When I add your solution into my select new the type change to ?. Yes to a question mark. – Bastien Vandamme Mar 16 '16 at 01:15
  • How are you inserting it? In my example I put a var which is not suitable for Linq, same for the ";" at the end of the line. Just put "Size = " as in your example and "," instead of ";". – Alberto Chiesa Mar 16 '16 at 01:17
  • 1
    Do keep in mind that this solution may not work if the provider doesn't translate the call to `String.Join` to an appropriate SQL instruction. You may need to complete your SQL query without the `String.Join` and then bring the results into memory before doing the join then. – Enigmativity Mar 16 '16 at 01:18
  • @B413 Please don't edit answers with new questions. In any case, the problem was because the answer had a typo (missing closing bracket) which has now been fixed. – Rob Mar 16 '16 at 01:27
  • @Rob I didn't noticed the missing bracket. Thank you Rob. – Bastien Vandamme Mar 16 '16 at 01:30
  • Now I'm curious to know if it can handle it at the SQL level or if the provider is going to retrieve all the lines anyway. ;) – Alberto Chiesa Mar 16 '16 at 01:32
0

You can also use aggregate as an alternative - although join (already suggested) is the most direct approach. Smink describes the process in his answer to a similar question:

Using Aggregate Queries

Although, as the other post points out, it can be much slower than stringbuilder.

Community
  • 1
  • 1
Taylor Flatt
  • 73
  • 9