这是两个表,一个命名State
,另一个命名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
现在我想让值班的工人从事最少的项目。
我想通过SQL代码实现这一目标:
select state.name,count(workingon.working) from state join workingon on state.name=workingon.name group by state.name
但是,我想通过 Entity framework Core来实现。
你能帮我吗?谢谢。
====================================
这是后端代码:
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; }
}
现在我想让值班的工人从事最少的项目。
您可以让值班的工人,按项目数量订购他们,然后参加第一个项目。
即给
DbContext db = ...;
var state = db.Set<statemodel>();
var workingon = db.Set<workingonmodel>();
查询将是这样的:
var workerInQuestion = state
.Where(s => s.state == "onduty")
.OrderBy(s => workingon.Count(w => w.name == s.name))
.Select(s => s.name)
.FirstOrDefault();
好吧,它起作用了!令我惊讶的是,它不需要加入表,而我认为这是必须的。