不要轻易使用linq代替sql

最新推荐文章于 2021-02-01 03:00:24 发布

weixin_30293079

最新推荐文章于 2021-02-01 03:00:24 发布

阅读量206

点赞数

CC 4.0 BY-SA版权

文章标签： runtime 数据库

原文链接：http://www.cnblogs.com/Fly-sky/p/5436036.html

本文通过一个具体的例子展示了使用EntityFramework与自定义SQL在执行效率上的显著差异。通过对比发现，对于复杂的查询操作，自定义SQL比EntityFramework生成的SQL快了数十倍。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用Entityframework+Reposity模式写出的代码执行效率极低。

Linq代码：

 var querySql = rel_project_personservice.GetItems(r => r.Person).Where(w => w.ProjectId == projectId && w.Person.College.University.Province == provinceName);

转化的SQL语句：

SELECT "Extent1"."personid", "Extent1"."projectid", "Extent1"."isallotroom", "Extent1"."randomcode", "Extent1"."admissionticket", 
"Extent1"."pushstatus", "Extent1"."createdtime", "Extent1"."examinationroomid", "Extent2"."id", "Extent2"."name", "Extent2"."idnumber",
 "Extent2"."gender", "Extent2"."mobile", "Extent2"."idcard", "Extent2"."email", "Extent2"."degree", "Extent2"."nation", "Extent2"."major", 
"Extent2"."graduatedate", "Extent2"."livingcity", "Extent2"."accountlocation", "Extent2"."expectcity", "Extent2"."enterprisenature", 
"Extent2"."expectindustry", "Extent2"."expectjob", "Extent2"."collegeid", "Extent2"."status", "Extent2"."signuptype", 
"Extent2"."createdtime" AS "createdtime1", "Extent2"."modifiedtime", "Extent2"."createdby", "Extent2"."modifiedby", 
"Extent2"."classname", "Extent2"."idcardtype", "Extent2"."birthday", "Extent2"."xzid", "Extent2"."szid", "Extent2"."testcity", 
"Extent2"."graduateschool", "Extent2"."degreeid", "Extent2"."nationid", "Extent2"."majorid", "Extent2"."accountlocationid", 
"Extent2"."livingcityid", "Extent2"."expectcityid", "Extent2"."enterprisenatureid", "Extent2"."expectindustryid", 
"Extent2"."expectjobid", "Extent2"."testcityid" FROM "public"."rel_project_person" AS "Extent1" INNER JOIN "public"."person" AS "Extent2" 
ON "Extent1"."personid" = "Extent2"."id" LEFT OUTER JOIN "public"."college" AS "Extent3" ON "Extent2"."collegeid" = "Extent3"."id"
 LEFT OUTER JOIN "public"."university" AS "Extent4" ON "Extent3"."universityid" = "Extent4"."id" 
 WHERE "Extent1"."projectid" = 11 AND ("Extent4"."province" = '北京' OR "Extent4"."province" IS NULL )

linq转出来的语句在数据库上执行，花费40多秒:

Total query runtime: 40.1 secs
检索到 100127 行。

实现同样的业务，自己写的sql语句：

Select C.UNIVERSITYID,c.name,count(p.id)
from Person p
join Rel_Project_Person R on P.id =R.PersonID
join College C on p.CollegeID = C.id
where R.ProjectId =11 and C.universityid IN
(
Select id from University where Province = '北京'
)
group by C.UNIVERSITYID,c.name

执行自己写的sql，花费不到1秒：

Total query runtime: 680 msec
检索到 2 行。

转载于:https://www.cnblogs.com/Fly-sky/p/5436036.html