Language Integrated Query, also known as LINQ, is a powerful query language that is introduced in .NET 3.5 framework. It allows you a consistent way to query any kind of data sources like SQL database, in-memory arrays or objects. It has multiple steps such as fetch data from relational databases or XML, save data to the same sources, create the query and the last step is to execute the query. It has numerous built-in functions to support the query on different data sources. There can be multiple scenarios where the record filtration is required in an application.
How to perform an SQL IN Logic
The IN operator is a special operator which checks a value in a set of values. Let us suppose you want to extract a result list of the students who have passed the exams belonging Computer Science department, to be shared with the Examination department.
You will need to create a simple class that takes the personal information of the registered students.
public class Student
{
public int StudentID { get; set; }
public string Name { get; set; }
public string EmailAddress { get; set; }
public string Department { get; set; }
public Datetime DOB { get; set; }
public string Gender { get ; set; }
}
The next step is to fill some dummy data and create a list of students that belongs to Computer Science department.
public List GetStudents() {
List Students = new List();
Students.Add(new Student { ID = 1, Name = "Student 1", EmailAddress = "[email protected]" , Department = "ComputerScience " , DOB = "YYYY-MM-DD " , Gender = "Option A " });
Students.Add(new Student { ID = 2, Name = " Student 2", EmailAddress = "[email protected] " , Department = "ComputerScience " , DOB = "YYYY-MM-DD " , Gender = "Option A " });
Students.Add(new Student { ID = 3, Name = " Student 3", EmailAddress = "[email protected] " , Department = "ComputerScience " , DOB = "YYYY-MM-DD " , Gender = "Option B " });
Students.Add(new Student { ID = 4, Name = " Student 4", EmailAddress = "[email protected] " , Department = "ComputerScience " , DOB = "YYYY-MM-DD " , Gender = "Option B" });
return Students;
}
Once the static students list is ready, extract the list of students who have cleared the exams in the Computer Science department by using the following code:
// The following query has Contains method to perform the IN logic translated by SQL
var passingStudentsList = _examRepository.GetResults ()
.Where(result => GetStudents().Contains(result.StudentID))
.ToList();How find items that are not in another list
This is the reverse scenario of the above query. If you want to select the students who are not from Computer Science department then you can use the following query:
// Get the exam results of those students who are not in the Computer Science department
var result = _examRepository.GetResults ()
.Where(p => GetStudents().All(p2 => p2.StudentID != p.StudentID))
.ToList();
How to perform Case-insensitive “contains” in LINQ
If you call Contains() method in dbcontext class, it will mapped to the LIKE ‘%hello%’ operator automatically, and can perform the search by following the two ways:
var results = _StudentRepository.GetPassedOutStudents
.Where(p => p.Name.ToLower().Contains(GetStudents().Name.ToLower()))
.ToList();
How to perform Case-insensitive “contains” in LINQ
For string comparison scenarios, you often need a case-insensitive search to have a perfect matching records from the list or database. If you call Contains() method in dbcontext class, it will mapped to the LIKE ‘%hello%’ operator automatically, and can perform the search by following the two ways:
var results = _StudentRepository.GetPassedOutStudents
.Where(p => p.Name.ToLower().Contains(GetStudents().Name.ToLower()))
.ToList();
Alternatively, you can also do perform case insensitive comparison in this way:
var lists = _StudentRepository.GetPassedOutStudents
.Where(p => CultureInfo.CurrentCulture.CompareInfo.IndexOf
(p.Name, GetStudents().Name, CompareOptions.IgnoreCase) >= 0)
.ToList();
How to improve the performance of a .contains() operation
In LINQ, Contains() method translates the query into IN clause in SQL which is a slow process but SQL execution is fast. This does not mean that you do searching one by one records. It can be ideal for medium datasets but not for very large collections. Entity Framework does not contain the native translation of the Contains() method, therefore, ADO.NET cannot process the query efficiently. Alternatively, you can use stored procedures to achieve the fast performance. Microsoft Corporation has addressed this issue in the Entity Framework 6. They have added an InExpression, which increased the performance of Enumerable Contains function.
