LINQ to SQL, Stored Procedures and the Methods Pane (more like Methods PAIN!)

Question

I'm new to LINQ and am having a small issue. I created a DBML file and dragged all of my tables to the design surface. Now, when I try to drag a stored procedure to the methods pane, Visual Studio thinks a second and then doesn't do anything. My method does not show up in the methods pane.

Is some error occurring behind the scenes? If so, how can I troubleshoot it?

Answer 1

It's possible that the LINQ to SQL designer isn't able to figure out the schema on your stored procedure, especially if you use temporary tables. Try changing your stored procedure to just select from the table in question, map it into the designer (by dragging on top of the correct table), then change the procedure back to the original code.

Answer 2

I just tried this myself. I used VS2008 SP1, dragged every table from my DB into the class pane and they added. Then I dragged in a stored procedure. It did take longer to process the SP then the tables but the method showed up without an issue in my methods pane. Your stored procedure may not be able to use the metadata available to successfully create the method for it.

Did I do this right?

Answer 3

Jep, I had the same problem. My fault was that the stored procedure that I was trying to add to the pane had a return result with a geography datatype in it. This is not yet supported by the LinQ2SQL designer.

What you can do to test your returning datatypes (if you are using some exotic ones), is add them as parameters to your stored procedure. The designer will give an error.

For example try adding the following stored procedure to the pane will generate an error:

CREATE PROCEDURE test
(
    @GeographicLocation geography
)
AS
BEGIN
END;