Najitaka - 2 years ago 190
SQL Question

LINQ to SQL C# COALESCE

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.

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());
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download