LINQ to SQL C# COALESCE

Question

Given the following table:

Length | Width | Color | ID
===========================
    18 |    18 |  blue |  1
---------------------------
    12 |    12 |   red |  1
---------------------------

I want to produce a single column/row:

 SIZES
 =================
 18 x 18, 12 x 12,

I can do this in SQL as follows:

DECLARE @SIZES VARCHAR(8000)
SELECT @SIZES = COALESCE(@SIZES, '') + Convert(varchar(80), [Length]) + ' x ' + 
                Convert(varchar(80), [Width]) + ', '
FROM table
where ID = 1
GROUP BY [Length], [Width]
ORDER BY [Length], [Width]
SELECT SIZES = @SIZES

But I cannot figure out how to do this in LINQ.

The closest I got was:

from t in table
where id == 1
group t by new {
                 t.Length,
                 t.Width
               } into g
orderby g.Key.Length, g.Key.Width
select new {
             SIZES = (Convert.ToInt32(g.Key.Length) + " x " +
                      Convert.ToInt32(g.Key.Width) + ", ")
           }

Which produces one column and two rows:

SIZES
========
18 x 18,
12 X 12,

The converts are unimportant to the problem. The columns are defined as floats though all are integers. The key is the COALESCE function I cannot figure out how to do that in LINQ.

Answer 1

Try ?? (null coalesce operator) like:

t.Length ?? 0

Answer 2

I don't think LINQ to SQL supports this T-SQL trick. The COALESCE isn't really the issue (as Mehrdad points out the equivalent in C# is ??) -- it's the fact that SQL Server aggregates each result via string concatenation into the variable @SIZES. AFAIK LINQ to SQL can't construct this type of query.

This will yield your desired result, but the string concatenation is performed on your side, not on the SQL server side. That probably doesn't matter.

var query = 
    from t in table
    where id == 1
    group t by new {
                 t.Length,
                 t.Width
               } into g
    orderby g.Key.Length, g.Key.Width
    select new {
             SIZES = (Convert.ToInt32(g.Key.Length) + " x " +
                      Convert.ToInt32(g.Key.Width) + ", ")
           };

var result = string.Join(string.Empty, query.Select(r => r.SIZES).ToArray());

Answer 3

I would just return the int sizes from SQL and do the string building client-side:

var query = 
    from t in table
    where id == 1
    group t by new {
                 t.Length,
                 t.Width
               } into g
    orderby g.Key.Length, g.Key.Width
    select g.Key;

var sizeStrings = from s in query.AsEnumerable()
                  select string.Format("{0} x {1}", s.Length, s.Width);

var result = string.Join(", ", sizeStrings.ToArray());

Answer 4

You can't do it with LINQ-to-SQL, but you can do it with LINQ.

In other words, you are executing a 'SELECT *' type statement to SQL and are doing the 'formatting' outside the database. But it's extremely simple:

string result = String.Empty;
this.Context.Sizes.ForEach(s -> 
   result += string.Format("{0} x {1}", s.Length, s.Width));