SQL to LINQ Conversion Guide for Developers

Web Development
January 30, 2025
Share this post

Introduction

This guide is designed to help developers convert SQL queries and procedures to LINQ (Language IntegratedQuery) in .NET applications. LINQ provides a way to interact with databases ina strongly-typed, readable, and maintainable manner, reducing dependency on database-specific logic like stored procedures and cursors.

Steps to Convert a SQL Query to LINQ

1. Analyze the SQL Query:

      Understand the structure of the query, including tables, joins, filters, grouping, and ordering.

2. Create Entity Models:

      Ensure that each table used in the query is represented by an entity model.

      Use Entity Framework (EF) to generate models from the database schema if needed.

3. Map SQL Joins and Conditions:

      Convert SQL joins to LINQ joins or use navigational properties if relationships are defined.

      Translate WHERE, GROUP BY, HAVING, and ORDER BY clauses toLINQ methods like .Where(), .GroupBy(), and .OrderBy().

4. Write the LINQ Query:

      Construct the LINQ query using entity models.

      Use LINQ method chaining to replicate the query logic 

5. Test and Optimize:

      Verify that the LINQ query returns the same results as theSQL query.

      Optimize performance by using projections (.Select()) tolimit data retrieval.

6. Integrate with API:

      Create a service or repository class to handle the LINQ query.

      Use dependency injection to provide the service to your API controller.

Example Conversion with API Integration

SQL Query Example

SELECT *
FROM Customers
WHERE City = 'London'
ORDER BY Name;

Entity Model

This defines the structure of a table from the database in the form of a C# class. Properties in the class map to the table columns, and navigational properties allow entity relationships to be represented. For example, Orders in the Customer class below indicates a one-to-many relationship between customers and their orders.

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public List<Order> Orders { get; set; }  // Navigational property
}

Service Class

The service class encapsulates data access logic using Entity Framework. It interacts with the database context to retrieve, insert, update, or delete data. By abstracting database logic here, we achieve separation of concerns, making the codebase more maintainable and testable.

public class CustomerService
{
    private readonly ChinookDbContext _context;

    public CustomerService(ChinookDbContext context)
    {
        _context = context;
    }

    public List<Customer> GetCustomersByCity(string city)
    {
        return _context.Customers
            .Where(c => c.City == city)
            .OrderBy(c => c.Name)
            .ToList();
    }
}

API Controller

The API controller handles incomingHTTP requests and returns appropriate responses. It uses dependency injection to call methods from the service class. Each action method in the controller corresponds to an endpoint, such as retrieving customers by city. The controller returns HTTP status codes like 200 OK or 404 Not Found based on the data retrieved.

[ApiController]
[Route("api/[controller]")]
public class CustomersController : ControllerBase
{
    private readonly CustomerService _customerService;

    public CustomersController(CustomerService customerService)
    {
        _customerService = customerService;
    }

    [HttpGet("city/{city}")]
    public IActionResult GetCustomersByCity(string city)
    {
        var customers = _customerService.GetCustomersByCity(city);
        if (customers == null || !customers.Any())
        {
            return NotFound();
        }
        return Ok(customers);
    }
}

‍

Joins

SQL Inner Join Example

SELECT Customers.Name, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerId = Orders.CustomerId;

LINQ Equivalent

var customerOrders = context.Customers
    .Join(context.Orders,
          customer => customer.CustomerId,
          order => order.CustomerId,
          (customer, order) => new { customer.Name, order.OrderDate })
    .ToList();

‍

Grouping and Aggregation

SQL Group By Example

SELECT CustomerId, COUNT(OrderId) AS TotalOrders
FROM Orders
GROUP BY CustomerId
HAVING COUNT(OrderId) > 5;

LINQ Equivalent

var customerOrderCounts = context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new {
        CustomerId = g.Key,
        TotalOrders = g.Count()
    })
    .Where(c => c.TotalOrders > 5)
    .ToList();

‍

Using Cursors in SQL vs LINQ Iteration

SQL Cursor Example

DECLARE @CustomerId INT;
DECLARE CustomerCursor CURSOR FOR
SELECT CustomerId FROM Customers;

OPEN CustomerCursor;
FETCH NEXT FROM CustomerCursor INTO @CustomerId;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Processing customer ' + CAST(@CustomerId AS VARCHAR(10));
    FETCH NEXT FROM CustomerCursor INTO @CustomerId;
END;

CLOSE CustomerCursor;
DEALLOCATE CustomerCursor;

LINQ Equivalent

var customers = context.Customers.ToList();

foreach (var customer in customers)
{
    Console.WriteLine($"Processing customer {customer.CustomerId}");
}

‍

Nested Cursors Example

SQL Nested Cursor

DECLARE @CustomerId INT;
DECLARE @OrderId INT;

DECLARE OuterCursor CURSOR FOR
SELECT CustomerId FROM Customers;

