Language Integrated Query, also known as LINQ, allows you to query any kind of data sources like SQL database, in-memory arrays or objects. It has numerous built-in functions to support the query on different data sources. There can be multiple scenarios where record filtration is required.
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 8 9 10 |
public List GetStudents() { List Students = new List(); Students.Add(new Student { ID = 1, Name = "Student 1", EmailAddress = "Name1@abc.com" , Department = "ComputerScience " , DOB = "YYYY-MM-DD " , Gender = "Option A " }); Students.Add(new Student { ID = 2, Name = " Student 2", EmailAddress = "Name2@abc.com " , Department = "ComputerScience " , DOB = "YYYY-MM-DD " , Gender = "Option A " }); Students.Add(new Student { ID = 3, Name = " Student 3", EmailAddress = "Name3@abc.com " , Department = "ComputerScience " , DOB = "YYYY-MM-DD " , Gender = "Option B " }); Students.Add(new Student { ID = 4, Name = " Student 4", EmailAddress = "Name4@abc.com " , Department = "ComputerScience " , DOB = "YYYY-MM-DD " , Gender = "Option B" }); return Students; } |
Extract the list of students who have cleared the exams in the Computer Science department.
|
1 2 3 4 5 6 |
// 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:
|
1 2 3 4 5 6 |
// 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:
|
1 2 3 4 5 |
var results = _StudentRepository.GetPassedOutStudents .Where(p => p.Name.ToLower().Contains(GetStudents().Name.ToLower())) .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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 |
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.
Source: https://www.chubbydeveloper.com/
