CODEWITHSUNDEEP
c#sqllinqasp-net-mvc-1
madvora
madvora

Reputation: 1747

Custom Sort in LINQ Query

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

Upvotes: 3

Views: 1869

Answers (4)

estinamir
estinamir

Reputation: 503

Sorting by index of the ArrayList:

    var periods = new List<string>() 
    { "AM1", "AM2", "0", "1", "2", 
        "3", "4", "5", "5A", "5B", "6", "6X", 
        "7", "8", "9", "10", "11", "12" };
                
    var bl2 = (from b in db.Tardies 
as IEnumerable<Tardies> select b)
              .ToList()
              .OrderByDescending(b => 
periods.IndexOf(b.period.Trim()));

Upvotes: 0

Luc Morin
Luc Morin

Reputation: 5380

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.

Upvotes: 0

msmolcic
msmolcic

Reputation: 6577

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

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727067

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

Upvotes: 4

Related Questions