Linq insert with no primary key

Question

I need to insert records into a table that has no primary key using LINQ to SQL. The table is poorly designed; I have NO control over the table structure. The table is comprised of a few varchar fields, a text field, and a timestamp. It is used as an audit trail for other entities.

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

Answer 1

LINQ to SQL isn't meant for this task, so don't use it. Just warp the insert into a stored procedure and add the procedure to your data model. If you can't do that, write a normal function with a bit of in-line SQL.

Answer 2

Open your DBML file in the designer, and give the mapping a key, whether your database has one or not. This will solve your problem. Just beware, however, that you can't count on the column being used for identity or anything else if there isn't a genuine key in the database.

Answer 3

I was able to work around this using a composite 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 username and role. This enabled me to use LINQ for adding and deleting entries.

Answer 4

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

Or, you might add a primary key to a column, this will allow the objects to be tracked for inserts/updates/deletes by the datacontext. This will work even if the column isn't really an enforced primary key in the database (how would linq know?). If you're only doing inserts and never re-use a primary key value in the same datacontext, you'll be fine.