LinqToSql
SubmitChanges() not working
Have you ever tried to update a row in a database table
using Ling ToSql and found that it does not work?
I had that situation and could not work out what was wrong.
I was using simple code like the extract below:
using
(databaseDataContext db = new databaseDataContext())
{
//Update
a record and display info
localization
item = (from o in
db.localizations
where o.Pk == 16606
select o).First();
item.ResourceSet = “newtext”;
db.SubmitChanges();
}
After messing about with the code I checked the ‘SQL Server
Profiler tool’ and that showed me that there was no UPDATE statement being sent to the SQL server.
Why no UPDATE statement?
Because my database table called ‘localizations’ has no Primary Key defined. The table did have a column called ‘Pk’ but it
was not defined as a primary key in the Database Table. If there is no Primary
Key defined then LinqToSql treats the table as read only.
To fix this I opened SQL Server Management Studio and designated
the column ‘Pk’ as the primary key for the ‘localization’ table. I also had to
do the same in my LinqToSql dbml file. After
specifying a primary key the SubmitChanges() command
worked and the UPDATE statement was
sent to the SQL server.
Note: The localization table I was using came from the Updated
Westwind.Globalization Data Driven Resource Provider for ASP.NET which is a very good project for
enabling and managing multiple languages in your website or application. Most people would not need to
run LinqToSql commands on the table but I need to correct a mistake in the
naming of my resource sets.
Note: do not use the dash character ‘-‘ in the name
of resource sets. Dot Net does not like the name of a resx file to have a ‘-‘ in it.