Similar to SQL, it is possible to write queries in C# LINQ using the query syntax. These queries are used in C# database applications on web to get the data from the database and display on the web to the third party users. In this guide, we will discuss some LINQ queries that are similar to SQL. For this, we will create a list data source and apply the queries in it.

Query Syntax:

Let’s look at the generic syntax:

Here:

1. The Data_Source can be the list that holds the data.
2. The iterator is used to fetch the elements from the Data_Source.

Data Source

In this entire guide, we will use the following list of records as a data source and all the queries are applied on this data source only. Make sure that you run this code in your environment and modify the query statements with the following examples one by one which we are going to discuss:

Records:

Explanation:

1. Create the prices with the following attributes:

2. Create another class which is “Calculations” with main method and create the country_prices list with five records.

Select

Basically, “select” is a projection operator which selects the attributes from the specified data source. The query starts with “from”. Then, we specify the iterator that iterates over the data source. Then, the “select” operator is specified.

Syntax:

All Attributes: from iterator in Data_Source select iterator;

Specific Attribute: from iterator in Data_Source select iterator.attribute;

Example 1:

Let’s write a query to select all the records from the list.

Output:

Here, we didn’t specify any attribute in the “select” query. We fetched all the attributes from the query (data) inside the “foreach” loop using the iterator.

Example 2:

Now, get the items by specifying the item attribute within the “select” operator. The query is  “from i in country_prices select i.item”.

Output:

Line # 21 – Line # 29:

2. Where

If you want to filter the data based on some conditions/s, you can use the “where” operator in the query along with the “select” clause. But the “where” operator is used first and then the select operator is specified.

Syntax:

Let’s see how to use the “where” operator inside the LINQ query.

Example 1: Single Condition

Let’s filter the records based on the item attribute. Use the equal to (==) operator in the “where” operator as a condition and compare the iterator with “Chillies”. So, the records that are related to “Chillies” are selected.

The query is “from i in country_prices
                          where i.item == “Chillies”
                          select i”

Output:

There is only one record with the “Chillies” item.

Line # 21 – Line # 30:

Example 2: Multiple Conditions

Let’s filter the records based on the location and quantity attributes. The quantity should be greater than 50 and less than 300. The location should be “USA”.

The query is “from i in country_prices
                          where i.quantity > 50
                          where i.quantity < 300
                          where i.location == “USA”
                          select i”

Output:

There are two records that matched the previous conditions.

Line # 21 – Line # 32:

Example 3: And (&&) Operator

We can specify the “and (&&)” operator to specify multiple conditions at a time. If all the conditions are true, the records that satisfy all the conditions are returned by the query.

In this example, we select the records if the quantity is greater than 20 and the cost is 67.89.

The query is “from i in country_prices
                          where i.quantity < 20 && i.cost == 67.89
                          select i”

Output:

There is only one record with the quantity that is less than 20 and a cost of 67.89

Line # 21 – Line # 29:

Example 4: Or (||) Operator

The “or (||)” operator is also used to specify multiple conditions at a time. If at least one condition is true, the records that satisfy that condition are returned.

In this example, we select the records if the quantity is greater than 300 or the location is “Tokyo”.

The query is “from i in country_prices
                          where i.quantity > 300 || i.location == “Tokyo”
                          select i”

Output:

There is only one record with a quantity that is greater than 300 (the first condition is matched).

Line # 21 – Line # 29:

3. Order By

If you want to arrange the records that are returned by the LINQ query in ascending or descending order based on values in any of the attributes, you can use the “order by” operator in the query. You need to specify this operator before the “select” operator. 

Syntax:

Let’s see how to use the “order by” operator inside the LINQ query.

Ascending Order:

Descending Order:

Example 1: Ascending Order

Select all the attributes from the data source (list) and return them in ascending order based on the values in the quantity attribute.

The query is “from i in country_prices
                   orderby i.quantity ascending
                   select i”

Output:

Line # 21 – Line # 29:

Example 2: Descending Order

Select all the attributes from the data source (list) and return them in descending order based on the values in the cost attribute.

The query is “from i in country_prices
                   orderby i.cost descending
                   select i”

Output:

Line # 21 – Line # 29:

4. Limit

Limit in SQL limits the records that are returned by the query. It returns the top records that are returned by the query. In LINQ, we can achieve this by utilizing the Skip() with the Take() operator. Take() gets the specified number of records. Skip() is used to specify the starting record number. In this way, we can achieve the “limit” functionality in LINQ.

Syntax:

1. Skip() is used to skip the records and return the remaining records. It takes an integer which specifies the number of elements to be skipped. In our case, it is 0.
2. Take() is used to take “n” number of records from the first record.

Example:

Select the first three records out of five records that are returned by the query.

The query is “(from i in country_prices
                   select i).Skip(0).Take(3)”

        }

Output:

Line # 21 – Line # 28:

Conclusion

We learned how to write the queries in C# LINQ which  is similar to SQL. As part of this tutorial, we discussed how to use the “select” operator to select the records from the data source. To filter the records that are returned by the query, we used the “where” operator by specifying the conditions. Then, we learned how to sort the records that are returned by the query with the “order by” operator. Finally, to limit the records, we utilized the Skip() and Take() operators.