Entity Framework Core-使用FromSqlRaw() 执行原生SQL查询

EF Core FromSqlRaw()方法执行原生SQL 包含参数化查询，这个方返回实体对象

例子1：使用FromSqlRaw()方法执行原生SQL

Employee实体类

public class Employee
{
    public int Id { get; set; }
    public string Department { get; set; }
    public string Name { get; set; }
    public string Designation { get; set; }
}

为了获取开发部门下所有的员工，我们使用FromSqlRaw() 方法执行SQL查询

var employees =_employeeDbContext
                .Employees
                .FromSqlRaw("Select * from Employees where Department = 'Admin'")
                .ToList();

‍

Select * from Employee where Department = ‘Admin’是一个原生SQL查询

例子2：使用FromSqlRaw方法执行参数化查询

下面例子显示如何使用FromSqlRaw 执行参数化查询，查询Tony所有信息

string name = "Tony";
var emp = context.Employee.FromSqlRaw($"Select * from Employee where Name = '{name}'").ToList();

FromSqlRaw 方法中使用LINQ操作

执行完FromSqlRaw ()方法之后，我们可以使用LINQ操作符

下面代码包含了.OrderBy() LINQ 运算符，将查询结果按照员工名称进行升序排列

var emp = _employeeDbContext
                .Employees
                .FromSqlRaw("Select * from Employees")
                .OrderBy(x => x.Name).ToList();

‍

包含关联数据

Include方法能够使用包含关联数据，这里我们在实体中包含关联的Project记录

var employees=_employeeDbContext
                .Employees
                .FromSqlRaw("Select * from Employees where Department = 'Admin'")
                .Include(e=>e.Project).ToList();

这两个实体如下：

public class Employee
{
    public int Id { get; set; }
    public string Department { get; set; }
    public string Name { get; set; }
    public string Designation { get; set; }
    public Project Project { get; set; }
}

public class Project
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Employee> Employee { get; set; }
}

总结

这节我们主要介绍了在EF Core中使用FromSqlRaw()方法执行原生SQL查询

源代码地址

https://github.com/bingbing-gui/Asp.Net-Core-Skill/tree/master/EntityFrameworkCore/EFCoreExecuteRawSql