EF6学习笔记十：原始查询，在EF中使用SQL语句

EF6学习笔记⼗：原始查询，在EF中使⽤SQL语句
EF⾥⾯当然也可以直接使⽤SQL语句了，⽐如有些复杂的查询⽤LINQ写不了的，还有存储过程那些东西。

EF为查询操作提供了两个⽅法：ctx.Database.SqlQuery<T>()、ctx.DbSet<T>.SqlQuery() （ctx表⽰上下⽂对象）
为Insert、Update、Delete 操作提供了两个⽅法：ExecuteSqlCommand()、ExecuteSqlCommandAsync()
我们来弄⼀弄这些⽅法，看看怎么回事
原始查询
ctx.Database.SqlQuery<T>() 和 ctx.Dbset<T>.SqlQuery() 两个⽅法的区别，最先要说的就是，ctx.Database.SqlQuery<T>()查询出的数据没有被上下⽂追踪，另⼀个⽅法查询出
的实体则被追踪了
来看ctx.Database.SqlQuery<T>() 查询出实体的状态为Detached
using(EFDbContext ctx = new EFDbContext)
{
var res = ctx.Database.SqlQuery<Product>("select *from tb_products");
//var state = ctx.Entry(res).State; // 报错实体 DbRawSqlQuery不是上下⽂模型的⼀部分
var first = res.FirstOrDefault();
var state = ctx.Entry(first).State;
Console.WriteLine(state); // Deteched
}
View Code
来看ctx.Dbset<T>.SqlQuery() 实体状态为Unchanged
var res = ctx.Products.SqlQuery("select * from tb_products");
var pro = res.FirstOrDefault();
var state = ctx.Entry(pro).State;
Console.WriteLine(state); // Unchanged
View Code
这是他们之间的第⼀个区别，不过这个倒没什么⼤碍，对吧。

