Linq to SQL - Can't Update dbml File
Since I started using LINQ to SQL on a regular basis, I've been working with stored procedures. LINQ syntax is great, but I tend to use the LINQ syntax just on LINQ to Objects. For LINQ to SQL, I connect with stored procedures on the SQL Server rather than using LINQ to SQL's dynamic SQL creator. This is mainly for database reasons, there are strong arguments for using stored procedures over dynamic SQL queries created in LINQ to SQL. This is partly due to the Query Execution Plans which get created on SQL Server, been able to control when these Execution Plans get re-created, and also optimising the query based on database statistics, which isn't something LINQ to SQL deals with. Also as Microsoft have said they're not going to be providing any more updates for LINQ to SQL, they're concentrating on the Entity Framework, it seems like a technology which will fade away. However I have to admit it's the current method I use for creating a Data Access Layer (DAL), albeit one which ultimately calls Stored Procedures, using LINQ to SQL
Can't Update the .dbml File
This leads me to a problem I've been having recently, where I drag a stored procedure on from the Server Explorer in Visual Studio onto the .dbml file, which is all very nice and easy, but when it comes to changing the stored procedure such that it returns a different set of datatypes, or it's input parameters change, it's not been so easy to get the dbml file to reflect this, or not at least in a way I expect it to.
So my previous workaround was to create a new .dbml file, dragging the changed stored procedure to this file which generate the auto generated code correctly for the new version of the stored procedure, I'd then simply copy that code to a new file I'd call 'dbmlFilename-extension.cs' and then everything was fine to work from that file. Not the nicest way to do it though.
How to Update .dbml with an Updated Stored Procedure
A bit fed up with this, I managed to work out a better way to handle this, in what would appear to be the way it was designed to be done, just my thinking was maybe a little different expecting it to work in another way, but now I've discovered it, it makes good sense.
1. Delete the stored procedure from the design surface of the .dbml file
2. Click Save All files
3. Click Refresh in Server Explorer on the list of Stored Procedures
4. Add (drag) the stored procedure back onto the design surface of the .dbml file
5. Click Save All
6. Click Build
7. Check the designer.cs code file and you will have the updated C# code for the new version of the stored procedure
I had a bigger list to start with and I've been removing steps, so it's possible without further testing that some more steps could be removed.
Still Can't Update the .dbml File
Since writing the above I've found this technique doesn't always work. I've yet to work out exactly how it decides when to grab a 'fresh' version of the sproc. However, I've found the following techniques assist in getting a correct refresh:
1. Add a new parameter to the sproc, then do perform the above. You'll be able to see exactly when it has a fresh version by looking whether the newly generated file contains the new parameter.
2. Rename the sproc, refresh, then rename it back. This appears to be a more solid solution and the one I currently perform when the all of the above fail.