LINQ to SQL - returning values from a multi select stored proc

Question

Is is possible for a LINQ stored proc query to return values from a procedure that contains multiple select statements. Like 'GetNext..' in a DataReader.

 

Jimm

 

Answer 1

Yes you can, here is a walk through:

 

http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/10/05/linq-to-sql-stored-procedures-with-multiple-results-imultipleresults.aspx

Answer 2

I tried this as described - the first select returns properly but any additional sets end up throwing this error ::

 

"The required column 'RegionID' does not exist in the results"

 

 

Jimm

 

Answer 3

i would think that your second select in the stored proc is missing the region ID column, NOTE: result set in the stored

proc should return the same columns as your LINQ entity, if you dont want to use that column / property in the LINQ entity, just delete it from the LINQ To SQL designer.

Answer 4

The proc code does containt the RegionID. Some added details to what I'm working with is that I have been using SQLMETAL to produce the initial wrapper code. I creates some code like this ...

 

[Function(Name="dbo.usp_GetLookups")]

[ResultType(typeof(Usp_GetLookupsResult1))]

[ResultType(typeof(Usp_GetLookupsResult2))]

public IMultipleResults Usp_GetLookups()

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

return ((IMultipleResults)(result.ReturnValue));

}

 

I've tried it as is and also have changed it to the following and still have the same problem...

 

[Function(Name="dbo.usp_GetLookups")]

[ResultType(typeof(Language))]

[ResultType(typeof(Region))]

...

public IMultipleResults Usp_GetLookups()

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

return ((IMultipleResults)(result.ReturnValue));

}

 

 

the code that's causing the error follows::::

 

 

var db =

new ExceleratorMConfig("Data Source=localhost;Initial Catalog=ExceleratorMConfig;Integrated Security=SSPI");

IMultipleResults results = db.Usp_GetLookups();

IEnumerable<Region> l = results.GetResult<Region>();   <<-- I Get The Error Here...

foreach (var t in l)

{

Debug.WriteLine(t.Description);

}

 

Jimm