EntitySet Querying

Question

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?

Answer 1

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).