Entity Framework Tutorial Basics(39):Raw SQL Query
Execute Native SQL Query
You can execute native raw SQL query against the database using DBContext. You can execute the following types of queries:
- SQL query for entity types which returns particular types of entities
- SQL query for non-entity types which returns a primitive data type
- Raw SQL commands to the database
SQL query for entity types:
As we have seen in one of the previous chapters, DBSet has SQLQuery() method to write raw SQL queries which return entity instances. The returned objects will be tracked by the context, just as they would be if they were returned by a LINQ query. For example:
using (var ctx = new SchoolDBEntities())
{
var studentList = ctx.Students.SqlQuery("Select * from Student").ToList<Student>(); }
However, columns returned by SQL query should match the property of an entity type of DBSet otherwise, it will throw an exception. For example:
using (var ctx = new SchoolDBEntities())
{
var studentName = ctx.Students.SqlQuery("Select studentid, studentname
from Student where studentname='New Student1'").ToList(); }
If you change the column name in query, then it will throw an exception because it must match column names:
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='New Student1'").ToList();
}
SQL query for non-entity types:
A SQL query returning instances of any type, including primitive types, can be created using the SqlQuery method on the Database class. For example:
using (var ctx = new SchoolDBEntities())
{
//Get student name of string type
string studentName = ctx.Database.SqlQuery<string>("Select studentname
from Student where studentid=").FirstOrDefault<string>();
}
Raw SQL commands to the database:
ExecuteSqlCommnad method is useful in sending non-query commands to the database, such as the Insert, Update or Delete command. For example:
using (var ctx = new SchoolDBEntities())
{ //Update command
int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student
set studentname ='changed student by command' where studentid=");
//Insert command
int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname)
values('New Student')");
//Delete command
int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student
where studentid="); }
最新文章
- mysql_connect() php7不支持,php5.5可以,是废弃函数
- TP框架,根据当前应用状态对应的配置文件
- Linux 指令
- [jQuery学习系列四 ]4-Jquery学习四-事件操作
- Mysql学习笔记(十一)临时表+视图
- placeholder在ie浏览器里不显示的问题解决
- ognl--数据运转的催化剂
- 通过LOGBACK实现每个类、包或自定义级别
- Elasticsearch 创建以及修改索引结构
- nginx比apache处理静态文件速度快,但是nginx处理大量并发的php请求时,容易出现502错误,频率大概是多少
- 磁盘操作系统 cmd命令
- 卓越研发之路 MOT技术管理者课堂
- 【AtCoder】AGC024
- linux下配置SS5(SOCK5)代理服务
- hdu2036(多边形面积)
- PPTP协议握手流程分析--转载
- linux 批量创建用户获取8位随机密码
- 关闭pm2
- Number 和 parseInt 区别
- 基于EasyNVR实现RTSP/Onvif监控摄像头Web无插件化直播监控