Warm tip: This article is reproduced from stackoverflow.com, please click
entity-framework entity-framework-core

How to get the smallest count one in entityframe core?

发布于 2020-04-09 22:55:21

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; }

    }
Questioner
Melon NG
Viewed
52
Ivan Stoev 2020-02-01 21:51

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();