Using LINQ And Context Acts Like It Is In Transaction.

I am using VS2008 Team Edition under Vista with SQL Compact 3.5

I am trying to develop an app using C# and LINQ and take advantage of the Sync services. The problem is that everything seems to work but nothing ever really gets saved out to the local .sdf file. I have all the SQL commands logged to the the console and they seem fine. I have SubmitChanges() wrapped in a try/catch and no exceptions are thrown. When I requery the context for the data the new added rows do show up. If while the application is open, I go to VS2008 and query the file I just added to, I don't see the new rows. If I exit the application and try I still don't see them. If I restart the application the rows show up. If I exit the app and refresh the connection to the .sdf file in VS and then restart the application the inserted records are gone.

They act exactly as if when I made my connection to the database I started a transaction (which I didn't do, at least no explicitly) that I never commit. Did I miss some basic point about opening a connection to SQL compact?

I connect when the app launches and I store the context in a member variable of my class which doesn't go out of scope until the application quits. I tried creating a new connection in a local method right around the call to SubmitChanges() but it behaved the same.

Below is the console output from adding the record and then updating it (two calls to SubmitChanges() )



INSERT INTO [Milestone]([Milestone_Key], [MilestoneType_Key], [Inspection_Key], [MilestoneDate], [Description], [RowVersion], [ModifiedOn], [ModifiedBy_Key], [CreationDate])

VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)

-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [74ec904a-0c8d-46e5-8bea-af066d2c2f79]

-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [3]

-- @p2: Input Guid (Size = 0; Prec = 0; Scale = 0) [57702e12-e0d8-487e-bf80-8066bf9ebdd5]

-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [4/17/2008 6:50:52 PM]

-- @p4: Input String (Size = 0; Prec = 0; Scale = 0) []

-- @p5: Input Binary (Size = 8; Prec = 0; Scale = 0) []

-- @p6: Input DateTime (Size = 0; Prec = 0; Scale = 0) [4/17/2008 6:50:52 PM]

-- @p7: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]

-- @p8: Input Int64 (Size = 0; Prec = 0; Scale = 0) [0]

-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Milestone_Key], [t0].[MilestoneType_Key], [t0].[Inspection_Key], [t0].[MilestoneDate], [t0].[Description], [t0].[RowVersion], [t0].[ModifiedOn], [t0].[ModifiedBy_Key], [t0].[CreationDate]

FROM [Milestone] AS [t0]

WHERE [t0].[Inspection_Key] = @p0

ORDER BY [t0].[MilestoneDate]

-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [57702e12-e0d8-487e-bf80-8066bf9ebdd5]

-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

The thread 0x2604 has exited with code 0 (0x0).

UPDATE [Milestone]

SET [Description] = @p7

WHERE ([Milestone_Key] = @p0) AND ([MilestoneType_Key] = @p1) AND ([Inspection_Key] = @p2) AND ([MilestoneDate] = @p3) AND ([RowVersion] IS NULL) AND ([ModifiedOn] = @p4) AND ([ModifiedBy_Key] = @p5) AND ([CreationDate] = @p6)

-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [74ec904a-0c8d-46e5-8bea-af066d2c2f79]

-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [3]

-- @p2: Input Guid (Size = 0; Prec = 0; Scale = 0) [57702e12-e0d8-487e-bf80-8066bf9ebdd5]

-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [4/17/2008 6:50:52 PM]

-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [4/17/2008 6:50:52 PM]

-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]

-- @p6: Input Int64 (Size = 0; Prec = 0; Scale = 0) [0]

-- @p7: Input String (Size = 0; Prec = 0; Scale = 0) [added a record then changed a field and re-saved.]

-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8