首页 新闻 搜索 专区 学院

请教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条件出现在错误的位置,导致扫描行数是正常的几百万倍。。。。 疯了

Goooing的主页 Goooing | 初学一级 | 园豆:104
提问于:2018-03-08 16:23
< >
分享
最佳答案
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);
        }

Goooing | 初学一级 |园豆:104 | 2018-03-08 19:00
其他回答(6)
0

建议用LINQ,拼接SQL很方便。例如:

var query = from q in (from b in _context.ycb_goods select b)
                  select q;

         

收获园豆:20
潇潇@暮雨 | 园豆:256 (菜鸟二级) | 2018-03-08 16:36
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
dudu | 园豆:36170 (高人七级) | 2018-03-08 16:45

去掉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

因为找不着核心原因是什么,只好是想通过全部参数化来解决这个问题。 尴尬!

 

支持(0) 反对(0) Goooing | 园豆:104 (初学一级) | 2018-03-08 17:09

@Goooing: EF版本是多少?

支持(0) 反对(0) dudu | 园豆:36170 (高人七级) | 2018-03-08 17:16

@dudu: 6.0

支持(0) 反对(0) Goooing | 园豆:104 (初学一级) | 2018-03-08 17:21

@dudu: 6.2.61023.0

支持(0) 反对(0) Goooing | 园豆:104 (初学一级) | 2018-03-08 17:23

@Goooing: 为什么不这样避开这个问题?

x => x.IsValid == true  && x.AccountBasicWorkState.IsWorking == true
支持(0) 反对(0) dudu | 园豆:36170 (高人七级) | 2018-03-08 17:52
0

db.T_Accounts.Where(t=>t.Id=你的参数).Take(1).Select(x => x.Id);不知道你是不是表达这个意思。

MySqlEf Take 实现 mysql limit;不必走到动态查询的用表达式是最好的选择,动态查询也没你上文那么复杂,微软提供了库,当作简单反射表达即可使用。

 

出来注意到你的标题,如果你是网络接口类,你其实甚至微软的动态linq库都不必,比如OData就是你的选择,你只需限制最最基本的输出到IQuerable即可。

花飘水流兮 | 园豆:13299 (专家六级) | 2018-03-08 17:00

感谢回复!

我的使用场景是动态的where条件以及动态的select,所以只能使用Expression拼接的方式。

给dudu回复中有更具体的现象,如果方便,还请帮我看看,谢谢。

支持(0) 反对(0) Goooing | 园豆:104 (初学一级) | 2018-03-08 17:14

@Goooing: 那么答案也告诉你了 —— 你不用写,微软有提供动态linq,这个工作微软已经给你做了。

支持(0) 反对(0) 花飘水流兮 | 园豆:13299 (专家六级) | 2018-03-08 17:15

@花飘水流兮: 你说的动态linq是指?

支持(0) 反对(0) Goooing | 园豆:104 (初学一级) | 2018-03-08 17:21

@Goooing: 告诉你一个优雅的省工办法 —— 你按照合理的单词在nuget里面搜索,vs经常让你有意外。动态linq的单词就不多说了。

支持(0) 反对(0) 花飘水流兮 | 园豆:13299 (专家六级) | 2018-03-08 20:32
0

图啥呢...

吴瑞祥 | 园豆:29369 (高人七级) | 2018-03-08 17:24
0

我还是很佩服题主自己写Expression 解析的,说明还是有点代码能力的。

不过貌似什么地方错了,感觉是方法错了。

爱编程的大叔 | 园豆:30813 (高人七级) | 2018-03-08 17:33
0

linqkit

Neo-1989 | 园豆:202 (菜鸟二级) | 2019-05-10 08:55
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册