epv epv - 5 months ago 22
SQL Question

Linq to SQL Group by Controller

I am trying to learn how to write a sql query to return a count of records then group it by an ID and return it to a list. I have tried a few things and keep getting cannot implicitly convert type System.Collections.Generic.List<>' to 'System.Linq.IQueryable..etc

What I have tried so far in my repository:

public IQueryable<CHECKLIST> GetAllComplaintsCount()
{
try
{
return _context.Checklists
.GroupBy(a => a.MonitorEnteredEmpID)
.Select(a => new { Amount = a.Sum(b =>b.MonitorEnteredEmpID), Name = a.Key })
.ToList();
}
catch (Exception ex)
{
_logger.LogError("Could not get am with checklist", ex);
return null;
}
}


If I change it to a IEnumerable I get this error:

Cannot implicitly convert type 'System.Collections.Generic.List<<anonymous type: int Amount, int Name>>' to 'System.Collections.Generic.IEnumerable


Question

Can someone please advise me on what I am doing wrong and how I can return a count of all the checklists by EntereredEMPID?

Answer

The return type of your method GetAllComplaintsCount() is IQueryable<CHECKLIST>. But in your query, you create an anonymous type at

.Select(a => new { Amount = a.Sum(b =>b.MonitorEnteredEmpID), Name = a.Key })

and you try to return a List<T> of this anonymous type. So this is cannot be casted into a IQueryable<CHECKLIST>.

So I guess you have a class (or struct) called CHECKLIST that has properties called Amount and Name (as you use them in your query). Now, instead of creating instances of an anonymous type, create instances of that CHECKLIST:

 return _context.Checklists
            .GroupBy(a => a.MonitorEnteredEmpID)
            .Select(a => new CHECKLIST { Amount = a.Sum(b =>b.MonitorEnteredEmpID), Name = a.Key });

and ommit the .ToList() as you want to return an IQueryable and not a finished List.

Of course, if you don't want to execute the query later but return a enumerated List, you need to change the signature of your method to List<CHECKLIST> and use .ToList() like this:

public List<CHECKLIST> GetAllComplaintsCount()
{
    try
    {
        return _context.Checklists
            .GroupBy(a => a.MonitorEnteredEmpID)
            .Select(a => new CHECKLIST { Amount = a.Sum(b =>b.MonitorEnteredEmpID), Name = a.Key })
            .ToList();
    }
    catch (Exception ex)
    {
        _logger.LogError("Could not get am with checklist", ex);
        return null;
    }
}

UPDATE:

As you (probably) actually whish to know the count of elements with a MonitorEnteredEmpID, you may consider a totally different return type. How about a Dictionary<int, int> that maps your MonitorEnteredEmpID to the element count:

public Dictionary<int, int> GetAllComplaintsCount()
{
    try
    {
        return _context.Checklists
            .GroupBy(a => a.MonitorEnteredEmpID)
            .ToDictionary(g => g.Key, g => g.Count);
    }
    catch (Exception ex)
    {
        _logger.LogError("Could not get am with checklist", ex);
        return null;
    }
}

So you can use this like that:

Dictionary<int, int> result = GetAllComplaintsCount();
Console.WriteLine("ID        COUNT");
foreach(int id in result.Keys)
    Console.WriteLine($"{id}        {result[id]}");
Comments