In this article, I’ll show you what the basic steps are for converting a SQL query into LINQ. You’ll learn the basic steps needed while we convert an example query.
In this article, it's assumed that you have a basic understanding of SQL, and know how to write C# code.
Introduction
Structured Query Language (SQL) is a powerful language for working with relational databases. It is widely used to retrieve and manipulate data from a variety of databases. However, when it comes to working with data in C# code, Language Integrated Query (LINQ) is a popular alternative to SQL. In this article, we will show how to manually convert a SQL query into LINQ, step-by-step.
Example SQL Query
Let’s begin with an example SQL query that includes a join, a couple of where conditions, and ordering. Here is the query:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.ShippedDate IS NULL AND Orders.Freight > 100
ORDER BY Orders.OrderDate DESC
Step-by-Step Conversion to LINQ
Let’s begin with the overall steps that you need to perform when converting your SQL into LINQ.
Step 1: Analyse The SQL Query
When you have selected the query you need to convert into LINQ, it makes sense that you at least understand how the SQL is put together.
I often do this — with SQL not written by me — by walking through the query from top to bottom and marking anything that’s unclear. I tend to use Visual Studio Code and SQL comments for this:
-- just writing a comment above the lines that are unclear
Then I’d look up / Google any functions or constructions that aren’t clear to me, so I can get a grasp of the functionality.
When I know what the query does functionally, I write down the query’s functionality in my own words. For example, for our example query the explanation would be:
The query retrieves the OrderID, CustomerName, and OrderDate from the Orders and Customers tables, where the ShippedDate is null and the Freight is greater than 100. The results are ordered by the OrderDate in descending order.
This results in a list of Orders from new to old, that haven’t been shipped yet and have a freight amount of at least 100.
Step 2: Using A Data Context Class
Depending on your needs, and what is already implemented in your solution, you might need to create a data context class. A data context class provides a connection to the database and maps database objects to C# classes.
A data context class can be created manually by you, or when you’re using Entity Framework (EF), you can generate a *data context class *using that.
Creating a data context class
Here is an example of a data context class coded as the representation of a database that our example query would use:
public class NorthwindDataContext : DbContext
{
public DbSet<Order> Orders { get; set; }
public DbSet<Customer> Customers { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Northwind");
}
}
This data context class represents a database called “Northwind” and includes two entities: Order and Customer (each entity is typically a table in your database). We also specify the connection string for the database.
Using Entity Framework (EF)
Here are the steps to use Entity Framework to generate a data context class and use it to query data with LINQ:
Install Entity Framework: If you haven’t already done so, install Entity Framework by adding the EntityFramework package to your project. You can do this by opening the NuGet Package Manager Console in Visual Studio and running the following command:
Install-Package EntityFrameworkCreate a data model: Create a class that represents your database schema, also known as a data model. You can create this class by using the Entity Framework Designer in Visual Studio OR by writing code manually. Here is an example of how to define a simple data model for a Northwind database:
public class NorthwindDataContext : DbContext
{
public DbSet<Customer> Customers { get; set; }
public DbSet<Order> Orders { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Customer>()
.HasKey(c => c.CustomerID);
modelBuilder.Entity<Order>()
.HasKey(o => o.OrderID);
modelBuilder.Entity<Order>()
.HasRequired<Customer>(o => o.Customer)
.WithMany(c => c.Orders)
.HasForeignKey(o => o.CustomerID);
}
}
public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Region { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
public virtual ICollection<Order> Orders { get; set; }
}
public class Order
{
public int OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime OrderDate { get; set; }
public DateTime? ShippedDate { get; set; }
public decimal Freight { get; set; }
public virtual Customer Customer { get; set; }
}
Step 3: Creating the LINQ code
To retrieve data with LINQ, we need to interact with the data context class, depending on the manual or EF flavor, there are minor differences in the code (which I’ll illustrate using comments).
So let’s use the DbSet property of the data context class. I usually go from top to bottom in the SQL query, and write down the equivalent in LINQ using auto-completion to find any options.
In our example, we want to retrieve the OrderID, CustomerName, and OrderDate from the Orders and Customers tables. We can do this with the following LINQ query:
using (var context = new NorthwindDataContext())
{
var query = from order in context.Orders
join customer in context.Customers on order.CustomerID equals customer.CustomerID
where order.ShippedDate == null && order.Freight > 100
orderby order.OrderDate descending
select new { order.OrderID, customer.CustomerName, order.OrderDate };
var result = query.ToList();
}
This LINQ query uses a join to combine the Orders and Customers tables. It includes two where conditions to filter the results and an orderby clause to order the results.
Finally, it selects the OrderID, CustomerName, and OrderDate into an anonymous type. All similar to our input query.
An advantage of using ORM features
One optimization that you can make when you’re using an ORM like Entity Framework, is that you can use so-called navigation properties, which saves you a “join” statement. For example:
var query = from order in context.Orders
where order.ShippedDate == null
select new
{
order.OrderID,
order.Customer.CustomerName,
order.OrderDate
};
In this LINQ query, we’re selecting the OrderID, CustomerName, and OrderDate properties from the Orders entity, using the Customer navigation property to get the associated CustomerName.
By using the navigation property instead of an explicit Join, the ORM can generate the appropriate SQL query behind the scenes, optimizing the query execution and simplifying the code for the developer.
Step 4: Compiling & Executing the LINQ query
With LINQ, the query is generated and setup, but only executed when you actually perform an action on it.
As visible in the LINQ example from step 3, the result variable will only get the information after a method like “ToList()” is performed on the LINQ query.
By setting a breakpoint on this line, you can compile and execute the query and check if this works properly.
Any typos or references to non-existing properties or entities should also have been long signalled by your IDE, as type-checking and compile time checks really form a nice way to filter out human mistakes.
Step 5: Validating the results
It is only common sense that, after you’ve setup your LINQ code AND verified that it compiles into proper code, you will test if the outcomes of the SQL and the LINQ are similar.
So step 5 is the testing phase, in which you typically could follow these steps to verify that your LINQ is giving the expected results:
Test step 1: Execute the SQL query: First, execute the SQL query in your database management tool, such as SQL Server Management Studio or MySQL Workbench. This will give you a result set that you can use for comparison.
Test step 2: Capture the SQL query: Next, capture the SQL query generated by LINQ. You can do this by inspecting the ToString() output of the LINQ query, as we did earlier:
var query = from order in context.Orders
join customer in context.Customers on order.CustomerID equals customer.CustomerID
where order.ShippedDate == null && order.Freight > 100
orderby order.OrderDate descending
select new { order.OrderID, customer.CustomerName, order.OrderDate };
string sql = query.ToString();
//This was ToList() before, but now results in a string
//that you can get with a breakpoint on this line
Test step 3: Execute the LINQ query: Finally, execute the LINQ query and compare the result set to the one obtained from executing the SQL query. You can use a tool like LINQPad to execute the LINQ query and inspect the results.
using (var context = new NorthwindContext())
{
var query = from order in context.Orders
join customer in context.Customers on order.CustomerID equals customer.CustomerID
where order.ShippedDate == null && order.Freight > 100
orderby order.OrderDate descending
select new { order.OrderID, customer.CustomerName, order.OrderDate };
var result = query.ToList();
// Compare the result set to the SQL query result set
// ...
}
To compare the result sets, you can use a tool like Beyond Compare or WinMerge to compare the data in the two result sets. Alternatively, you can write a small program to iterate over the result sets and compare each row.
Keep in mind that there may be small differences in the ordering of the rows or the formatting of the data, so you may need to account for these differences when comparing the results. In addition, if you are using different database engines for the SQL query and the LINQ query, there may be differences in the way that data is stored and retrieved, so you should be aware of any such differences.
Overall, testing the SQL and LINQ output for equivalence is an important step in verifying the correctness of your LINQ queries, and can help you catch errors early in the development process.
Also, don’t forget to check if the query is at least equally fast to the original SQL version (although, in my experience, LINQ is often faster because LINQ generates better and faster queries with each new release).
That’s it! We have manually converted the SQL query into LINQ.
To conclude
In this article, we have shown how to manually convert a SQL query into LINQ.
We started with an example SQL query that included a join, a couple of where conditions, and ordering. We then demonstrated each step in the conversion process:
Step 1: analysing the SQL
Step 2: Using a data context class
Step 3: Creating the LINQ code
Step 4: Compiling & Executing the LINQ query
Step 5: Validating the results
From creating analysing the SQL, to creating a data context class to executing and testing the LINQ query, the entire procedure would take about 15 to 80 minutes, depending on your familiarity with LINQ and the complexity of the query.
If you’d ever need a faster way that would save you time to do this manually, check out an online tool that I created: LINQ Me Up. It is a conversion tool that empowers the power of AI to generate C# LINQ code from your SQL input in a minute (or a couple, depending on the size of your SQL query).
Thanks for reading my conversion guide, and I hope that it at minimum gave you some insights on how to go from SQL to C# LINQ.
Code hard. Ship harder 🔥
Top comments (0)