Custom Sort in LINQ Query

Question

I have a table valued function that I'm calling with a LINQ query.
As I now understand it (as referenced here), the sorting needs to be done outside of the table valued function. This means, I have to do it in the LINQ query.

I need some assistance with the syntax. Here's my original call to the function.

var sourceQuery = (from f in db.fGameListDataTable(competitionID, eventID,  participantType)
                   select f);

I need to take this custom sorting from the SQL query and add it to the LINQ query.

Order By 
    CASE 
        WHEN GameType = 'G' THEN '1'
        WHEN GroupNumber = '1' THEN '2'
        WHEN GroupNumber = '2' THEN '3'
        WHEN GroupNumber = '3' THEN '4'
        WHEN GroupNumber = '4' THEN '5'
        WHEN GameType = 'GT' THEN '6'
        WHEN GameType = 'P' THEN '7'
        WHEN GameType = 'FT' THEN '8'   
    END ASC,
    g.GameID ASC

UPDATE

For a visual reference, here's the table that getting sorted. Note the order.

enter image description here

Do you control the code of the SQL function ? Can't you add an extra column to the returned "table" ? — Luc Morin, Jul 19 '15 at 01:33
Yes, it's my function. Can you explain what you mean with the extra column? — madvora, Jul 19 '15 at 02:31
Maybe there's something escaping me, but why don't you simply return that extra sorting column from the database function ? Then you can simply sort based on it. — Luc Morin, Jul 19 '15 at 02:49
Actually - just tried your idea. No that doesn't work, because it's more than just assigning a single value to each column. For instance, some records can have both a GameType of 'G' and be in GroupNumber '1'. — madvora, Jul 19 '15 at 03:55
You could use the power of expressions in .net https://stackoverflow.com/questions/2013198/linq-to-sql-dynamic-orderby-case-when/56868434#56868434 — Tiju John, Aug 05 '19 at 13:16

score 4 · Answer 1 · answered Jul 19 '15 at 00:30

4

You could build a big conditional expression. Unfortunately, it is going to have eight levels of nesting (yuck!)

var ordered = (from f in ... select ...)
    .OrderBy(f =>
        f.GameType == 'G' ? 1
    :   f.GroupNumber == '1' ? 2
    :   f.GroupNumber == '2' ? 3
    :   f.GroupNumber == '3' ? 4
    :   f.GroupNumber == '4' ? 5
    :   f.GameType == 'GT'   ? 6
    :   f.GameType == 'P'    ? 7
    :   f.GameType == 'PT'   ? 8
    :   9
    );

answered Jul 19 '15 at 00:30

Sergey Kalinichenko

675,664
71
998
1,399

I think this may be the solution. I'll let you know when I can test out out. Does this affect performance at all? – madvora Jul 19 '15 at 17:47
@madvora I suspect that you would get the same generated SQL for this (i.e. with a `CASE` expression), so I think the performance is not going to be different. – Sergey Kalinichenko Jul 19 '15 at 20:34

score 2 · Answer 2 · answered Jul 19 '15 at 09:23

It's possible to do something like this:

var sourceQuery =
    (from f in db.fGameListDataTable(competitionID, eventID, participantType) select f)
    .OrderBy(f =>
    {
        int sortValue = 0;

        if (f.GameType == "G")
            sortValue = 1;
        else if (f.GroupNumber == "1")
            sortValue = 2;
        else if (f.GroupNumber == "2")
            sortValue = 3;
        else if (f.GroupNumber == "3")
            sortValue = 4;
        else if (f.GroupNumber == "4")
            sortValue = 5;
        else if (f.GameType == "GT")
            sortValue = 6;
        else if (f.GameType == "P")
            sortValue = 7;
        else if (f.GameType == "FT")
            sortValue = 8;

        return sortValue;
    }).ThenBy(f => f.GameID);

Luc Morin · Answer 3 · 2015-07-19T15:08:43.457

I still fail to see why you can't use the DB engine to generate that number based on the conditions that you described.

You already gave this as a sample code:

Order By 
    CASE 
        WHEN GameType = 'G' THEN '1'
        WHEN GroupNumber = '1' THEN '2'
        WHEN GroupNumber = '2' THEN '3'
        WHEN GroupNumber = '3' THEN '4'
        WHEN GroupNumber = '4' THEN '5'
        WHEN GameType = 'GT' THEN '6'
        WHEN GameType = 'P' THEN '7'
        WHEN GameType = 'FT' THEN '8'   
    END ASC,
    g.GameID ASC

Now, while I understand you might not want (or even can) have this exact code inside your table value function (as per your link, the ORDER BY needs to be outside the function), I'm pretty near sure you could still have the CASE statement to generate an additional column.

I'm not saying that you should ORDER BY on the server though. Simply generate the additional column with the CASE statement, then you will be able to use that column as the LINQ query's order by.

In my earlier comment, when I mentioned adding an extra column, I didn't mean just a sequential column, I meant what I wrote above. That extra column's value would be based on your exact same logic, but would be generated on the server rather than try to implement it in LINQ client side.

Regards

EDIT:

Here's a code snippet showing what it would look like within your table value function:

INSERT INTO @Table
  SELECT EventID, GameID, [all you other columns],
    CASE 
        WHEN GameType = 'G' THEN '1'
        WHEN GroupNumber = '1' THEN '2'
        WHEN GroupNumber = '2' THEN '3'
        WHEN GroupNumber = '3' THEN '4'
        WHEN GroupNumber = '4' THEN '5'
        WHEN GameType = 'GT' THEN '6'
        WHEN GameType = 'P' THEN '7'
        WHEN GameType = 'FT' THEN '8'   
    END
  FROM [Whatever you pull your columns from]

This is just an example, not the actual code.

This doesn't work because it's sorting on multiple levels. Take a look at the first record in my example (gameID 279). According to this logic you would first assign that a 1 because of the game type, then you would overwrite that with a 2 because it's group number 1. Also you would have multiple records assigned the same value. How would that help the sort? The original SQL case logic sorts a small group first, then continues to sort other levels of that small group. — madvora, Jul 19 '15 at 17:44
Like I said, there's something obviously escaping me. I honestly fail to see how your sample SQL CASE is any different. Maybe if you posted the entire original table function, the one that doesn't work ? — Luc Morin, Jul 19 '15 at 23:01

Custom Sort in LINQ Query

3 Answers3