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'.
  1. List<string> listName =new List<string>{ "Hamid""Ashish""Smith""Lily""Mohan"}    
  2. //Query    
  3. var result = from n in listName    
  4. where n.Contains('a')    
  5. select n;    
  6. // foreach look for output    
  7. foreach(var name in result)    
  8. {    
  9.    Console.WriteLine(name);    
  10. }    
  11. // output    
  12. Hamid    
  13. Mohan    
Example 2 - Simple Select Query
 
Suppose we have class, Name it Customer and it's address, like below:
  1. public class Customer {  
  2.     public int Id {  
  3.         get;  
  4.         set;  
  5.     }  
  6.     public string Name {  
  7.         get;  
  8.         set;  
  9.     }  
  10.     public string Gender {  
  11.         get;  
  12.         set;  
  13.     }  
  14.     public sting Designation {  
  15.         get;  
  16.         set;  
  17.     }  
  18.     public string City {  
  19.         get;  
  20.         set;  
  21.     }  
  22.     public List < Address > Address {  
  23.         get;  
  24.         set;  
  25.     }  
  26. }  
  27. public class Address {  
  28.     public int Id {  
  29.         get;  
  30.         set;  
  31.     }  
  32.     public string AddressLine1 {  
  33.         get;  
  34.         set;  
  35.     }  
  36.     public string AddressLine2 {  
  37.         get;  
  38.         set;  
  39.     }  
  40. }   
Then suppose we want to select only Customer data. The query syntaxt will be like this:
  1. public void Test() {  
  2.     var customers = new List < Customer > {  
  3.         new Customer {  
  4.             Id = 10,  
  5.                 Name = "c1",  
  6.                 Address = new List < Address > {  
  7.                     new Address {  
  8.                         Id = 1,  
  9.                             AddressLine1 = "ad1"  
  10.                     }  
  11.                 },  
  12.         }  
  13.     };  
  14.     var result = from cut in customers  
  15.     select cut;  
  16. }   
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:
  1. public void Test() {  
  2.     var result = from c in customers  
  3.     where c.Name == "Vijay"  
  4.     select c;  
  5.     //or    
  6.     var result = customers.Where(x => x.Name.Equals("vijay"));  
  7. }   
We can also use here with ||, && condition as well.
 
The query woud be like this:
  1. 1: where c.Name == "Smith" && c.Name == "John"  
  2. 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:
  1. var result =  
  2. from c in customers  
  3. where c.Name == "Smith"  
  4. orderby c.Name ascending  
  5. select c;  
  6. var result =  
  7. from c in customers  
  8. where c.Name == "Smith"  
  9. orderby c.Name descending  
  10. select c;  
Note
Sometimes we are required to set multiple orders by query. For this, we can use this query:
  1. 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:
  1. var result = from c in customers  
  2. group c by c.Designation;  
  3. foreach(var g in result) {  
  4.     Console.WriteLine(g.Key);  
  5.     foreach(var customer in g) {  
  6.         Console.WriteLine(" {0}", customer.Name);  
  7.     }  
  8. }   
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:
  1. public class Distributor {  
  2.     public int Id {  
  3.         get;  
  4.         set;  
  5.     }  
  6.     public string Name {  
  7.         get;  
  8.         set;  
  9.     }  
  10.     public string City {  
  11.         get;  
  12.         set;  
  13.     }  
  14. }  
  15. var innerJoinResult = from c in customers  
  16. join d in distributors on c.City equals d.City  
  17. select new {  
  18.     CustomerName = c.Name, DistributorName = d.Name  
  19. };   
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
  1. int[] numbers = {220, 410, 150, 268, 390};    
  2. int result = numbers.Sum();    
  3. Console.WriteLine(result);    
  4.     
  5. int result2 = sequence.Max();    
  6. Console.WriteLine(result2);    
  7.     
  8. int result3 = sequence.Min();    
  9. Console.WriteLine(result3);    
  10.     
  11. int result4 = sequence.Avg();    
  12. 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.