LINQ update on table having no primary key


I was trying to update a record in a table last night, using LINQ. Everything was fine and running without any errors.
Surpisingly the record was not getting updated. I then attached the modified object to the context. It started giving an exception as,

Can't perform Create, Update or Delete operations on Table because it has no primary key.

Then I realised that there is no primary key assigned to the table. The possible solution was to change the database schema and add primary key 
to that table. But this change was not acceptable.
Further debugging into the code I got the workaround for this. 

When we generate a class file using a tool such as sqlmetal or anything else, certain attributes are associated with the columns of that table.
We can open that class file and manually add IsPrimary= true attribute to any one of the columns.

This will fool the LINQ engine and treat the respective column as a primary key.

Here is an example for the same.
I have generated a class with table as TableWithNoPK. It has 3 columns as Field1, Field2, Field3.
I assigned IsPrimary attribute to field1 as,

[global::System.Data.Linq.Mapping.ColumnAttribute(Name="field1", Storage="_Field1", DbType="VarChar(50)", IsPrimaryKey= true)]
        public string Field1
        {
            get
            {
                return this._Field1;
            }
            set
            {
                if ((this._Field1 != value))
                {
                    this._Field1 = value;
                }
            }
        }


The c# code goes normal as,

var qry = from NoPK in instance.TableWithNoPK
                      select NoPK;

       TableWithNoPK test = qry.FirstOrDefault();
       test.Field3 = "Gud 1";
       instance.SubmitChanges();


No comments:

Post a Comment

Labels

.net .Net Instrumentation logging .net localization Agile amazon amazon elasticache amazon services AppDomain Application Domain architecture asp ASP.Net authentication authentication mechanisms Byte order mark c# cache cdata certifications class classic mode cloud cloud computing cluster code-behind Combobox compilation Configuration providers configurations connection connectionString constructors control controls contructor CSV CTS .net types conversion database DataGridView DataSource DataTable DataType DBML delegates design pattern dispose double encoding Entity framework Events exception handling expiry fault contracts fault exceptions function pointers functions generics help HostingEnvironmentException IIS inner join instance management integrated mode javascript join left outer join LINQ LINQ join LINQ to SQL memory leak methods microsoft modes in IIS MSIL multiple catch blocks no primary key Nullable Osmos Osmotic Osmotic communication Osmotic communications page events page life cycle partial class PMI preserve precision points private contructor ProcessExit Project management properties property protect connectionString providerName providers query regular expression repository Responsive Web Design return type run-time RWD Saas self join session session expiry sessions singelton singleton pattern software as a service source control system SQLMetal string toolstrip ToolStrip controls ToolStripControlHost tortoise SVN ToString() try catch finally update wcf web application web design web site web.config where-clause xml