Convert SQL to LINQ Query

SQL (Structured Query Language) and LINQ (Language Integrated Query) are two of the best methods to work with data. SQL is a simple method to organize data in databases and LINQ is a feature specific to .NET languages like C# that enables you to query your data straight from code.

If you are already familiar with writing SQL statements and are interested in how we can convert the same in LINQ then this guide will be useful to you.

Table of Contents

• Difference Between SQL and LINQ
• How to Convert SQL Query to LINQ Query?
  • By using Method
  • By using Query
• Conclusion

Difference Between SQL and LINQ

How to Convert SQL Query to LINQ Query?

To get database information in C#, we need to convert SQL queries to LINQ. There are two ways to do this in C#: method syntax and query syntax. Below is the LINQ version of your SQL query, assuming you have the required entity classes and a DbContext set up for Entity Framework.

1. Using Method

Here’s an example of converting an SQL query to a LINQ query using 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}");
    }
}

Explanation:

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

2. Using Query

Here’s a simple example of converting an SQL query to a LINQ query using the Query Syntax.

We have a database table Customers with the following columns: CustomerID, Name, City, and Country. We want to retrieve all customers from “New York” who are 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

Explanation

The LINQ query successfully filters the customers who are in “New York” and from the “USA,” similar to how the SQL query works.

Conclusion

So far in this blog, we have learned what SQL Queries and LINQ queries are, what is the difference between them, and how we can convert SQL queries to LINQ queries with different methods. If you are interested in learning more about these techniques, then you should check our comprehensive SQL Course.