Reputation: 5223
Let's say I know ahead of time that the request I'm processing will require me to pull data from 5 different tables that are not related to populate different sections of a page.
Is there a way, and would it be more efficient, to combine these into a single database call that returns 5 different data sets?
Something like:
var all_data = context.Select(a => new {
dataset1 = context.Table1.Where(b => b.XXX == XXX),
dataset2 = context.Table2.Where(b => b.XXX == XXX),
dataset3 = context.Table3.Where(b => b.XXX == XXX),
dataset4 = context.Table4.Where(b => b.XXX == XXX),
dataset5 = context.Table5.Where(b => b.XXX == XXX),
});
(obviously the above doesn't work) Then I can do whatever I need with each, for example:
GridView1.DataSource = all_data.dataset1;
GridView1.DataBind();
GridView2.DataSource = all_data.dataset2;
GridView2.DataBind();
I feel this would be more efficient than sending 5 separate calls to the database but can't figure out how to do it. Thoughts welcome.
Upvotes: 4
Views: 1824
Reputation: 114
Don't be so quick to say "Obviously this doesn't work" . I think it does work. It should do what you want in a single query. I get why you would assume that it doesnt work though. It does look like separate queries and therefore separate round trips. But other examples I am reading (and my own experience ) seems to indicate it knows how to combine this into a single query.
See: Subquery with Entity Framework
Also: Try putting a breakpoint in your code and use the debugger to inspect the IQueryable generated (all_data). It should have Sql that combines into a single query.
If I'm wrong here I'd certainly like to know more myself. If this doesnt work, a Union() certainly would work . Though it would be a bit ugly.
Upvotes: 1