EF 6 - Truy vấn Raw SQL

Thực hiện các truy vấn SQL trong Entity Framework

Entity Framework cho phép bạn thực hiện các truy vấn SQL cho cơ sở dữ liệu quan hệ.

Các phương thức sau đây có thể được sử dụng để thực thi các truy vấn SQL vào cơ sở dữ liệu bằng Entity Framework 6.x:

  • DbSet.SqlQuery()
  • DbContext.Database.SqlQuery()
  • DbContext.Database.ExecuteSqlCommand()

DbSet.SqlQuery()

Sử dụng phương thức DbSet.SqlQuery() để viết các truy vấn SQL trả về các thể hiện của thực thể. Các thực thể trả về sẽ được theo dõi bởi Context, như thể chúng được trả về bởi truy vấn LINQ.

using (var ctx = new SchoolDBEntities())
{
    var studentList = ctx.Students
                         .SqlQuery("Select * from Students")
                         .ToList<Student>();
}

Truy vấn trên thực thi câu lệnh SQL Select * from Students trong cơ sở dữ liệu để trả về tất cả sinh viên và được chuyển đổi thành một danh sách các thực thể Student. Tên cột trong truy vấn SQL phải khớp với các thuộc tính của kiểu thực thể, nếu không nó sẽ đưa ra một ngoại lệ.

Bạn có thể chỉ định các tham số bằng cách sử dụng đối tượng SqlParameter, như sau:

using (var ctx = new SchoolDBEntities())
{
    var student = ctx.Students
        .SqlQuery("Select * from Students where StudentId=@id", 
            new SqlParameter("@id", 1))
        .FirstOrDefault();
}

Nếu bạn thay đổi tên cột trong truy vấn SQL, thì nó sẽ đưa ra một ngoại lệ vì nó phải khớp với tên cột. Ví dụ sau đây sẽ đưa ra một ngoại lệ.

using (var ctx = new SchoolDBEntities())
{                
    //this will throw an exception
    var studentName = ctx.Students.SqlQuery("Select studentid as id, studentname as name from Student where studentname='Steve'").ToList();
}

Phương thức DbSet<TEntity>.SqlQuery() thực hiện truy vấn SQL chỉ dành cho bảng được ánh xạ với thực thể được chỉ định (ví dụ: DbSet<Student>.SqlQuery() chỉ trả về kết quả từ bảng Students tương ứng chứ không phải từ bất kỳ bảng nào khác). Sau đây sẽ ném một ngoại lệ.

using (var ctx = new SchoolDBEntities())
{                
    //this will throw an exception
    var studentName = ctx.Students.SqlQuery("Select * from Courses").ToList();
}

Database.SqlQuery()

Lớp Database đại diện cho cơ sở dữ liệu và cung cấp phương thức khác nhau để truy vấn cơ sở dữ liệu. Phương thức Database.SqlQuery() trả về một giá trị của bất kỳ kiểu dữ liệu nào.

using (var ctx = new SchoolDBEntities())
{
    //Get student name of string type
    string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=1")
                            .FirstOrDefault();

    //or
    string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=@id", new SqlParameter("@id", 1))
                            .FirstOrDefault();
}

Database.ExecuteSqlCommand()

Phương thức Database.ExecuteSqlCommnad() rất hữu ích trong thực hiện các lệnh Insert, Update và Delete.

using (var ctx = new SchoolDBEntities())
{
    int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student 
            set studentname ='changed student by command' where studentid=1");

    int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) 
            values('New Student')");

    int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student 
            where studentid=1");
}