EF6学习笔记⼗：原始查询，在EF中使⽤SQL语句EF⾥⾯当然也可以直接使⽤SQL语句了，⽐如有些复杂的查询⽤LINQ写不了的，还有存储过程那些东西。EF为查询操作提供了两个⽅法：ry()、.SqlQuery() （ctx表⽰上下⽂对象）为Insert、Update、Delete 操作提供了两个⽅法：ExecuteSqlCommand()、ExecuteSqlCommandAsync()我们来弄⼀弄这些⽅法，看看怎么回事原始查询ry() 和 .SqlQuery() 两个⽅法的区别，最先要说的就是，ry()查询出的数据没有被上下⽂追踪，另⼀个⽅法查询出的实体则被追踪了来看ry() 查询出实体的状态为Detachedusing(EFDbContext ctx = new EFDbContext){ var res = ry("select *from tb_products"); //var state = (res).State; // 报错 实体 DbRawSqlQuery不是上下⽂模型的⼀部分 var first = rDefault(); var state = (first).State; ine(state); // Deteched}View Code来看.SqlQuery() 实体状态为Unchangedvar res = ry("select * from tb_products");var pro = rDefault();var state = (pro).State;ine(state); // UnchangedView Code这是他们之间的第⼀个区别，不过这个倒没什么⼤碍，对吧。即使不被跟踪，我也可以调⽤Attach⽅法对它进⾏追踪查询指定列的数据（⼤于1，⼩于总列数）这两个⽅法不⽀持查询指定某⼏列的数据，必须要所有列的数据来看SqlQuery() var res = ry("select id,name from tb_products"); var product = rDefault();// 报错：CommandExecutionException: The data reader is incompatible with the specified 't'. A member of the type, 'Price', does not have a corresponding column in the data reader wiView Code来看SqlQuery()var res = ry("select id,name from tb_products");var product = rDefault();// 报错：CommandExecutionException: The data reader is incompatible with the specified 't'. A member of the type, 'Price', does not have a corresponding column in the data reader wView Code连接查询上⾯的问题是，他必须要查询所有列的数据，但是连接查询⼜可以，指定某⼏列，没有问题……但是使⽤连接查询你得定义类来接收（除⾮你有适合的类型,哪怕是object、dynamic都不⾏），只要查询出来的列和你model中的属性数量不匹配，就会报错我⽤dynamic类型接收，不报错，但是没有数据var res = ry(@"select ,O, as ProductName from tb_Orders as o inner join tb_Products as p
//on = _Order_Id").ToList(); // ine(izeObject(res)); // [{},{},{},{},{},{},{},{},{}]View Code⽤object是⼀样的结果var res = ry
//on = _Order_Id"); // ine(izeObject(())); //[{"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()⽀持，SqlQuery()不⽀持 // 查询单条记录 var res = ry("select name from tb_products");
ine(izeObject(())); // ["⽛刷","砖头","苹果","柚⼦","瓷砖","柑橘","嗽⼝⽔","⽛膏","⽔泥"]View Code var res3 = ry("select name from tb_products"); ine(izeObject(res3)); // CommandExecutionException: The data reader is incompatible with the specified 't'. A member of the type, 'Id', does not have a corresponding column in the data reader wView Code查询时，传递参数在查询字符串中传递参数这个太需要了对吧，不安全的做法，直接拼接SQL语句；安全的做法，使⽤参数化查询先来个拼接的// 拼接的⽅式decimal price = 14m;var products = ry($"select *from tb_Products where Price = {price}");
ine(izeObject(())); //[{"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 = l, Value = price };var res = ry("select * from tb_products where price=@price",parameters); ine(izeObject(()));// [{"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 = ry("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",d().ToString()), new SqlParameter("@Name","⼤⽶"), new SqlParameter("@Price",73m), new SqlParameter("@FK_Order_Id","82903023-a7a6-4839-9caa-153ee9d00e65"), new SqlParameter("@AddTime",), new SqlParameter("@Unit","袋") }; // 这么多@符号容易让⼈⼀下⼦联想到⿇将中的⼀筒啊 var parameterArr = y(); var res = eSqlCommand(sql,parameterArr); ine(res); // result:1View Code是不是OK啊
2023年6月21日发(作者：)
EF6学习笔记⼗：原始查询，在EF中使⽤SQL语句EF⾥⾯当然也可以直接使⽤SQL语句了，⽐如有些复杂的查询⽤LINQ写不了的，还有存储过程那些东西。EF为查询操作提供了两个⽅法：ry()、.SqlQuery() （ctx表⽰上下⽂对象）为Insert、Update、Delete 操作提供了两个⽅法：ExecuteSqlCommand()、ExecuteSqlCommandAsync()我们来弄⼀弄这些⽅法，看看怎么回事原始查询ry() 和 .SqlQuery() 两个⽅法的区别，最先要说的就是，ry()查询出的数据没有被上下⽂追踪，另⼀个⽅法查询出的实体则被追踪了来看ry() 查询出实体的状态为Detachedusing(EFDbContext ctx = new EFDbContext){ var res = ry("select *from tb_products"); //var state = (res).State; // 报错 实体 DbRawSqlQuery不是上下⽂模型的⼀部分 var first = rDefault(); var state = (first).State; ine(state); // Deteched}View Code来看.SqlQuery() 实体状态为Unchangedvar res = ry("select * from tb_products");var pro = rDefault();var state = (pro).State;ine(state); // UnchangedView Code这是他们之间的第⼀个区别，不过这个倒没什么⼤碍，对吧。即使不被跟踪，我也可以调⽤Attach⽅法对它进⾏追踪查询指定列的数据（⼤于1，⼩于总列数）这两个⽅法不⽀持查询指定某⼏列的数据，必须要所有列的数据来看SqlQuery() var res = ry("select id,name from tb_products"); var product = rDefault();// 报错：CommandExecutionException: The data reader is incompatible with the specified 't'. A member of the type, 'Price', does not have a corresponding column in the data reader wiView Code来看SqlQuery()var res = ry("select id,name from tb_products");var product = rDefault();// 报错：CommandExecutionException: The data reader is incompatible with the specified 't'. A member of the type, 'Price', does not have a corresponding column in the data reader wView Code连接查询上⾯的问题是，他必须要查询所有列的数据，但是连接查询⼜可以，指定某⼏列，没有问题……但是使⽤连接查询你得定义类来接收（除⾮你有适合的类型,哪怕是object、dynamic都不⾏），只要查询出来的列和你model中的属性数量不匹配，就会报错我⽤dynamic类型接收，不报错，但是没有数据var res = ry(@"select ,O, as ProductName from tb_Orders as o inner join tb_Products as p
//on = _Order_Id").ToList(); // ine(izeObject(res)); // [{},{},{},{},{},{},{},{},{}]View Code⽤object是⼀样的结果var res = ry
//on = _Order_Id"); // ine(izeObject(())); //[{"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()⽀持，SqlQuery()不⽀持 // 查询单条记录 var res = ry("select name from tb_products");
ine(izeObject(())); // ["⽛刷","砖头","苹果","柚⼦","瓷砖","柑橘","嗽⼝⽔","⽛膏","⽔泥"]View Code var res3 = ry("select name from tb_products"); ine(izeObject(res3)); // CommandExecutionException: The data reader is incompatible with the specified 't'. A member of the type, 'Id', does not have a corresponding column in the data reader wView Code查询时，传递参数在查询字符串中传递参数这个太需要了对吧，不安全的做法，直接拼接SQL语句；安全的做法，使⽤参数化查询先来个拼接的// 拼接的⽅式decimal price = 14m;var products = ry($"select *from tb_Products where Price = {price}");
ine(izeObject(())); //[{"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 = l, Value = price };var res = ry("select * from tb_products where price=@price",parameters); ine(izeObject(()));// [{"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 = ry("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",d().ToString()), new SqlParameter("@Name","⼤⽶"), new SqlParameter("@Price",73m), new SqlParameter("@FK_Order_Id","82903023-a7a6-4839-9caa-153ee9d00e65"), new SqlParameter("@AddTime",), new SqlParameter("@Unit","袋") }; // 这么多@符号容易让⼈⼀下⼦联想到⿇将中的⼀筒啊 var parameterArr = y(); var res = eSqlCommand(sql,parameterArr); ine(res); // result:1View Code是不是OK啊
发布评论