CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-4iqueryable
AakashM
AakashM

Reputation: 63338

Can I easily evaluate many IQueryables in a single database call using Entity Framework?

Suppose I have a collection (of arbitrary size) of IQueryable<MyEntity> (all for the same MyEntity type). Each individual query has successfully been dynamically built to encapsulate various pieces of business logic into a form that can be evaluated in a single database trip. Is there any way I can now have all these IQueryables executed in a single round-trip to the database?

For example (simplified; my actual queries are more complex!), if I had

ObjectContext context = ...;
var myQueries = new[] {
    context.Widgets.Where(w => w.Price > 500),
    context.Widgets.Where(w => w.Colour == 5),
    context.Widgets.Where(w => w.Supplier.Name.StartsWith("Foo"))
};

I would like to have EF perform the translation of each query (which it can do indivudually), then in one database visit, execute

SELECT * FROM Widget WHERE Price > 500
SELECT * FROM Widget WHERE Colour = 5
SELECT W.* FROM Widget 
                INNER JOIN SUpplier ON Widget.SupplierId = Supplier.Id 
           WHERE Supplier.Name LIKE 'Foo%'

then convert each result set into an IEnumerable<Widget>, updating the ObjectContext in the usual way.

I've seen various posts about dealing with multiple result sets from a stored procedure, but this is slightly different (not least because I don't know at compile time how many results sets there are going to be). Is there an easy way, or do I have to use something along the lines of Does the Entity Framework support the ability to have a single stored procedure that returns multiple result sets??

Upvotes: 1

Views: 332

Answers (2)

lukeguy
lukeguy

Reputation: 118

It might be late answer, hopefully it would help some one else with the same issue.

There is Entity Framework Extended Library on NuGet which provides the future queries feature (among others). I played a bit with it and it looks promising.

You can find more information here.

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364269

No. EF deosn't have query batching (future queries). One queryable is one database roundtrip. As a workaround you can try to play with it and for example use:

string sql = ((ObjectQuery<Widget>)context.Widgets.Where(...)).ToTraceString();

to get SQL of the query and build your own custom command from all SQLs to be executed. After that you can use similar approach as with stored procedures to translate results.

Unless you really need to have each query executed separately you can also union them to single query:

context.Widgets.Where(...).Union(context.Widgets.Where(...));

This will result in UNION. If you need just UNION ALL you can use Concat method instead.

Upvotes: 3

Related Questions