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 IELTS 10
2 TOEFL 10

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:

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

Basics of LINQ II

After a very simple beginning of LINQ let’s continue to play with it more.

In this article, I will try to share some basic methods such as First,Last, Max and Min.

1. Numbers Example:

Let’s create an int array with some numbers and find the first number, last number, first number which is greater then 4, first odd number and number less then 6.

var numbers = new int[] { 2, 3, 4, 5, 6, 7, 8, 9 };

 var firstNumber = numbers.First(); // = 2

 var lastNumber = numbers.Last(); // = 9 

 var firstNumberGreaterThenFour = numbers.First(t => t > 4) ; // = 5

 var firstOddNumber = numbers.First(n => n % 2 == 1); // = 3

 var lastNumberLessThanSix = numbers.Last(n => n < 6); // = 5

2. My Friends

In this part, I’ve created a custom class called Friend and made a list of my friend with their age. Continue reading

#first, #last, #linq-2, #max, #min

Basics of LINQ

Some notes on LINQ
Language-Integrated Query (LINQ) is the technology that provides tools for developers to use query directly in the programming language (C#). A query is an expression that retrieves data from a data source. In LINQ, you work with objects. You use the codes to query and transform data in XML documents, SQL databases, Datasets, collections, and any other format that LINQ is available.

All LINQ operations include there actions:

  1. Obtain the data source.
  2. Create the query.
  3. Execute the query.

A simple example of LINQ:

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };

var queryLowNums =
from num in numbers
where num &lt; 6
select num;

foreach (int num in numQuery)
Console.Write("{0,1} ", num);

This is just an introduction to LINQ. I will share more while I build with it.