In SQL, the LIKE operator is used for pattern matching with wildcards
(%, _). In LINQ to SQL, we use Contains(), StartsWith(), and
EndsWith() to achieve the same functionality.
Let's connect the SQL Sever database to our Console Application
Entity Framework Core is the modern and preferred way to use LINQ with SQL Server.
Step 1: Install Required NuGet Packages
install the below NuGet packages, Right click on your Project name from Solution Explorer → click on Manage NuGet Packages.. → select Browse → search and install below all packages
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.ToolsStep 2: Add SQL Server Database to your Project
Click on Server Explorer → choose the Connect to Database option. A new popup will open like below image,
Click on Test Connection button, if connection is successful then click on OK button.
Step 3: Create a Database Context
Now, create a DbContext class (MyDbContext.cs) to manage the database connection and communicate with the database.
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using MyConsoleApplication.Models;
namespace MyConsoleApplication
{
class MyDbContext: DbContext
{
public DbSet<Employees> Employees { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Server=YOUR_SERVER_NAME; Database=DATABASE_NAME; User ID=USER_ID;Password= YOUR_PASSWORD;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Employees>()
.HasKey(e => e.EmpId); // Define the primary key
}
}
}
Note: Replace YOUR_SERVER_NAME,
YourDatabase, User ID,
and Password with actual values in the connection string.
Here the database connection will be established successfully.
Step 4: Define a Model Class Employees.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyConsoleApplication.Models
{
class Employees
{
[Key]
public int EmpId { get; set; }
public string EmpName { get; set; }
public string Gender { get; set; }
public DateTime CreationDate { get; set; }
public Nullable<DateTime> ModificationDate { get; set; }
public decimal Salary { get; set; }
public int DepartmentId { get; set; }
}
}
Now, Perform the different LIKE operators in LINQ to SQL
Create a custom class in your application to perform the different LIKE operators,
Let's see the result of Employees table
using System;
using System.Collections.Generic;
using System.Linq;
using MyConsoleApplication.Models;
namespace MyConsoleApplication
{
class MindStickSoft
{
static void Main()
{
using (var db = new MyCollegeDbContext())
{
var employee = db.Employees.ToList();
foreach (var Emp in employee)
{
Console.WriteLine("Name: {0} \t Gender: {1} \t Salary: {2}", Emp.EmpName, Emp.Gender, Emp.Salary);
}
}
}
}
}
Output:
Name: Ashu Gender: Male Salary: 12410.0000
Name: Priya Shukla Gender: Female Salary: 70788.0000
Name: Ashutosh Verma Gender: Male Salary: 50066.0000
Name: Rani Sharma Gender: Female Salary: 57722.0000
Name: Amit Tiwari Gender: Male Salary: 81391.0000
Name: Ashu Patel Gender: Male Salary: 57659.0000
Name: Samiksha Mishra Gender: Female Salary: 15000.0000
Name: Akanksha Singh Gender: Female Salary: 15000.0000
Name: Shinu Gender: Male Salary: 21450.0000
Name: Tejasvi Raj Gender: Male Salary: 32145.0000
Using Contains() (Equivalent to
LIKE '%value%')
using (var db = new MyCollegeDbContext())
{
var employee = db.Employees.Where(x => x.EmpName.Contains("As")).ToList();
foreach (var Emp in employee)
{
Console.WriteLine("Name: {0} \t Gender: {1} \t Salary: {2}", Emp.EmpName, Emp.Gender, Emp.Salary);
}
}Output:
Name: Ashu Gender: Male Salary: 12410.0000
Name: Ashutosh Verma Gender: Male Salary: 50066.0000
Name: Ashu Patel Gender: Male Salary: 57659.0000
Name: Tejasvi Raj Gender: Male Salary: 32145.0000
Using StartsWith() (Equivalent to
LIKE 'value%')
using (var db = new MyCollegeDbContext())
{
var employee = db.Employees.Where(x => x.EmpName.StartsWith("As")).ToList();
foreach (var Emp in employee)
{
Console.WriteLine("Name: {0} \t Gender: {1} \t Salary: {2}", Emp.EmpName, Emp.Gender, Emp.Salary);
}
}
Output:
Name: Ashu Gender: Male Salary: 12410.0000
Name: Ashutosh Verma Gender: Male Salary: 50066.0000
Name: Ashu Patel Gender: Male Salary: 57659.0000Using EndsWith() (Equivalent to
LIKE '%value')
using (var db = new MyCollegeDbContext())
{
var employee = db.Employees.Where(x => x.EmpName.EndsWith("a")).ToList();
foreach (var Emp in employee)
{
Console.WriteLine("Name: {0} \t Gender: {1} \t Salary: {2}", Emp.EmpName, Emp.Gender, Emp.Salary);
}
}
Output:
Name: Priya Shukla Gender: Female Salary: 70788.0000
Name: Ashutosh Verma Gender: Male Salary: 50066.0000
Name: Rani Sharma Gender: Female Salary: 57722.0000
Name: Samiksha Mishra Gender: Female Salary: 15000.0000
Using Different LIKE Queries in One Code
Here, you will perform multiple like operators simultaneously.
using (var db = new MyCollegeDbContext())
{
var employee = db.Employees.Where(x => x.EmpName.StartsWith("As") || x.EmpName.Contains("As") || x.EmpName.EndsWith("a")).ToList();
foreach (var Emp in employee)
{
Console.WriteLine("Name: {0} \t Gender: {1} \t Salary: {2}", Emp.EmpName, Emp.Gender, Emp.Salary);
}
}
Output:
Name: Ashu Gender: Male Salary: 12410.0000
Name: Priya Shukla Gender: Female Salary: 70788.0000
Name: Ashutosh Verma Gender: Male Salary: 50066.0000
Name: Rani Sharma Gender: Female Salary: 57722.0000
Name: Ashu Patel Gender: Male Salary: 57659.0000
Name: Samiksha Mishra Gender: Female Salary: 15000.0000
Name: Tejasvi Raj Gender: Male Salary: 32145.0000
Best practice: Use Contains(), StartsWith(), and
EndsWith() for pattern matching.
Hope you understand clearly.
Thanks.
Also, read: How to use LINQ to SQL Select Query using C#
Leave Comment