实体框架定义查询为空+左连接 = 太慢
有此 SQL 语句
select st.Column1, isnull(sot.SomeColumn, 0) as SomeColumn
from SomeTable st
left join SomeOtherTable sot
我的 Entity Framework (4.0) .edmx 文件中的
我可以去掉 isnull，它在包装的 EF SQL 中的速度与本机一样快，但我需要 isnull 来确保 SomeColumn 具有值。
我可以在这里使用 isnull 的替代方案吗？它可以很好地与 EF 配合使用吗？
感谢您的帮助。
I have this SQL statement
select st.Column1, isnull(sot.SomeColumn, 0) as SomeColumn
from SomeTable st
left join SomeOtherTable sot
inside a <DefiningQuery> tag inside my Entity Framework (4.0) .edmx file and I'm running into a serious performance issue. Running the SQL as it's written is fast, but EF wraps the SQL during runtime to inject parameters, which slows it down tremendously.
I can take away the isnull, and it's just as fast in the wrapped EF SQL as it is natively, but I need the isnull to ensure SomeColumn has a value.
Are there any alternatives to isnull that I could use here that would play nicely with EF?
Thanks for your help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 EF 设计器的属性属性中应该有一个默认值属性，但我不确定这是否能满足您的需求。作为替代方案，从 EF 模型生成的所有实体都是部分实体。我将添加另一个包装 SomeColumn 属性（不带 isnull）的属性以提供默认值。鉴于 SomeColumn 可能会映射为可为 null 的 int，您可以像这样定义新属性：
您将针对 SomeColumn 编写查询，然后在需要确保它不为 null 的地方使用 SomeColumnWithDefaultValue。
In the property's properties in the EF designer there should be a default value property, I'm not sure if that will fit your needs though. As an alternative, all of the entities generated out of you EF model are partials. I would add another property that wraps the SomeColumn property (without the isnull) to provide a default value. Given that SomeColumn will likely be mapped as a nullable int, you can define the new property like this:
You would write your queries against SomeColumn and then use SomeColumnWithDefaultValue where you needed to ensure that it was not null.
我的解决方案是从 SQL 中完全删除任何
isnull的使用，而是包含一个SELECT NEWID() AS ID（需要它与 SQL 2000+ 兼容）这些查询。一旦我将此ID设置为我的单个isnullable=true并且我不再需要应对 EF 速度变慢的问题。希望这对某人有帮助。
My solution was to remove any use of
isnullaltogether from my SQL and instead include aSELECT NEWID() AS ID(needed this to be SQL 2000+ compatible) with all of these queries. Once I set thisIDas my single<EntityKey>, I could allow set any of the left join properties asisnullable=trueand I no longer had to deal with the EF slowdown.Hope this helps someone.