Linq2SQL: Why doesn't datacontext update database

Question

This is my first go at LINQ to SQL. I create my dbml in the designer using a SQL Server Express database. I create a stored procedure called StoredProcedure1, which I also dragged into the dbml. Then I wrote up some code to test if I could update some of the fields. The problem is that no updates occur to the database, but I see the changes when the data is bound to a DataGrid. I have no idea why it's not working or what I am doing wrong. Here is the code:

DataClasses1DataContext data = new DataClasses1DataContext();
List<StoredProcedure1Result> result = new List<StoredProcedure1Result>(data.StoredProcedure1());


int i = 0;
foreach (StoredProcedure1Result r in result)
{
    r.TestField = "A" + i.ToString();
   i++;
}

// to see what has changed, set break point on next line
var changeSet = data.GetChangeSet(); 

// submit changes and show result in datagrid
data.SubmitChanges();
dataGrid.ItemsSource = result;

The stored procedure just grabs all of the records from a table:

    USE [E:\test\test.MDF]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[StoredProcedure1]
AS
    SELECT  * FROM TEST_TABLE
    RETURN

Answer 1

Well if you have no idea what you're doing wrong, probably you could try something simpler, such as a classic select query on some existing objects in the database, update some fields and submitChanges(). Then you could build on your example by binding your Grid to the results returned by your select, and call SubmitChanges() in the grid's correct event handler.

Answer 2

You should directly change the records from the database through LinqToSql. Changing the results that you retrieved from the stored procedure will not update the database.

Try something like this, instead of calling the stored procedure:

var result = data.TEST_TABLES();

Answer 3

I figured it out. In the LINQ2SQL designer, set the return type for StoredProcedure1 to TEST_TABLE instead of auto-generated.