Here are two tables,one named State
, the other named WorkingOn
.
workers_name state
aaa onduty
bbb offduty
ccc onduty
ddd offduty
workers_name working_on
aaa projectA
aaa projectB
aaa projectC
bbb projectB
bbb projectC
ccc projectA
ccc projectB
ccc projectC
ccc projectD
ddd projectC
Now I want to get the worker which is on duty and working on the least number of projects.
I tried to achieve this by SQL code:
select state.name,count(workingon.working) from state join workingon on state.name=workingon.name group by state.name
However, I want to achieve it by Entity framework Core.
Would you please help me? Thank you.
=====================================
Here is the code of back-end:
public class TestDBContext : DbContext
{
public virtual DbSet<statemodel> state { get; set; }
public virtual DbSet<workingonmodel> workingon { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Data Source=123.db");
}
public TestDBContext(DbContextOptions options) : base(options)
{
}
protected TestDBContext()
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
builder.Entity<statemodel>().HasNoKey();
builder.Entity<workingonmodel>().HasNoKey();
base.OnModelCreating(builder);
}
}
public class statemodel
{
public string name { get; set; }
public string state { get; set; }
}
public class workingonmodel
{
public string name { get; set; }
public string working { get; set; }
}
Now I want to get the worker which is on duty and working on the least number of projects.
You can get workers which are on duty, order them by the number of projects and take the first one.
i.e. given
DbContext db = ...;
var state = db.Set<statemodel>();
var workingon = db.Set<workingonmodel>();
the query would be something like this:
var workerInQuestion = state
.Where(s => s.state == "onduty")
.OrderBy(s => workingon.Count(w => w.name == s.name))
.Select(s => s.name)
.FirstOrDefault();
Well, it works! I am surprised that it doesn't need to join the table while I consider it is a must.