Return Multiple Results in Linq2Sql without a stored procedure?

Question

Hi folks,

i would like to return two record sets from a simple database table with one Linq2Sql query. I know how to do it if this was using Linq2Sql calling a stored procedure, but I don't want to use a stored procedure.

Is it possible to do it?

I've found an article here that has a suggested solution, but i hate the idea of having to write up a massive amount of code to partially extend the current context?! like... OUCH!!!

Just doesn't seem... right ?

Is the suggestion in the article the only way to do it? Are there other ways (without using stored procedures and still using Linq2Sql) ?

Wish Matt Warren was here to answer this :)

EDIT

I'm not asking about how to lazy-load / eager load (and using DataLoadOptions). That's a different concept.

Answer 1

That's the only way I've heard of it being done without a stored procedure. And you're right it does seem a bit excessive for a seemingly simple concept. If it was me I just get the records as separate result sets.

Answer 2

Potentially you may do this with Multiple Active Result Sets (MARS) which I found from this page. It's an MSDN article on the topic, but does not specifically relate to LINQ to SQL, however this one does and probably the one you wanna check out.

Having said that, good luck because it looks like there's a bug posted to Microsoft regarding how it doesn't work, and the fix won't be here until .NET 4.0!

Lastly I understand you say you do not wish to use Stored Procedures, but if you do, I found a really simple guide here and here to get going.