ef执行原生sql语句_EF6学习笔记十：原始查询，在EF中使用SQL语句

要专业系统地学习EF前往《你必须掌握的Entity Framework 6.x与Core 2.0》这本书的作者(汪鹏，Jeffcky)的博客：https://www.cnblogs.com/CreateMyself/

EF里面当然也可以直接使用SQL语句了，比如有些复杂的查询用LINQ写不了的，还有存储过程那些东西。

EF为查询操作提供了两个方法：ctx.Database.SqlQuery()、ctx.DbSet.SqlQuery()   (ctx表示上下文对象)

为Insert、Update、Delete 操作提供了两个方法：ExecuteSqlCommand()、ExecuteSqlCommandAsync()

我们来弄一弄这些方法，看看怎么回事

原始查询

ctx.Database.SqlQuery() 和 ctx.Dbset.SqlQuery() 两个方法的区别，最先要说的就是，ctx.Database.SqlQuery()查询出的数据没有被上下文追踪，另一个方法查询出的实体则被追踪了

来看ctx.Database.SqlQuery() 查询出实体的状态为Detached

using(EFDbContext ctx = newEFDbContext)

{var res = ctx.Database.SqlQuery("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.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()

var res = ctx.Database.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 with the same name.

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 with the same name.

View Code

连接查询

上面的问题是，他必须要查询所有列的数据，但是连接查询又可以，指定某几列，没有问题……

但是使用连接查询你得定义类来接收(除非你有适合的类型,哪怕是object、dynamic都不行)，只要查询出来的列和你model中的属性数量不匹配，就会报错

我用dynamic类型接收，不报错，但是没有数据

var res = ctx.Database.SqlQuery(@"select o.Id,o.OrderNO,p.name 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(@"select o.Id,o.OrderNO,p.name 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(@"select id,name from tb_products").ToList();//Console.WriteLine(JsonConvert.SerializeObject(res));//[{},{},{},{},{},{},{},{},{}]

View Code

那我查询全部，也是一样的

ctx.Database.SqlQuery(@"select * from tb_products").ToList();//Console.WriteLine(JsonConvert.SerializeObject(res));//[{},{},{},{},{},{},{},{},{}]

View Code

不去了解他了

我刚刚把数量给高亮了，这就要说明一下，你使用什么类型去接收，只要你的model属性的数量和查询数据集中列的数量不一致就会报错

现在我定义一个test类

public classTest

{public string Id { get; set; }public string Name { get; set; }

}

View Code

然后连接查询两列，ID和ProductName，用test类型去接收，可以的

//var res = ctx.Database.SqlQuery(@"select o.Id,o.OrderNO,p.name 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","Name":null},{"Id":"469b82be-8139-4e67-b566-5b2b5f6d838d","Name":null},{"Id":"e18757db-1db8-4f7f-b702-79138709b304","Name":null},{"Id":"82903023-a7a6-4839-9caa-153ee9d00e65","Name":null},{"Id":"82903023-a7a6-4839-9caa-153ee9d00e65","Name":null},{"Id":"469b82be-8139-4e67-b566-5b2b5f6d838d","Name":null}]

View Code

查询表中单列数据

比如我们查询表中某一列数据，或者使用Count()聚合查询，那么SqlQuery()支持，SqlQuery()不支持

//查询单条记录

var res = ctx.Database.SqlQuery("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 with the same name.

View Code

查询时，传递参数

在查询字符串中传递参数这个太需要了对吧，不安全的做法，直接拼接SQL语句；安全的做法，使用参数化查询

先来个拼接的

//拼接的方式

decimal price =14m;var products = ctx.Database.SqlQuery($"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("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{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啊