I have a view and it's composed of two tables. I want to edit a value in each table through the view and save those changes but LINQ is throwing an error about not being able to edit two values on the same view.
Does anyone know of a good workaround?
Thanks
SQLServer doesn't support updating columns from multiple tables on a view. You may be able to get around this by separating the column changes so that you only update the columns on one table, submit your changes, update the changes on the other table, and then submit changes again. You could also use a stored procedure to update the individual tables independently in the same transaction. This stored procedure could be added as a method on your data context using the designer. Assuming that it returns the same schema as the view when it completes, it could return an object of the same type as the view.
Updatable Views You can modify the data of an underlying base table through a view, as long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following: An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP. A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable. The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses. TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
While @tvanfosson's is answer is absolutely correct, if you are using LINQ-to-SQL, you can instead reference both tables directly in your code, get your collection of objects, update them, and push them back. Upon issuing the update command, my understanding is that LINQ-to-SQL will place all the commands in a transaction and handle it for you.
A third option is to create an INSTEAD OF trigger, where you write the code to split the updated values into two different queries.