即使不被跟踪，我也可以调⽤Attach⽅法对它进⾏追踪
查询指定列的数据（⼤于1，⼩于总列数）
这两个⽅法不⽀持查询指定某⼏列的数据，必须要所有列的数据
来看SqlQuery<T>()
var res = ctx.Database.SqlQuery<Product>("select id,name from tb_products");
var product = res.FirstOrDefault();
// 报错：System.Data.Entity.Core.EntityCommandExecutionException: The data reader is incompatible with the specified 'CodeFirstNamespace.Product'. A member of the type, 'Price', does not have a corresponding column in the data reader wi View Code
来看SqlQuery()
var res = ctx.Products.SqlQuery("select id,name from tb_products");
var product = res.FirstOrDefault();
// 报错：System.Data.Entity.Core.EntityCommandExecutionException: The data reader is incompatible with the specified 'CodeFirstNamespace.Product'. A member of the type, 'Price', does not have a corresponding column in the data reader w View Code
连接查询
上⾯的问题是，他必须要查询所有列的数据，但是连接查询⼜可以，指定某⼏列，没有问题……
但是使⽤连接查询你得定义类来接收（除⾮你有适合的类型,哪怕是object、dynamic都不⾏），只要查询出来的列和你model中的属性数量不匹配，就会报错
我⽤dynamic类型接收，不报错，但是没有数据
var res = ctx.Database.SqlQuery<dynamic>(@"select o.Id,o.OrderNO, as ProductName from tb_Orders as o inner join tb_Products as p
//on o.id = p.FK_Order_Id").ToList();
// Console.WriteLine(JsonConvert.SerializeObject(res)); // [{},{},{},{},{},{},{},{},{}]
View Code
⽤object是⼀样的结果
var res = ctx.Database.SqlQuery<object>(@"select o.Id,o.OrderNO, as ProductName from tb_Orders as o inner join tb_Products as p
//on o.id = p.FK_Order_Id").ToList();
// Console.WriteLine(JsonConvert.SerializeObject(res)); // [{},{},{},{},{},{},{},{},{}]
View Code
那我不使⽤连接查询呢？我就查询三列，⼀样
ctx.Database.SqlQuery<object>(@"select id,name from tb_products").ToList();
//Console.WriteLine(JsonConvert.SerializeObject(res)); // [{},{},{},{},{},{},{},{},{}]
View Code
那我查询全部，也是⼀样的
ctx.Database.SqlQuery<object>(@"select * from tb_products").ToList();
//Console.WriteLine(JsonConvert.SerializeObject(res)); // [{},{},{},{},{},{},{},{},{}]
View Code
不去了解他了
我刚刚把数量给⾼亮了，这就要说明⼀下，你使⽤什么类型去接收，只要你的model属性的数量和查询数据集中列的数量不⼀致就会报错
现在我定义⼀个test类
public class Test
{
public string Id { get; set; }
public string Name { get; set; }
}
View Code
然后连接查询两列，ID和ProductName，⽤test类型去接收，可以的
// var res = ctx.Database.SqlQuery<Test>(@"select o.Id,o.OrderNO, as ProductName from tb_Orders as o inner join tb_Products as p
//on o.id = p.FK_Order_Id");
// Console.WriteLine(JsonConvert.SerializeObject(res.ToList()));
//[{"Id":"82903023-a7a6-4839-9caa-153ee9d00e65","Name":null},{"Id":"469b82be-8139-4e67-b566-5b2b5f6d838d","Name":null},{"Id":"e18757db-1db8-4f7f-b702-79138709b304","Name":null},{"Id":"e18757db-1db8-4f7f-b702-79138709b304" View Code
查询表中单列数据
⽐如我们查询表中某⼀列数据，或者使⽤Count()聚合查询，那么SqlQuery<T>()⽀持，SqlQuery()不⽀持
// 查询单条记录
var res = ctx.Database.SqlQuery<string>("select name from tb_products");
Console.WriteLine(JsonConvert.SerializeObject(res.ToList()));
// ["⽛刷","砖头","苹果","柚⼦","瓷砖","柑橘","嗽⼝⽔","⽛膏","⽔泥"]
View Code
var res3 = ctx.Products.SqlQuery("select name from tb_products");
Console.WriteLine(JsonConvert.SerializeObject(res3));
// System.Data.Entity.Core.EntityCommandExecutionException: The data reader is incompatible with the specified 'CodeFirstNamespace.Product'. A member of the type, 'Id', does not have a corresponding column in the data reader w View Code
查询时，传递参数
在查询字符串中传递参数这个太需要了对吧，不安全的做法，直接拼接SQL语句；安全的做法，使⽤参数化查询
先来个拼接的
// 拼接的⽅式
decimal price = 14m;
var products = ctx.Database.SqlQuery<Product>($"select *from tb_Products where Price = {price}");
Console.WriteLine(JsonConvert.SerializeObject(products.ToList()));
//[{"Order":null,"Name":"⽛刷","Price":14.00,"Unit":"只","FK_Order_Id":"82903023-a7a6-4839-9caa-153ee9d00e65","Id":"1b25351c-3008-4d27-a9de-6749ec1d0845","AddTime":"2019-01-15T10:35:03.947"}]
View Code
参数化查询
// 参数化SQL实现
decimal price = 14m;
var parameters = new SqlParameter() { ParameterName = "@price", SqlDbType = System.Data.SqlDbType.Decimal, Value = price };
var res = ctx.Database.SqlQuery<Product>("select * from tb_products where price=@price",parameters);
Console.WriteLine(JsonConvert.SerializeObject(res.ToList()));
// [{"Order":null,"Name":"⽛刷","Price":14.00,"Unit":"只","FK_Order_Id":"82903023-a7a6-4839-9caa-153ee9d00e65","Id":"1b25351c-3008-4d27-a9de-6749ec1d0845","AddTime":"2019-01-15T10:35:03.947"}]
View Code
原始⾮查询
Insert、Update、Delete这些操作EF为我们提供了 ExecuteSqlCommand()和ExecuteSqlCommandAsync()
你⼀定会想到把insert语句写到上⾯的SqlQuery()查询⽅法中去，哈，我也想到了
var res = ctx.Products.SqlQuery("insert into tb_Products values(newid(),'茶叶',55.5,'82903023-a7a6-4839-9caa-153ee9d00e65',getdate(),'⽄');");
View Code
其实数据被添加进去了，只不过这种⽅式太抬杠了
最后来个⼀个添加，使⽤EF提供的正宗的⽅法，当然必须是参数化的⽅式
// 参数化添加
string sql = @"insert into tb_Products values(@Id,@Name,@Price,@FK_Order_Id,@AddTime,@Unit);";
var parameterList = new List<SqlParameter> {
new SqlParameter("@Id",Guid.NewGuid().ToString()),
new SqlParameter("@Name","⼤⽶"),
new SqlParameter("@Price",73m),
new SqlParameter("@FK_Order_Id","82903023-a7a6-4839-9caa-153ee9d00e65"),
new SqlParameter("@AddTime",DateTime.Now),
new SqlParameter("@Unit","袋")
}; // 这么多@符号容易让⼈⼀下⼦联想到⿇将中的⼀筒啊
var parameterArr = parameterList.ToArray();
var res = ctx.Database.ExecuteSqlCommand(sql,parameterArr);
Console.WriteLine(res); // result:1
View Code
是不是OK啊。