LINQ to SQL OrderBy for a hardcoded string using Expression in C#

Question

I'm having a tables Namely StudentInfo and ScoreInfo

Table: DbSet<StudentInfo>

ID    Name
_____________________
1     Ram
2     Kumar

Table: DbSet<ScoreInfo>

Id    StudentId    Subject    Score
_____________________________________
1     1            GK         90
2     1            PHY        97
3     1            CHE        89
4     1            BIO        93
5     1            TAM        100
6     1            ENG        95
7     2            GK         85
8     2            PHY        76
9     2            CHE        63
10    2            BIO        79
11    2            TAM        61
12    2            ENG        60

The Linq to SQL Query (IQueryable)

using(var db = new DBContext()) {
    IQueryable<ScoreInfo> resultQuery = db.ScoreInfos.OrderBy(????)
      .Take(5)
}

The Order By Logic is

((m.Subject == "GK") || (m.Subject == "PHY") (m.Subject == "CHE") || (m.Subject == "BIO"))
    ? "Science"
    : "Arts"

The LINQ to SQL return result should be (Expected)

Id    StudentId    Subject    Score
_____________________________________
5     1            TAM        100
6     1            ENG        95
11    2            TAM        61
12    2            ENG        60
1     1            GK         90

The variable sortColumn is a assumption column based on the Sort Logic which was specified in above. Kindly assist me how to write the Expression<Func<ScoreInfo,object>> i.e., OrderBy(Expression<Func<ScoreInfo,object>>) ?

Hi, I don't get your order by logic. As it seems you want to take into consideration the subjects GK, PHY, CHEC and BIO. Why you use the conditional operator to separate them in Science and Arts? How can this be used in the order by. This is where I can't get you. What you like to take the 5 highest grades in the subjects GK, PHY, CHEC and BIO? This is pretty easy I can elaborate hou you can do so. However the expected output doesn't show that this is what you are looking for. Please state more clearly what are your criteria in plain english (without any code sample). Thanks — Christos, May 27 '17 at 18:15
@Christos - First I need the records of ENG and TAM, then the rest of the Non Language Subjects like GK, PHY, CHEM and BIO. So, in the 12 records 4 records are contains ENG or TAM moreover I'm doing the pagination work so 4 Records of ENG/TAM and 1 Science related record. — , May 27 '17 at 18:22
This smells like homework. You can write conditional expressions in order by like `d.Subject == "TAM"|| d.Subject == "EN"?0:(d.Subject =="Something else"? 1:2;` they het translated to case then in sql — Filip Cordas, May 27 '17 at 20:17
I answered something similar on [this](https://stackoverflow.com/questions/39258093/order-column-by-a-separate-liststring-variable/44210085#44210085) question. Note my answer uses Queriable the other answer dose not. — Filip Cordas, May 27 '17 at 20:20

score 1 · Accepted Answer · answered May 28 '17 at 04:53

1

You can do the same logic in Order By clause

var science = new HashSet<string> { "GK", "PHY", "CHE", "BIO" };

IQueryable<ScoreInfo> score = db.ScoreInfos
    .OrderBy(m => science.Contains(m.Subject) ? "S" : "A")
    .ThenBy(x => x.Id)
    .Take(5);

answered May 28 '17 at 04:53

B.Balamanigandan

4,713
11
68
130

Christos · Answer 2 · 2017-05-27T21:30:00.910

You could try something like the following:

var scienceSubjects = new HashSet<string> { "GK", "PHY", "CHE", "BIO" };

using(var db = new DBContext()) 
{
    var resultQuery = db.ScoreInfos
                        .Select(scoreInfo => new 
                        {
                            ScoreInfo = scoreInfo,
                            GrpId = scienceSubjects.Contains(scoreInfo.Subject) ? 0 : 1
                        })
                        .OrderByDescending(x=>x.GrpId)
                        .ThenByDescending(x=>x.ScoreInfo.Score)
                        .Select(x=>x.ScoreInfo)
                        .Take(5);
}

Essentially we split the subjects in two categories with ids 0 for science subjects and 1 for the rest of them. Then we order them in a descending order based on the category id, in order we have first the non-science object. Later we order them in a descending order based on the score info, in order we have the highest scores first. Last we pick up the 5 records we are interested in.

Here you will find a .net fiddle, where the above is tested.

LINQ to SQL OrderBy for a hardcoded string using Expression in C#

2 Answers2