How to corretly load DataContext of Conditional Linq-to-SQL Stored Proc

Question

I have a Stored Proc that do a validation on a parameter

ex.

IF @SearchType = 'BNa'
BEGIN
    ... DO something
END
ELSE IF @SearchType = 'SNa'
BEGIN
    ... DO something
END

So by default the Stored Proc return a scalar value and if SearchType = something valid it will return a IMultipleValues.

The problem is that when I drop my Stored Proc in the DataContext designer, it creates the LINQ-to-SQL for a function that only returns a scalar int. It doesn't understands that the Strored Proc could return a IMultipleResults with a scalar value and a DataSet.

Anyone know how to made LINQ-to-SQL to extrapolate the possible return values?

Answer 1

I can't answer your question directly, but have you tried using SQL Profiler to work out how the designer retrieves the metadata for your stored procedure?

I'm thinking that in your situation it may not be possible for the designer to figure things out for you. SQL Profiler will let you figure this out for sure either way...

I've had quite a positive experience with using SqlMetal (instead of the designer). To save you Googling the documentation is at http://msdn.microsoft.com/en-us/library/bb386987.aspx.

One of the ways you can use SqlMetal is:

1. Run SqlMetal to extract database metadata to a DBML file.
2. Run some custom processing on the DBML file (which is easy to do as it's just XML).
3. Run SqlMetal again to turn the updated DBML into code.

Using this approach, you could correct the metadata being used for your stored procedure (assuming it can't be determined correctly).

The winning thing about this approach for me is that you can modify your database whenever you need to, and easily regenerate the matching strongly typed code, while still retaining a high degree of control over what is generated.