Get unrelated entities in one trip to the database

Question

Hello,,

I have 3 entities that are not related to each other and I want to get all these entities in one trip to the database how can I do this ??

Thanks

Answer 1

You can do it with:

 var result = from foo in ctx.Foos
              from bar in ctx.Bars
              where foo.id == xxx && bar.id == yyy
              select new { Foo = foo, Bar = bar};

This will fetch you a specific foo and a bar in the same query. Although, this will generate some pretty inefficient SQL so I wouldn't recomend it.

Answer 2

If you are retrieving data from a pure relational database, you should look to see if you can retrieve multiple result sets in a single query. Alternately, if you can fetch ragged results, retrieve these in a tree structure with the corresponding shape.

If your connection to the database is not smart enough for this, you could use a universal join of the results and then return a union query tagged in some form to identify which result is from which table.

You don't state the reason for the reduced round-tripping, but you may also be able to use something like a stored procedure to collect the required data at the server and then return it in one result set.