multiple recordsets returned by Store Procedure LINQ to SQL

[Suba]

Hi there

I tried an LINQ example where my Store procedure returns multiple result
sets. I am facing a problem with getting the results out by changing the
designer generated code to reurn IMultipleResults instead of ISingleResult. I
have specified the result types also. I am able to retrieve data. I followed
the steps given in this

http://blog.linqexchange.com/post/2008/06/How-to-Retrieve-Multiple-Recordsets-Using-LINQ-to-SQL.aspx

But facing a problem with the order of retrieval. Suppose am returning 2
record sets from the SP in the order First Country and second State. When i
have to access State its not returning me the values properly. When i
debugged i found out that the order of the method call is important. To get
the state list i have to call Country first and then call State. Other wise
its not filling my State list.

For some reason the order is causing the problem. Have you encountered
anything like this. Any idea why this is happening?

 

[Marc Gravell]

For some reason the order is causing the problem. Have you encountered

anything like this. Any idea why this is happening?

Well, under the bonnet it is probably consuming an IDataReader as you
read rows, which each call to GetResult moving to the next grid
(NextResult). LINQ is an object pipeline ultimately - it lazily spools
rows from the connection, so it makes sense that you can only access
data in the right order.

Marc

 

[Suba]

Supppose Country is returned as the first result set and State as the second
result set.
The problem is if i need to access State information first i have to do

IMultipleResults res = FetchLookups();
List<Country> c = res.GetResult<Country>().ToList();
List<State> s = res.GetResult<State>().ToList();

I cannot do

IMultipleResults res = FetchLookups();
List<State> s = res.GetResult<State>().ToList();

When i just call State it doesnot work. I have to make a call to fetch
countries and then state.

I would need some kinda dataset kind of implementation where i can do
dataset.Tables["Person"]. Does LINQ support this any idea?

 

[Marc Gravell]

I have to make a call to fetch countries and then state.
Yes; as already explained, this just fetches the *next* result grid.
http://msdn.microsoft.com/en-us/library/bb534218.aspx

I would need some kinda dataset kind of implementation where i can do
dataset.Tables["Person"]. Does LINQ support this any idea?

Not on an IMultipleResults, no. LINQ is an object pipeline, not a store
[forgetting the change/identity managers for the moment]. However, it might
be possible to use LINQ-to-objects functionality to wrap this into more
concrete lists if you need - kinda like you have already done with the
ToList().

Marc