OPEN OuterCursor;
FETCH NEXT FROM OuterCursor INTO @CustomerId;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE InnerCursor CURSOR FOR
    SELECT OrderId FROM Orders WHERE CustomerId = @CustomerId;

    OPEN InnerCursor;
    FETCH NEXT FROM InnerCursor INTO @OrderId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Processing order ' + CAST(@OrderId AS VARCHAR(10));
        FETCH NEXT FROM InnerCursor INTO @OrderId;
    END;

    CLOSE InnerCursor;
    DEALLOCATE InnerCursor;

    FETCH NEXT FROM OuterCursor INTO @CustomerId;
END;

CLOSE OuterCursor;
DEALLOCATE OuterCursor;

LINQ Equivalent

var customerOrders = context.Customers
    .Select(customer => new {
        CustomerId = customer.CustomerId,
        Orders = context.Orders
            .Where(order => order.CustomerId == customer.CustomerId)
            .Select(order => order.OrderId)
            .ToList()
    })
    .ToList();

foreach (var customer in customerOrders)
{
    Console.WriteLine($"Processing customer {customer.CustomerId}");

    foreach (var orderId in customer.Orders)
    {
        Console.WriteLine($"Processing order {orderId}");
    }
}

‍

Insert, Update, and Delete Operations

SQL Insert Example

INSERT INTO Customers (Name, City)
VALUES ('John Doe', 'London');

LINQ Equivalent

var newCustomer = new Customer { Name = "John Doe", City = "London" };
context.Customers.Add(newCustomer);
context.SaveChanges();

SQL Update Example

UPDATE Customers
SET City = 'Manchester'
WHERE Name = 'John Doe';

LINQ Equivalent

var customer = context.Customers.FirstOrDefault(c => c.Name == "John Doe");
if (customer != null)
{
    customer.City = "Manchester";
    context.SaveChanges();
}

SQL Delete Example

DELETE FROM Customers
WHERE Name = 'John Doe';

LINQ Equivalent

var customer = context.Customers.FirstOrDefault(c => c.Name == "John Doe");
if (customer != null)
{
    context.Customers.Remove(customer);
    context.SaveChanges();
}

‍

Case Study: SQL to LINQ Migration in a Real-World Application

Background

A retail company used a complex SQL query to generate a report that aggregated sales data across multiple stores.The query involved several joins, grouping, and filtering conditions to produce a summary of total sales by store and product category. The company wanted to move away from stored procedures and integrate the query logic directly into their .NET application using LINQ.

Original SQL Query

SELECT s.StoreName, c.CategoryName, SUM(o.Quantity * o.UnitPrice) AS TotalSales
FROM Stores s
INNER JOIN Orders o ON s.StoreId = o.StoreId
INNER JOIN Products p ON o.ProductId = p.ProductId
INNER JOIN Categories c ON p.CategoryId = c.CategoryId
WHERE o.OrderDate BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY s.StoreName, c.CategoryName
ORDER BY TotalSales DESC

Entity Models

public class Store
{
    public int StoreId { get; set; }
    public string StoreName { get; set; }
    public List<Order> Orders { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int StoreId { get; set; }
    public int ProductId { get; set; }
    public DateTime OrderDate { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public Store Store { get; set; }
    public Product Product { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    public List<Product> Products { get; set; }
}

Converted LINQ Query

var salesReport = context.Stores
    .Select(store => new
    {
        StoreName = store.StoreName,
        CategorySales = store.Orders
            .Where(order => order.OrderDate >= new DateTime(2024, 1, 1) && order.OrderDate <= new DateTime(2024, 12, 31))
            .GroupBy(order => order.Product.Category.CategoryName)
            .Select(group => new
            {
                CategoryName = group.Key,
                TotalSales = group.Sum(order => order.Quantity * order.UnitPrice)
            })
            .OrderByDescending(category => category.TotalSales)
    })
    .ToList();

Results

The LINQ query produced the same output as the original SQL query. By integrating it into the .NET application, the company achieved:

  • Improved Maintainability: Developers could modify the query logic without updating database scripts.
  • Strong Typing: Compile-time checks helped  catch errors early.

Lessons Learned

  1. Define Relationships Early: Setting up proper entity relationships simplifies LINQ queries.
  2. Test with Real Data: Ensure LINQ queries are tested against production-like data to validate performance.
  3. Optimize Query Execution: Use .AsNoTracking() for read-only operations and projections to reduce memory usage.

‍

Advantages of LINQ over SQL Cursors and Queries

  1. Readability and Maintainability: LINQ queries are integrated within the application, making them easier to read and modify.
  2. Strong Typing: LINQ provides compile-time type checking, reducing runtime errors.
  3. Deferred Execution: LINQ queries are executed only when enumerated, optimizing performance.
  4. Reduced Boilerplate Code: LINQ eliminates the need for manual cursor management and repetitive SQL operations.
  5. Debugging: LINQ can be debugged using C# debugging tools.

‍

Summary

This guide provided examples and best practices for converting SQL queries, joins, grouping, and cursors to LINQ. By using LINQ, developers can improve code maintainability, reduce database dependency, and leverage the full power of .NET for data manipulation.

Read and Share

Stay Updated with Our Newsletter

Subscribe to our newsletter for the latest updates.

By joining, you agree to our Terms and Conditions.
Thank you! You're now subscribed.
Oops! Something went wrong. Please try again.