LINQ equivalent of my SQL Query for UNION ALL

debugcn Published at Dev

RookieAppler

(SELECT * FROM SheetHay WHERE SheetStatus = 2)
 UNION ALL(SELECT * FROM SheetHay WHERE SheetStatus = 1)
 UNION ALL (SELECT * FROM SheetHay WHERE SheetStatus  = 0)
 UNION ALL(SELECT * FROM SheetHay WHERE SheetStatus= 3)

I get result set like this: enter image description here

I mean I get all '2' together, '0' together, '3' together ( no '1' in table yet). But when I use LINQ and UNION them I see the result on my grid in order of SheetID, the primary key. I mean I see the order of sheets displayed as 15,23,25,27,28,29 etc. But I want it just as the SQL result set.23,43,25,28 etc

IEnumerable<SheetHay> listTwos = Get(LINQ QUERY TO GET just twos);    
IEnumerable<SheetHay> listOnes = Get(LINQ QUERY TO GET just Ones);
IEnumerable<SheetHay> listZeros = Get(LINQ QUERY TO GET just  Zeros);
IEnumerable<SheetHay> listThrees = Get(LINQ QUERY TO GET just Threes);
....
    return listTwos.Union(listZeros).Union(listOnes).Union(listThrees);

Let me know if you need any other information. Thanks.

Tim Schmelter

You don't need to use multiple queries you can use CASE in the ORDER BY in sql and a similar way in LINQ.

SQL:

SELECT * FROM SheetHay 
WHERE SheetStatus IN(0,1,2,3)) 
ORDER BY CASE SheetStatus 
    WHEN 2 THEN 1 
    WHEN 1 THEN 2 
    WHEN 0 THEN 3 
    WHEN 3 THEN 4 END ASC, SheetStatus ASC

LINQ:

int[] status =  {0, 1, 2, 3};
var query = db.SheetHay 
    .Where(s => status.Contains(s.SheetStatus))
    .OrderByDescending(s => s.SheetStatus == 2)
    .ThenByDescending(s =>  s.SheetStatus == 1)
    .ThenByDescending(s =>  s.SheetStatus == 0)
    .ThenByDescending(s =>  s.SheetStatus == 3)
    .ThenBy(s =>  s.SheetStatus);

Descending because a comparison returns bool and true is "higher" than false(1/0).

You could also use a conditional operator to return an int for the ordering:

var query = db.SheetHay 
    .Where(s => status.Contains(s.SheetStatus))
    .OrderBy(s => s.SheetStatus == 2 ? 0 : 1)
    .ThenBy(s =>  s.SheetStatus == 1 ? 0 : 1)
    .ThenBy(s =>  s.SheetStatus == 0 ? 0 : 1)
    .ThenBy(s =>  s.SheetStatus == 3 ? 0 : 1)
    .ThenBy(s =>  s.SheetStatus);

Collected from the Internet

Please contact [email protected] to delete if infringement.