LINQ Queries

Introduction 

 

Language-Integrated Query (LINQ) is one of the most useful queries nowadays. We have must have knowledge of LINQ, as it is required on every function/method to sort out the problem. It is one of the hottest topics for interviews, as every interviewer asks some questions from LINQ. So reader, without LINQ knowledge, a developer cannot survive in the software industry. LINQ is one of the most unique parts of programming, it doesn't matter what you are using in your backed DB (Oracle, SQL, MySQL, MongoDb).

 

The LINQ query will be the same for all, but if you write a Stored procedure, then your query based on database will be different. I am trying to cover here some of the basic to advanced queries in LINQ.

 

Prerequisites

 

Before going to start a LINQ query, I am assuming you have knowledge of .NET Framework3.5/4.5, C#, and  Visual Studio is also required.

 

Example 1

 

In this example, I'll write a simple LINQ query that gets all data from the list which contains 'a'.

List<string> listName =new List<string>{ "Hamid", "Ashish", "Smith", "Lily", "Mohan"}
//Query
var result = from n in listName
where n.Contains('a')
select n;
// foreach look for output
foreach(var name in result)
{
   Console.WriteLine(name);
}
// output
Hamid
Mohan

Example 2 - Simple Select Query

 

Suppose we have class, Name it Customer and it's address, like below:

public class Customer {
    public int Id {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public string Gender {
        get;
        set;
    }
    public sting Designation {
        get;
        set;
    }
    public string City {
        get;
        set;
    }
    public List < Address > Address {
        get;
        set;
    }
}
public class Address {
    public int Id {
        get;
        set;
    }
    public string AddressLine1 {
        get;
        set;
    }
    public string AddressLine2 {
        get;
        set;
    }
}

Then suppose we want to select only Customer data. The query syntaxt will be like this:

public void Test() {
    var customers = new List < Customer > {
        new Customer {
            Id = 10,
                Name = "c1",
                Address = new List < Address > {
                    new Address {
                        Id = 1,
                            AddressLine1 = "ad1"
                    }
                },
        }
    };
    var result = from cut in customers
    select cut;
}

Example 3 - Filter

 

In this example, I am trying to filter the record based on where condition, as in real project application most usable query.

 

It matches the condition and based on that, returns the filtered result, like this:

public void Test() {
    var result = from c in customers
    where c.Name == "Vijay"
    select c;
    //or
    var result = customers.Where(x => x.Name.Equals("vijay"));
}

We can also use here with ||, && condition as well.

 

The query woud be like this:

1: where c.Name == "Smith" && c.Name == "John"
2: where c.Name == "Jai" || c.Name == "Vijay"

Example 4 - Order

 

In this example, we are trying to understand order by the query. As we know, this query is useful in case we have a list of data and we want it sorted, either A-Z (this is called ascending order), or Z-A (this is called descending order).

 

Query syantax would be like this:

var result =
from c in customers
where c.Name == "Smith"
orderby c.Name ascending
select c;
var result =
from c in customers
where c.Name == "Smith"
orderby c.Name descending
select c;

Note

Sometimes we are required to set multiple orders by query. For this, we can use this query:

var result = customers.OrderBy(c => c.Gender).ThenBy(n => n.Name)

Here in this query, we are trying to sort the query Gender first, then sort by its name.

 

Example 5 - Group

 

The group query is used to group the result based on a set of keys.

 

Here in this example, I am considering Designation as a key,

 

So all customers will be grouped by designation:

var result = from c in customers
group c by c.Designation;
foreach(var g in result) {
    Console.WriteLine(g.Key);
    foreach(var customer in g) {
        Console.WriteLine(" {0}", customer.Name);
    }
}

Here in this query group by reslut always return a list of lists.

 

So we always need to use a nested foreach loop.

 

The first loop will use for key and the second loop will use for its member.

 

Example 6 - Join

 

As we know, joining is one of the most important concepts. Without joining, is not possible to develop any application.

 

In join, we need to fetch data from multiple data sources.

 

Suppose we have one class. Name it Distributor, like below:

public class Distributor {
    public int Id {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public string City {
        get;
        set;
    }
}
var innerJoinResult = from c in customers
join d in distributors on c.City equals d.City
select new {
    CustomerName = c.Name, DistributorName = d.Name
};

Example 7 - Aggregate Function

 

Aggregation functions in LINQ is an often-used function that is basically used to get a single value from the collection in which we apply this function/method

 

This is the list of the Aggregate Function used to perform operations:

 

S.N	METHOD	DESCRIPTION
1	Average	Used to get the average value of a collection of values.
2	Count	Used to count the elements in a collection
4	Max	Used to get the maximum value in a collection.
5	Min	Used to get the minimum value in a collection.
6	Sum	Used to get the sum of the values in a collection.

 

Example

int[] numbers = {220, 410, 150, 268, 390};
int result = numbers.Sum();
Console.WriteLine(result);

int result2 = sequence.Max();
Console.WriteLine(result2);

int result3 = sequence.Min();
Console.WriteLine(result3);

int result4 = sequence.Avg();
Console.WriteLine(result3);

Summary

 

This is a basic understanding of the LINQ queries.

 

Here, we have learned select, sort filter order group, and joins.

 

I hope in our upcoming article, we will learn more about LINQ.

 

Thank you for reading this article.