Is it possible to turn off concurrency violation checking?

[isaacabraham]

Description

Using the SQL Programmability Provider, I'm trying to use it for updates to existing rows. I only have a subset of columns in the table that need updating, and don't need to load in data first. The way I'm doing it currently is:

1. Create a new data row with blank values.
2. Call AcceptChanges()
3. Modify the appropriate fields with the values to update.
4. Call Update().

The issue is that the generated SQL also adds in where clauses to ensure that all the other values are the same as before, which obviously doesn't work since it expects all values to be blank. Is it possible to turn this check off for the dynamically generated SQL? The only other alternatives that I can see are:

1. Load in the data before performing the update - not needed in my scenario and will greatly increase the number of SQL calls.
2. Switch to hand-crafted update statements, either using a custom data adapter or using the SQL Command Provider.

[mauriciomagni]

May I suggest adding a primary key? it should remove the need of using all the fields in the where clause. If the engine cannot locate the row it should make an update for all the rows in the table, a kind of update table set ... --No Where clause

[isaacabraham]

@mauriciomagni I believe that the PK was already there. It's not about that - it's that it's trying to do some kind of optimistic concurrency check (at least I think that's what it is - this is going back years to the old ADO .NET days)

[smoothdeveloper]

@isaacabraham AFAIR, this kind of behaviour derives from SqlDataAdapater, data adapters tend to be cumbersome in my experience.

I think paying a look at https://github.com/fsprojects/FSharp.Data.SqlClient/blob/fb8706f1d96c26c9ca68f3c98df2c5a4a057ba0e/src/SqlClient/DataTable.fs#L59-L86 and considering if we could replace usage of data adapter with some other logic to enable the use case you are looking would be the way to go.

[isaacabraham]

If this is "core" behaviour of the Sql DA, I think this is more a feature add then - I would leave this is an option feature request as up-for-grabs and see if someone wants to add it (otherwise next time we need this, we will submit a PR).