Linq insert without primary key

Question

I need to insert records into a table that does not have a primary key using LINQ to SQL. The table is poorly designed; I have no control over the structure of the table. A table consists of several varchar fields, a text field, and a timestamp. It is used as an audit trail for other objects.

What is the best way to do inserts? Can I extend the partial Linq class for this table and add a "fake" key? I am open to any hack, however kludgey.

Answer 1

LINQ to SQL is not intended for this task, so do not use it. Just insert the insert into the stored procedure and add this procedure to your data model. If you cannot do this, write a regular function with a bit of embedded SQL.

Answer 2

Open your DBML file in the constructor and give it a key, regardless of whether your database has one or not. This will solve your problem. However, be careful that you cannot rely on a column to be used for identification or anything else if the database does not have an authentic key.

Answer 3

I managed to get around this with a complex key.

I had a similar problem with a table containing only two columns: username, role. This table obviously does not require an identity column. So, I created a composite key with the username and role. This allowed me to use LINQ to add and remove records.

Answer 4

You can use the DataContext.ExecuteCommand method to run your own custom insert statement.

Or you can add a primary key to a column, this will allow the objects that need to be tracked for insert / update / delete using datacontext. This will work even if the column is not really a forced primary key in the database (how does LINK know?). If you only do inserts and you will never use the primary key value in the same data text, everything will be fine.