LINQ to SQL C#粗疏 [英] LINQ to SQL C# COALESCE

问题描述

给出下表:

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,

我可以在SQL中执行以下操作:

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

但是我不知道如何在LINQ中做到这一点.

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

我最接近的是:

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,

转换对问题并不重要.尽管所有列都是整数，但这些列被定义为浮点数.关键是COALESCE函数，我无法弄清楚如何在LINQ中做到这一点.

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.

推荐答案

我不认为LINQ to SQL支持此T-SQL技巧. COALESCE并不是真正的问题(正如Mehrdad指出的，C#中的等效项是??)-这是SQL Server通过字符串连接将每个结果聚合到变量@SIZES中的事实. AFAIK LINQ to SQL无法构造这种类型的查询.

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.

这将产生所需的结果，但是字符串连接是在您这一边执行的，而不是在SQL Server方面执行的.可能没关系.

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());

这篇关于LINQ to SQL C#粗疏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！