Simple self-join with LINQ and SQL

Let’s say we have a table in which we store the various test results of a persons.

PersonId TestId Result
1 TOEFL 5
1 IELTS 10
2 TOEFL 10
2 IELTS 6
3 TOEFL 2
4 IELTS 4

And assuming that it has been queried and stored in a list of objects.

results= db.Query<PersonResult>(SELECT * FROM Results).ToList();

(This is a Dapper query)

To get the result of one person in one row, we can do a self-join with LINQ as following:

var modifiedList = from r1 in theList.Where(x => x.TestId == "IELTS").ToList()
                   join r2 in theList.Where(x => x.TestId == "TOEFL").ToList()
                   r1.PersonId equals r2.PersonId 
                   select new Person
                           {
                                   PersonId= r1.PersonId
                                   TestId = r1.TestId,
                                   ResultToefl= r2.Result
                                   ResultIelts = r1.Resul
                            }

Or another way, I think better is to do is with SQL in the server:

SELECT 
t1.PersonId, t1.Result as TOEFLResult, t2.Items AS IELTSResult
FROM Results t1, Results t2
WHERE t1.PersonId= t2.PersonId
AND t1.TestId = 'TOEFL' and t2.TestID='IELTS'
ORDER BY t1.CompletionDate DESC

#linq #sql #self-join #combine-two-rows-sql #merge-two-row-sql-linq #merge-rows