I am currently using Entity Framework with PostgreSQL server, and I am looking for efficient way to use linq/IQueryable with For Update for retrieved rows/row
There will be no deadlock since I will be sorting rows by key, and only need to lock on returned rows, not all included entities.
But I can't seem to find for update extension anywhere, and typing out SQL raw string queries is not feasible, as it will cause so much work to create/debug, so I want to use IQueryable that has For update attached to returned rows.
I know I could use pg_advisory_lock, but I wouldn't know what rows to lock, as the return query is the one that will apply the locks to fetched rows that require changing.
Atm one way I could do this although very inefficient:
1) Fetch row IDs that require changing.
2) Fetch rows by previously found IDs with for update
3) Check in memory if fetched rows still require changing.
3-step process that could be done in 1… :(
Question:
How can I apply ForUpdate to IQueryable?
Are there any alternative ways I could achieve returned rows locking?
Am I missing something?
Hey,
I am currently using Entity Framework with PostgreSQL server, and I am looking for efficient way to use linq/IQueryable with For Update for retrieved rows/row
There will be no deadlock since I will be sorting rows by key, and only need to lock on returned rows, not all included entities.
But I can't seem to find for update extension anywhere, and typing out SQL raw string queries is not feasible, as it will cause so much work to create/debug, so I want to use IQueryable that has For update attached to returned rows.
I know I could use pg_advisory_lock, but I wouldn't know what rows to lock, as the return query is the one that will apply the locks to fetched rows that require changing.
Atm one way I could do this although very inefficient: 1) Fetch row IDs that require changing. 2) Fetch rows by previously found IDs with for update 3) Check in memory if fetched rows still require changing.
3-step process that could be done in 1… :(
Question: How can I apply ForUpdate to IQueryable? Are there any alternative ways I could achieve returned rows locking? Am I missing something?
My ideal usage: