SQL (Structured Query Language) and LINQ (Language Integrated Query) represent two of the most effective ways to manage and manipulate data. SQL serves as a straightforward technique for structuring data within databases, while LINQ is a feature exclusive to .NET languages such as C# that allows querying data directly from the codebase.

If you’re already accustomed to crafting SQL statements and wish to understand how to translate them into LINQ, then this guide will be beneficial for you.

Table of Contents

• Comparison Between SQL and LINQ
• How to Transform SQL Query into LINQ Query?
  • Using Method
  • Using Query
• Summary

Comparison Between SQL and LINQ

How to Transform SQL Query into LINQ Query?

In order to obtain information from a database in C#, it is necessary to convert SQL queries into LINQ. There are two approaches to achieve this in C#: method syntax and query syntax. Below is the LINQ representation of your SQL query, provided that you have the necessary entity classes and a DbContext configured for Entity Framework.

1. Using Method

Here’s an illustration of transforming an SQL query into a LINQ query through Method Syntax in C#:

SQL Query:

SELECT * FROM Customers WHERE Country = 'USA' AND City = 'New York';

LINQ Equivalent (Method Syntax):

using (var context = new MyDbContext())
{
    var customers = context.Customers
        .Where(c => c.Country == "USA" && c.City == "New York")
        .ToList();

    foreach (var customer in customers)
    {
        Console.WriteLine($"Name: {customer.Name}, City: {customer.City}");
    }
}

Clarification:

This LINQ query retrieves customers from the database where the Country is “USA” and the City is “New York,” akin to an SQL WHERE clause. It subsequently converts the filtered records into a list using .ToList() and iterates through them to present the customer details.

2. Using Query

Here’s a straightforward illustration of transforming an SQL query into a LINQ query utilizing the Query Syntax.

We possess a database table called Customers comprising the following columns: CustomerID, Name, City, and Country. Our goal is to extract all customers located in “New York” who hail from the “USA”

SQL Query:

SELECT CustomerID, Name, City, Country

FROM Customers

WHERE City = 'New York' AND Country = 'USA'

LINQ Query in C#

using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        // List of customers
        List<Customer> customers = GetCustomers();  // Simulating data source

        // LINQ query to filter customers in New York and USA
        var query = from customer in customers
                    where customer.City == "New York" && customer.Country == "USA"
                    select customer;

        // Displaying the results
        foreach (var customer in query)
        {
            Console.WriteLine($"CustomerID: {customer.CustomerID}, Name: {customer.Name}, City: {customer.City}, Country: {customer.Country}");
        }
    }

    // Simulated customer data
    static List<Customer> GetCustomers()
    {
        return new List<Customer>
        {
            new Customer { CustomerID = 1, Name = "John Doe", City = "New York", Country = "USA" },
            new Customer { CustomerID = 2, Name = "Jane Smith", City = "Los Angeles", Country = "USA" },
            new Customer { CustomerID = 3, Name = "Samuel Jackson", City = "New York", Country = "USA" },
            new Customer { CustomerID = 4, Name = "Carlos Gomez", City = "Madrid", Country = "Spain" }
        };
    }
}

class Customer
{
    public int CustomerID { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

Output

Clarification

The LINQ query effectively filters customers located in “New York” and originating from the “USA,” in a manner similar to the SQL query’s functionality.

Summary

In this article, we have explored SQL Queries and LINQ queries, their differences, and the methods used to convert SQL queries into LINQ queries. If you’re keen on furthering your knowledge about these techniques, consider enrolling in our extensive SQL Course.

This article Convert SQL to LINQ Query was first published on Intellipaat Blog.