Using multiple resultsets combined with joins in T-SQL

I'm currently using joins inside my stored procedures for outputting elements from different tables. An aggressive example

select a.*, b.*, c.*, d.*, e.*, f.* from tableA a
join tableB b on a.id = b.foreignid
join tableC c on b.id = c.foreignid
join tableD d on c.id = d.foreignid
join tableE e on d.id = e.foreignid
join tableF f on e.id = f.foreignid
where a.id = 1

It's getting pretty unhandy to work with when mapping the output to entities in my C# code, since I have to maintain a lot of boilerplate code. Instead I would look into using multiple resultsets, so that I could map each resultset into an object type in code. But how would I go around achieving this when I my case the different results would all relate to each other? The examples I've been able to find all revolved around selectin开发者_StackOverflowg from different tables where the data were not related by foreign keys like mine. If I were to ouput my result in multiple resultsets the only thing I can come up with is something like this

select a.* from tableA
where a.id = 1

select b.* from tableB
join tableA a on a.id = b.foreignid
where a.id = 1

select c.* from tableC
join tableB b on b.id = c.foreignid
join tableA on a.id = b.foreginid
where a.id = 1

select d.* from tableD
join tableC c on c.id = d.foreignid
join tableB b on b.id = c.foreignid
join tableA a on a.id = b.foreignid
where a.id = 1

select e.* from tableE
join tableD d on d.id = e.foreignid
join tableC c on c.id = d.foreignid
join tableB b on b.id = c.foreignid
join tableA a on a.id = b.foreignid
where a.id = 1    

select f.* from tableF
join tableE e on e.id = f.foreignid
join tableD d on d.id = e.foreignid
join tableC c on c.id = d.foreignid
join tableB b on b.id = c.foreignid
join tableA a on a.id = b.foreignid
where a.id = 1    

But this is not cleaner, a lot more ineffecient (I would suppose, since there's alot more join statements) Is it possible to use multiple resultset in this way I'm trying to? I just don't know how I would write the sql statements in the stored proc without having to do massive joins per resultset as in the example. And with the current solution I get an explosion of columns since I join them all together

You can actually return multiplte resultsets from a single SP and consume them in c#, check this post for instance : http://blogs.msdn.com/b/dditweb/archive/2008/05/06/linq-to-sql-and-multiple-result-sets-in-stored-procedures.aspx

It's a lesser known feature but sounds like what you're asking for. You don't have to join them and return them as a flattend rowset, just get the seperate rowsets and piece them together in memory.

Also you may want to read up on ORM frameworks, that could save you a lot of typing that you coud spend on features if it fits your needs. https://stackoverflow.com/questions/249550/what-orm-frameworks-for-net-do-you-like-best

Regards GJ