请教EF中生成参数化的动态查询的方法
0
悬赏园豆：100
[已解决问题]
解决于 2019-05-09 17:03
EF连接mysql，使用Expression实现动态查询，代码如下：
OscarSystemEnities db = new OscarSystemEnities(); long id = 1; db.Database.Log = ShowLOG; ParameterExpression para = Expression.Parameter(typeof( OscarSystem.Context.Models.T_Account), "x"); Expression left = Expression.Property(para, "DebtorId"); Expression right = Expression.Constant(id); var exp = ConditionExpressionBase.GetBinaryExpresion(left, right, BinaryType.Equal); var where = Expression.Lambda<Func<OscarSystem.Context.Models.T_Account, bool>>(exp, para); var sql = db.T_Accounts.Where(where.Compile()).Select(x => x.Id).ToString(); //+ "\r\n"
这时生成的sql语句是：
SELECT `Extent1`.`Id` FROM `T_Account` AS `Extent1` WHERE 1 = `Extent1`.`DebtorId` LIMIT 1
我想实现生成参数化的语句：
SELECT `Limit1`.`Id` FROM (SELECT `Extent1`.`Id` FROM `T_Account` AS `Extent1` WHERE `Extent1`.`DebtorId` = @p__linq__0 LIMIT 1) AS `Limit1`
请问怎么如何拼接Expression呢？？谢谢大家 ！
想使用参数化的原因是，ef+mysql+expression拼接生成sql非常坑爹，where条件出现在错误的位置，导致扫描行数是正常的几百万倍。。。。 疯了
最佳答案
0
解决问题了，参照这位高人的代码，https://code.msdn.microsoft.com/Entity-Framework-a958cffb
将ConstantExpression通过下列代码转换，就能以参数化的形式生成sql语句：
protected override Expression VisitConstant(ConstantExpression node) { Type tupleType; ParameterizableTypes.TryGetValue(node.Type, out tupleType); if (ParameterizableTypes.TryGetValue(node.Type, out tupleType)) { //Replace the ConstantExpression to PropertyExpression of Turple<T>.Item1 //Entity Framework 5 will parameterize the expression when the expression tree is compiled Object wrappedObject = Activator.CreateInstance(tupleType, new [] { node.Value }); Expression visitedExpression = Expression.Property(Expression.Constant(wrappedObject), "Item1"); return visitedExpression; } return base.VisitConstant(node); }
其他回答(6)
0
建议用LINQ，拼接SQL很方便。例如：
var query = from q in (from b in _context.ycb_goods select b)
select q;
收获园豆：20
0
去掉 where.Compile()，改用Expression 试试
Expression<Func<OscarSystem.Context.Models.T_Account, bool>> where =
Expression.Lambda<Func<OscarSystem.Context.Models.T_Account, bool>>(exp, para);
var sql = db.T_Accounts.Where(where).Select(x => x.Id).ToString(); //+ "\r\n"
收获园豆：80
去掉compile()后生成的仍然不是参数化的 。
把dudu大神引来了，正好请教一下，最近踩到的坑。
我的场景是动态生成where条件以及select字段，主表t_Account涉及大概5张表的关系。
当Lambda表达式中所有where条件都通过变量赋值时，生成的查询语句非常优良：
OscarSystemEnities db = new OscarSystemEnities(); db.Database.Log = ShowLOG; int userId = 6980; string name = "张三"; bool isvalid = true; bool iworking = true; int visionId = 0; var sql1 = db.T_Accounts.Where(x => x.IsValid ==isvalid && (x.AccountBasicWorkState.IsWorking==iworking ) && x.AccountBasicWorkState.UserId == userId && x.Debtor.DebtorName == name && x.AccountSupervisionId > visionId).Select(x => new { Name = x.Debtor.DebtorName, UserId = x.AccountBasicWorkState.UserId }).ToString();
生成的语句：
SELECT `Extent2`.`UserId`, `Extent3`.`DebtorName` FROM `T_Account` AS `Extent1` INNER JOIN `T_AccountBasicWorkState` AS `Extent2` ON `Extent1`.`AccountBasicWorkStateId` = `Extent2`.`Id` INNER JOIN `T_Debtor` AS `Extent3` ON `Extent1`.`DebtorId` = `Extent3`.`Id` WHERE `Extent1`.`IsValid` = @p__linq__0 AND `Extent2`.`IsWorking` = @p__linq__1 AND `Extent2`.`UserId` = @p__linq__2 AND `Extent3`.`DebtorName` = @p__linq__3 AND `Extent1`.`AccountSupervisionId` > @p__linq__4
当Lambda中where条件只是部分使用变量时，坑爹的事情出现了，生成了一个庞大的、性能极其低下的sql语句：
//差别只在于IsValid、IsWorking两个条件没写等于个变量
var sql2 = db.T_Accounts.Where(x => x.IsValid && (x.AccountBasicWorkState.IsWorking) && x.AccountBasicWorkState.UserId == userId && x.Debtor.DebtorName == name && x.AccountSupervisionId > visionId).Select(x => new { Name = x.Debtor.DebtorName, UserId = x.AccountBasicWorkState.UserId }).ToString();
生成的语句是：
SELECT `Filter1`.`UserId`, `Filter1`.`DebtorName`, `Extent4`.`UserId` AS `UserId1` FROM ( SELECT `Extent1`.`Id`, `Extent1`.`ServiceId`, `Extent1`.`DebtorId`, `Extent1`.`CustomerId`, `Extent1`.`CustomerName`, `Extent1`.`CurrencyTypeName`, `Extent1`.`CityId`, `Extent1`.`CityName`, `Extent1`.`ProvinceId`, `Extent1`.`ProvinceName`, `Extent1`.`OutDate`, `Extent1`.`DueDate`, `Extent1`.`OutAmount`, `Extent1`.`CurrentOutAmount`, `Extent1`.`OutRMBAmount`, `Extent1`.`CurrentRMBAmount`, `Extent1`.`CSName`, `Extent1`.`CustAgentName`, `Extent1`.`OutIDNumber`, `Extent1`.`OutIDType`, `Extent1`.`AccountNumber`, `Extent1`.`OutNumber`, `Extent1`.`OutLageDay`, `Extent1`.`OutAccountAge`, `Extent1`.`FeeRate`, `Extent1`.`AccountFeatureId`, `Extent1`.`AccountBasicWorkStateId`, `Extent1`.`AccountSupervisionId`, `Extent1`.`AccountDataResultId`, `Extent1`.`AccountCategoryId`, `Extent1`.`AddTime`, `Extent1`.`UpdateTime`, `Extent1`.`AddUserId`, `Extent1`.`UpdateUserId`, `Extent1`.`IsValid`, `Extent2`.`Id` AS `ID1`, `Extent2`.`AccountId`, `Extent2`.`UserId`, `Extent2`.`UserName`, `Extent2`.`AllotmentId`, `Extent2`.`GroupId`, `Extent2`.`GroupName`, `Extent2`.`UnitId`, `Extent2`.`UnitName`, `Extent2`.`CompanyId`, `Extent2`.`CompanyName`, `Extent2`.`AccountStateId`, `Extent2`.`OscarWorkStateId`, `Extent2`.`ProgressStateId`, `Extent2`.`ProgressValue`, `Extent2`.`VisitStateId`, `Extent2`.`MailApprovalStateId`, `Extent2`.`UserRemark`, `Extent2`.`SupervisorRemark`, `Extent2`.`CSRemark`, `Extent2`.`AuditRemark`, `Extent2`.`LastWorkTime`, `Extent2`.`NextWorkTime`, `Extent2`.`CloseTime`, `Extent2`.`CurrentLagDay`, `Extent2`.`CurrentAccountAge`, `Extent2`.`LastRepayTime`, `Extent2`.`LastRepayAmount`, `Extent2`.`NextTimeRemark`, `Extent2`.`IsWorking`, `Extent2`.`StarUserIdArry`, `Extent2`.`AddTime` AS `ADDTIME1`, `Extent2`.`UpdateTime` AS `UPDATETIME1`, `Extent2`.`AddUserId` AS `ADDUSERID1`, `Extent2`.`UpdateUserId` AS `UPDATEUSERID1`, `Extent2`.`IsValid` AS `ISVALID1`, `Extent3`.`Id` AS `ID2`, `Extent3`.`DebtorName`, `Extent3`.`IDNumber`, `Extent3`.`IDTypeName`, `Extent3`.`SexTypeName`, `Extent3`.`Age`, `Extent3`.`AddTime` AS `ADDTIME2`, `Extent3`.`UpdateTime` AS `UPDATETIME2`, `Extent3`.`AddUserId` AS `ADDUSERID2`, `Extent3`.`UpdateUserId` AS `UPDATEUSERID2`, `Extent3`.`IsValid` AS `ISVALID2` FROM `T_Account` AS `Extent1` INNER JOIN `T_AccountBasicWorkState` AS `Extent2` ON `Extent1`.`AccountBasicWorkStateId` = `Extent2`.`Id` INNER JOIN `T_Debtor` AS `Extent3` ON `Extent1`.`DebtorId` = `Extent3`.`Id` WHERE `Extent1`.`IsValid` = 1 -- 常量部分的条件在里 AND `Extent2`.`IsWorking` = 1 ) AS `Filter1` LEFT OUTER JOIN `T_AccountBasicWorkState` AS `Extent4` ON `Filter1`.`AccountBasicWorkStateId` = `Extent4`.`Id` WHERE `Filter1`.`UserId` = @p__linq__0 -- 变量部分的条件在外，导致子查询的过滤条件缺失，多扫描了NNNN倍的行 AND `Filter1`.`DebtorName` = @p__linq__1 AND `Filter1`.`AccountSupervisionId` > @p__linq__2
因为找不着核心原因是什么，只好是想通过全部参数化来解决这个问题。 尴尬！
@Goooing: EF版本是多少？
@dudu: 6.0
@dudu: 6.2.61023.0
@Goooing: 为什么不这样避开这个问题？
x => x.IsValid == true && x.AccountBasicWorkState.IsWorking == true
0
db.T_Accounts.Where(t=>t.Id=你的参数).Take(1).Select(x => x.Id);不知道你是不是表达这个意思。
MySqlEf Take 实现 mysql limit；不必走到动态查询的用表达式是最好的选择，动态查询也没你上文那么复杂，微软提供了库，当作简单反射表达即可使用。
出来注意到你的标题，如果你是网络接口类，你其实甚至微软的动态linq库都不必，比如OData就是你的选择，你只需限制最最基本的输出到IQuerable即可。
感谢回复！
我的使用场景是动态的where条件以及动态的select，所以只能使用Expression拼接的方式。
给dudu回复中有更具体的现象，如果方便，还请帮我看看，谢谢。
@Goooing: 那么答案也告诉你了 —— 你不用写，微软有提供动态linq，这个工作微软已经给你做了。
@花飘水流兮: 你说的动态linq是指？
@Goooing: 告诉你一个优雅的省工办法 —— 你按照合理的单词在nuget里面搜索，vs经常让你有意外。动态linq的单词就不多说了。
0
图啥呢...
0
我还是很佩服题主自己写Expression 解析的，说明还是有点代码能力的。
不过貌似什么地方错了，感觉是方法错了。
0
linqkit
