# 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

```

# 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

# 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:

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

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

//EXECUTION
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.

#linq