How to execute SQL query in Entity Framework Core using C#

In Entity Framework Core, you can execute SQL queries using the FromSqlRaw or FromSqlInterpolated methods. Here's a basic guide on how to execute a SQL query using C# in Entity Framework Core:

1. Ceate a Model: Define a model class that represents the structure of the result set from your SQL query.

public class MyModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Add other properties as needed
}

2.Execute Raw SQL Query: Use the FromSqlRaw method to execute a raw SQL query.

using (var context = new DbContext())
{
    var results = context.MyModels.FromSqlRaw("SELECT Id, Name FROM Table WHERE Condition").ToList();
}

Replace DbContext, MyModels, Table, and Condition with your actual DbContext class, model, table name, and condition.

3.Using Parameters in SQL Query: If your SQL query involves parameters, use the FromSqlInterpolated method to pass parameters safely.

using (var context = new DbContext())
{
    string condition = "Condition";
    var results = context.MyModels
        .FromSqlInterpolated($"SELECT Id, Name FROM YourTable WHERE Column = {condition}")
        .ToList();
}

Ensure that you use parameterized queries to prevent SQL injection. Mapping Result to a ViewModel: If the columns in your SQL query do not exactly match the properties in your model, you may want to create a view model and project the results accordingly.

public class MyViewModel
{
    public int Identifier { get; set; }
    public string DisplayName { get; set; }
}

// ...

using (var context = new DbContext())
{
    var results = context.MyModels
        .FromSqlRaw("SELECT Id as Identifier, Name as DisplayName FROM YourTable WHERE YourCondition")
        .Select(x => new MyViewModel { Identifier = x.Id, DisplayName = x.Name })
        .ToList();
}

Adjust the property names and types in the view model according to your needs. Remember to handle the SQL query and results carefully, especially if they involve user input, to prevent security issues like SQL injection. Always consider the performance implications of raw SQL queries compared to using Entity Framework's LINQ queries.

Let's explore an example of how to execute an SQL query to retrieve product details using ProductDbContext in Entity Framework Core using C#, step by step. To execute an SQL query for getting product details using ProductDbContext in Entity Framework Core using C#, you can use the FromSqlRaw or FromSqlInterpolated methods. Assuming you have a Product model:

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
    // Add other properties as needed
}

Using FromSqlRaw:

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

public class ProductDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    // Other DbSet properties and configurations

    public List<Product> GetProductsByCategory(string category)
    {
        var sql = $"SELECT * FROM Products WHERE Category = '{category}'";
        return Products.FromSqlRaw(sql).ToList();
    }
}

Make sure to replace ProductDbContext with the actual name of your DbContext class, and adjust the SQL query according to your needs. Using FromSqlInterpolated:

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

public class ProductDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    // Other DbSet properties and configurations

    public List<Product> GetProductsByCategory(string category)
    {
        var sql = $"SELECT * FROM Products WHERE Category = {category}";
        return Products.FromSqlInterpolated(FormattableStringFactory.Create(sql)).ToList();
    }
}

This approach is similar to FromSqlRaw, but it uses FromSqlInterpolated. Choose the method that fits your preference and adjust the SQL query based on your specific requirements.

How to execute SQL query in Entity Framework Core using C#

Read Similar Articles