Поиск  
Always will be ready notify the world about expectations as easy as possible: job change page
May 14, 2023

How to convert a SQL query into C# LINQ

Автор:
Edwin Klesman
Источник:
Просмотров:
6640

SQL into LINQ

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:

  1. 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 EntityFramework
  2. Create 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<T> 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.

SQL query into LINQ
Hooray! You converted SQL 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 🔥

Похожее
Feb 3, 2022
Author: Satish Chandra Gupta
What if programming languages were stocks? And you had to make a portfolio to fetch good returns in 2022?You probably have seen various surveys and analyses listing the most popular programming languages. Those cover the universe of programming languages, like...
18 января 2023 г.
Автор: Savindu Bandara
What is paginationSo you may have already used this one, but you may be wondering what this pagination 😀. So as in figure 1, pagination helps you break a large number of datasets into smaller pages. For example, a storybook...
Apr 11, 2023
Nowadays, everybody so concerned about DDD and how to implement business logic properly that people just forget about existence of other layers. Bada-bing bada-boom, other layers do exist.Shocked, don’t ya? Take your sit, you will be even more shocked when...
May 8, 2023
Author: Waqas Ahmed
Dapper is a lightweight ORM (Object-Relational Mapping) framework for .NET Core and is commonly used to query databases in .NET Core applications. Here are some of the advanced features of Dapper in .NET Core: Multi-Mapping: Dapper allows...
Написать сообщение
Почта
Имя
*Сообщение


© 1999–2024 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET Framework | .NET Core | C# | ASP.NET | Windows Forms | WPF | HTML5 | CSS3 | jQuery | AJAX | Angular | React | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban