LINQ到SQL组的数据行是空白的吗？

Question

我在sql中有以下代码：

SELECT "Field 1" AS fullname, null  AS limsuser_id
   UNION 
SELECT fullname, limsuser_id from table1
   UNION
SELECT "Field 2" AS fullname, null  AS limsuser_id
   UNION 
SELECT fullname, limsuser_id from table2

基本上，“字段1”和“字段2”充当标题。

现在，它在ASP.net端返回一个数据集。

但是，当有人在对数据集排序时单击“全名”时，数据也会使用“字段1”和“字段2”进行排序。

分类前：

Field 1
Jason Z   | 25
Anthony P | 21

Field 2
Shane | 24
Damon | 23

排序后：

Anthony P | 21
Damon     | 23
Field 1
Field 2
Jason Z   | 25
Shane     | 24

预期结果：

Field 1
Anthony P | 21
Jason Z   | 25
Field 2
Damon | 23
Shane | 24

如何使用linq在dataset上使用“字段1”和“字段2”进行分组？这是一个简化的sql。我无权更改sql查询。

Answer 1

您应该有一个字段，该字段显示您从哪个表中提取它们：

SELECT "Field 1" AS fullname, null  AS limsuser_id, 1 as [order]
   UNION 
SELECT fullname, limsuser_id, 1 as [order] from table1 
   UNION
SELECT "Field 2" AS fullname, null  AS limsuser_id, 2 as [order]
   UNION 
SELECT fullname, limsuser_id, 2 as [order] from table2

然后，您可以简单地这样做：

var result = yourItems.OrderByDescending(x=> x.fullname == "Field 1" || x.fullname == "Field 2")
    .ThenBy(x=> x.fullname)
    .GroupBy(x=> x.order);

.OrderByDescending(x=> x.fullname == "Field 1" || x.fullname == "Field 2")确保Field 1和Field 2将站在他们的群体的顶端，不管他们的非道德秩序是什么。

然后.ThenBy(x=> x.fullname)将按fullname对其进行排序

然后，您可以在GroupBy上的order字段。

或者，如果您只有Field 1和Field 2的null id，则可以这样做：

var result = yourItems.OrderByDescending(x=> x.limsuser_id  == null)
    .ThenBy(x=> x.fullname)
    .GroupBy(x=> x.order);

Answer 2

@Ashkan提出的新方法将有效。但是，对于您的要求，您可能不需要分组。只需为4组中的每一组添加列以标识具有不同值的顺序。

SELECT "Field 1" AS fullname, null  AS limsuser_id, 1 AS order_no
   UNION 
SELECT fullname, limsuser_id, 2 AS order_no from table1 
   UNION
SELECT "Field 2" AS fullname, null  AS limsuser_id, 3 AS order_no
   UNION 
SELECT fullname, limsuser_id, 4 AS order_no from table2

然后在Linq中进行排序。

var orderedSet = items.OrderBy(x=> x.order_no).ThenBy(x=> x.fullname);