C# – LINQ to SQL : How tonsert record into a view

clinq-to-sql

I'm using Linq to SQL for the Database operations, and am trying to perform insert operations in a VIEW, It throws the Error,

XXt threw exception:  System.InvalidOperationException: Can't perform Create, 
Update or Delete operations on 'Table(XXX)' because it has no primary key.

How to use LINQ to insert a record into View using C#?

Thanks.

Best Answer

You can insert/update into views as per Updatable Views here. Only one underlying table can be inserted/updated to or it will fail. To implement this functionality using LINQ, do the following;

  • In your .DBML file tag one (or more) of the columns in the view as a Primary Key
  • Ensure any mappings in the view you are expecting to insert/update are exposed simply as a link to the base table column. Example;
    • Insertable/Updatable columns cannot include;
      • SUM(BaseTable.ColumnName) as ColumnName
      • ISNULL(BaseTable.ColumName,0) as ColumnName
      • BaseTable.ColumnName1 + ' ' + BaseTable.ColumnName2 as ColumnName
    • But can include;
      • BaseTable.ColumnName
      • BaseTable.ColumnName as MyNewName
  • Tag any of the columns that are not direct mappings to the base table as Auto Generated Value in your .DBML.

Give it a shot. I am successfully using this technique to use views as the only objects i use for both reading/inserting/updating records.

Related Topic