CODEWITHSUNDEEP
sqllinqnhibernate
JustAMartin
JustAMartin

Reputation: 13733

Query for entities which are not joined with some other certain entities

I am trying to implement a custom task scheduler system. I have a following (simplified) entity model:

class User
{
    public virtual long UserId { get; set; }
    public virtual string Name { get; set; }
}

class Task
{
    public virtual long TaskId { get; set; }
    public virtual long? UserId { get; set; }
    public virtual string TaskName { get; set; }
    public virtual Guid SchedulerSessionUid { get; set; }
}

For now, corresponding SQL tables are straight forward with fields mapping exactly as they appear in the classes above.

The scheduler is a C# Windows console app. It will run once per day. It should work in a following way (simplified):

  1. generate a Guid for current session
  2. try selecting next User entity, which does not already have a task scheduled in the current scheduler session (Guid compare); if no such a User found, go to the step 5.
  3. add a new Task for the user selected in the step 2.
  4. go to step 2.
  5. exit the application

It seems a pretty trivial problem, but I have a problem implementing the second step. I have tried various queries, but there are some rules which always stop me.

Here are some rules which I have to obey while implementing the step 1:

Here are some real world example how it should work.

Users
-----------------
UserId    Name
-----------------
1         First
2         Second


Tasks
--------------------------------------------------------
TaskId    UserId    SchedulerSessionUid 
--------------------------------------------------------
1         NULL      6d8e48d0-4e92-477e-82fa-cd957e7dc201    
2         1         d213cfc8-23d6-49fb-b4e3-9ff3b60af6c4
3         1         9ee042df-88a7-447e-adbd-e7551ed50ae5

1.Now when the Scheduler runs, it generates a current session id = 76ea57fa-8c89-4c05-9ca2-a450b1f8a032.

  1. Now it should issue the magical LINQ query to NHibernate LINQ provider to get a User entity
  2. In the first iteration the query should return the User entity with UserId=1 because there are no tasks in the current session for that User yet
  3. Now the Scheduler creates a new task with UserId=1, SchedulerSessionUid=76ea57fa-8c89-4c05-9ca2-a450b1f8a032.

In the next iteration the Scheduler should get a User with UserId=2. Again, a new task is inserted with UserId=2, SchedulerSessionUid=76ea57fa-8c89-4c05-9ca2-a450b1f8a03. In the next iteration the Scheduler should get no users, so it exits.

What LINQ query could I use to get the User for the step 2? What changes in my SQL schema and entity model do I need?

Upvotes: 1

Views: 52

Answers (1)

Jon Hanna
Jon Hanna

Reputation: 113322

If I now follow you correctly, you need to get a (just one) user for which there are no tasks with the given session id. Am I correct?

Users.Where(u => !Tasks.Any(t = > t.UserId == u.UserId && t.SchedulerSessionUid == curSession)).FirstOrDefault()

Edit:

Since you're doing several spins through this, would you perhaps be faster doing:

foreach(var user toDealWith Users.Where(u => !Tasks.Any(t = > t.UserId == u.UserId && t.SchedulerSessionUid == curSession)))
{
  //do stuff
}

Rather than keep hitting the database each time?

Upvotes: 1

Related Questions