实体集查询
我正在尝试运行类似于 MyEntitySet 的查询
var results = MyItem.MyEntitySet.Where( x => x.PropertyB == 0 )
，它与 MyItem 有一个关联 PropertyA。
理想情况下，底层 SQL 查询应该是，
SELECT .. FROM .. WHERE ([t0].[PropertyA] = @p0) AND ([t0].[PropertyB ] = @p1)
因为 PropertyA 和 PropertyB 是我正在查询的表的两个主键。
但我的跟踪似乎表明程序首先使用 PropertyA 查询以返回 MyEntitySet，然后使用 PropertyB 查询以返回 var results。
我是否可以强制 Linq 在单个 SQL 语句中使用这两个条件进行查询？
I'm trying to run a query similar to
var results = MyItem.MyEntitySet.Where( x => x.PropertyB == 0 )
MyEntitySet has one association, PropertyA, with MyItem.
Ideally, the underlying SQL query should be
SELECT .. FROM .. WHERE ([t0].[PropertyA] = @p0) AND ([t0].[PropertyB ] = @p1)
since PropertyA and PropertyB are the two primary keys of the table I'm querying.
But my traces seem to indicate that the program queries with PropertyA first to return MyEntitySet, then queries with PropertyB to return var results.
Is there anyway I can force Linq to query with these two conditions in a single SQL statement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
也许，也许不是。生成的 SQL 确实与您编写 LINQ 查询的方式相匹配，因此生成的 SQL 并不令人意外。如果您从“MyEntitySet”表示的实体开始，那么生成的 SQL 可能会发生变化。
目前尚不清楚您使用的是 LINQ to SQL 还是实体框架。 LINQ to SQL 确实将一对多关系表示为“实体集”，而实体框架将关系视为一等对象，因此一对多关系是具有相关实体的关系对象的集合，而不是只是一个实体集。它确实会影响生成的 SQL。
另外两个想法...
如果您希望对生成的 SQL 进行那么多控制，那么您可能不会对 LINQ 感到满意。它并不总是生成最佳 SQL（尽管它有时会让您感到惊讶）。另一方面，LINQ 的主要好处之一是您可以开始编写表达数据中真实关系的代码。经典 ADO.NET 的缺点是您需要编写有关操作 SQL 以及处理 DataSet 和 DataTable 集合的代码。 LINQ 是一种极其干净、安全、健壮且更易于维护的代码。一切都是一个权衡。
其次，随着时间的推移，查询生成可能会变得更好（尤其是在实体框架中）。
Maybe, maybe not. The generated SQL does match the way you're writing the LINQ query, so the generated SQL isn't a surprise. If you started with the entity represented by "MyEntitySet" then, maybe, the generated SQL would change.
It's not immediately clear whether you're using LINQ to SQL or Entity Framework. LINQ to SQL does represent one-to-many relationships as an "entity set", while Entity Framework treats relationships as first-class objects, so that a one-to-many relationship is a set of relationship objects with related entities, rather than simply an entity set. It does affect the generated SQL.
Two other thoughts...
If you want that much control over the generated SQL, you probably won't be happy with LINQ. It doesn't always generate optimal SQL (although it can sometimes surprise you). On the other hand, one of the major benefits of LINQ is that you start writing code that expresses the real relationships in your data. The downfall of classic ADO.NET is that you write code about manipulating SQL and processing DataSet and DataTable collections. LINQ is infinitely cleaner, safer, more robust, and more maintainable code to write. Everything is a trade-off.
Second, the query generation is likely to get better over time (especially in Entity Framework).