Is it possible to use Database First EF 6 with SKIP LOCKED command?

Viewed 386

I'm using a SQL table as a job queue very similar to the article here: https://vladmihalcea.com/database-job-queue-skip-locked/

My problem is that I'm using Entity Framework 6 with Database First code and from what I can tell, EF6 doesnt' support the skip locked command. Here is my table class and I'm using each computer as a worker to handle the task I'm passing it.

public partial class InProgress
{
    public int ID { get; set; }
    public string Task { get; set; }
    public string Computer { get; set; }
    public DateTime Date { get; set; }
}

Does anyone have any C# code they can share so I can make sure that no other computer can work on the same task as another computer at the same time?

UPDATE: I want to clarify that I'm not doing a traditional queue where you constantly add and remove to the queue or in this case table. I have a table that contains a task list and I'm constantly having the tasks worked on by multiple computers and when they are finished, they update the Date column with the finished time. I work on the tasks that have the oldest Date first.

Here is some pseudo code of what I'm trying to do based on the info provided

create procedure usp_enqueuePending
  @date datetime,
  @task varchar(50),
  @computer varchar(50) 
as
  set nocount on;
  insert into InProgresses(Date, Task, Computer)
    values (@date, @task, @computer);
go

create procedure usp_dequeuePending
as
  set nocount on;
  declare @now datetime;
  set @now = getutcdate();
  with cte as (
    select top(1) 
      Task
    from InProgresses with (rowlock, updlock, readpast)
    where Date < @now
    order by Date)
  delete from cte
    output deleted.Task;
go

using var context = new context();
var dequeuedItem = context.usp_dequeuePending(); // not sure how to convert this back to an InProgress class
// do work here I'm guessing
// add to the queue when finished with it??
context.usp_enqueuePending(DateTime.UtcNow, task, computer);
1 Answers

You can write custom queries in EF Core, see here. So you could do something like this:

dbContext.InProgress
  .FromSqlRaw("SELECT * FROM model.InProgress WITH (rowlock, updlock, readpast)")
  .Where(...) // do other LINQ stuff here

It's not super pretty, but I don't know of a better solution at the moment.

Related