How can I get a list of objects related to a min from a Linq-CodePudding

I have a Linq Query that is working as intended, but I need to add the code so that it will show me ONLY the people with the less cases assigned for an app that is been used to treat customers inquiries. The idea behind the query is so that it will let me automatically assign inquiries randomly between those agents which have less assigned issues to cover.

As a simple example, lets say I have 5 agents with just 1 case each, I need to randomly assign one of them to an Inquire which has currently no agent assigned. So all I'm looking for is a way to actually get all the agents with the smallest number of cases assigned.

So far this is the full proof of concept code:

var inquires = new List<Inquire>();
var agents = new List<Agent>();

LoadData();

var assignationsPerAgent = (from agent in agents 
                        join inq in inquires on agent equals inq.AssignedAgent into agentsInInquires
                        select new {
                            o_agent = agent,
                            casesAssignedTo = agentsInInquires.Count()
                        }).ToList();


//This works but is NOT the kind of solution I'm looking for
var min = assignationsPerAgent.Min(c => c.casesAsignedTo);
var agentWithMin = assignationsPerAgent.Where(a => a.casesAsignedTo == min);

Console.WriteLine();

void LoadData()
{
    agents = new(){
        new Agent{ Id = Guid.Parse("317d3d26-25c2-49da-aa4b-b7e49a1b9015"), Name = "Robert" },
        new Agent{ Id = Guid.Parse("84188e21-8147-498f-bc2a-59874dc4a24a"), Name = "Corina" },
        new Agent{ Id = Guid.Parse("90ca6658-95d4-4df4-a072-159087feddc0"), Name = "John" },
        new Agent{ Id = Guid.Parse("34e091e4-cc7a-4222-9885-5de5bb5a0291"), Name = "Jack"},
        new Agent{ Id = Guid.Parse("f22dcb4e-e927-4ddf-ae66-f37c0de6753d"), Name = "Samuel"}
    };

    inquires = new(){
        new Inquire{ CustomerName = "Paula", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("317d3d26-25c2-49da-aa4b-b7e49a1b9015"))},
        new Inquire{ CustomerName = "Barry", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("84188e21-8147-498f-bc2a-59874dc4a24a"))},
        new Inquire{ CustomerName = "Bertie", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("84188e21-8147-498f-bc2a-59874dc4a24a"))},
        new Inquire{ CustomerName = "Herman", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("90ca6658-95d4-4df4-a072-159087feddc0"))},
        new Inquire{ CustomerName = "Ashley", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("317d3d26-25c2-49da-aa4b-b7e49a1b9015"))},
        new Inquire{ CustomerName = "Tate", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("90ca6658-95d4-4df4-a072-159087feddc0"))},
        new Inquire{ CustomerName = "Bonnie", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("90ca6658-95d4-4df4-a072-159087feddc0"))},
        new Inquire{ CustomerName = "Tabitha", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("90ca6658-95d4-4df4-a072-159087feddc0"))},
        new Inquire{ CustomerName = "Ashley", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("34e091e4-cc7a-4222-9885-5de5bb5a0291"))},
        new Inquire{ CustomerName = "Josie", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("84188e21-8147-498f-bc2a-59874dc4a24a"))},
        new Inquire{ CustomerName = "Kelly", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("84188e21-8147-498f-bc2a-59874dc4a24a"))},
        new Inquire{ CustomerName = "Kelly", AsignedAgent = agents.Single(a => a.Id == Guid.Parse("f22dcb4e-e927-4ddf-ae66-f37c0de6753d"))},
    };
}

#nullable disable
class Inquire
{
    private Guid _id;

    public Guid Id 
    { 
        get
        {
            return _id;
        }
        private set 
        {
            _id = Guid.NewGuid();
        }
    }
    
    public string CustomerName { get; set; }
    public Agent AsignedAgent { get; set; }
}

#nullable disable
class Agent
{
    public Guid Id {get; set;}
    public string Name { get; set; }
}

Please take in consideration that the assignationsPerAgent variable is simulating data coming from a query in the database (The actual query is below). If the "easy" way to do this comes from the SQL that it's also an acceptable solution.

SELECT u.Id, COUNT(g.Id) as QtyAsig
FROM Users as u
LEFT JOIN GeneralInquires AS g ON u.Id = g.UserId
GROUP BY u.Id
ORDER BY COUNT(g.Id)

What I'm getting from this Query is:

Id                                      QtyAsig
8A21A6D2-0CEC-4F5C-2A6B-08DA60967E94    1
323C8D1A-2FAE-4ECC-D7A2-08DA6098F19A    1
BA485F3C-C44A-4FE5-9BFA-08DA64EF283A    1
8F0E856E-FA0B-4167-BBEF-08DA6451FA81    2
40952727-5C76-4902-9C4F-08DA638B3068    3
DD51085A-5BE3-4872-F4B5-08DA6E7828AA    4

What I need is:

Id                                      QtyAsig
8A21A6D2-0CEC-4F5C-2A6B-08DA60967E94    1
323C8D1A-2FAE-4ECC-D7A2-08DA6098F19A    1
BA485F3C-C44A-4FE5-9BFA-08DA64EF283A    1

Thank you in advance!

CodePudding user response：

Linq doesn't require one statement and splitting results has no impact on performance. Try following :

            List<Inquire> sortedInquires = inquires.OrderBy(x => x.AsignedAgent.Id).ToList();

            var results = (from a in agents
                           join i in sortedInquires on a.Id equals i.Id
                           select new { agent = a, inquires = i
                           }
                           ).GroupBy(x => x.agent.Id)
                           .Select(x => x.First())
                           .ToList();

CodePudding user response：

check this

var assignationsPerAgent = (from agent in agents
                                        join inq in inquires on agent equals inq.AsignedAgent into agentsInInquires
                                        select new
                                        {
                                            o_agent = agent,
                                            casesAssignedTo = agentsInInquires.Count()
                                        }).GroupBy(x=>x.casesAssignedTo).OrderBy(x=>x.Key).FirstOrDefault();