What is LINQ to SQL?

LINQ to SQL allows querying relational databases (like SQL Server) using C# LINQ queries instead of raw SQL. It maps database tables to C# classes using an Object Relational Mapper (ORM).

 

How to Establish a Connection to SQL Server for LINQ Queries in C#?

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.Tools

 

Step 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, 

 

 

if Test Connection success then click on OK.

 

Step 3: Create a Database Context

Create a DbContext class to manage the database connection.

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
       }
   }
}

Replace YOUR_SERVER_NAME, YourDatabase, User ID, and Password with actual values.

 

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; }
   }
}

 

Step 5: Perform LINQ Queries

 

using System;
using System.Collections.Generic;
using System.Linq;
using MyConsoleApplication.Models;
namespace MyConsoleApplication
{
   class MindStickSoft
   {
       static void Main()
       {
           using (var db = new MyDbContext())
           {
               var employee = from emp in db.Employees
                              select emp;
               foreach (var Emp in employee)
               {
                   Console.WriteLine("Name: {0} Gender: {1}", Emp.EmpName, Emp.Gender);                    
               }
           }
       }
   }
}

 

Output

Name: Ashu Gender: Male
Name: Priya Shukla Gender: Female
Name: Ashutosh Verma Gender: Male
Name: Rani Sharma Gender: Female
Name: Amit Tiwari Gender: Male
Name: Ashu Patel Gender: Male
Name: Samiksha Mishra Gender: Female
Name: Akanksha Singh Gender: Female
Name: Shinu Gender: Male
Name: Tejasvi Raj Gender: Male

 

Your can perform more LINQ to SQL select function like given below,

Using Where with Select

 var employee = from emp in db.Employees
                   where emp.Salary > 10000
                   select emp;
         foreach (var Emp in employee)
          {
             Console.WriteLine("Name: {0} Gender: {1}", Emp.EmpName, Emp.Gender);                    
          }

 

Using Select with OrderBy

var employee = from emp in db.Employees
                           orderby emp.EmpName
                           select emp;
           foreach (var Emp in employee)
           {
               Console.WriteLine("Name: {0} Gender: {1}", Emp.EmpName, Emp.Gender);                    
           }

 

and so on….

 

I hope you understand clearly.

Thanks

 

Also, Read: Connected vs Disconnected Architecture in ADO.NET