Query that searches quantity of items from a column in a table with the same name

Question

Hello, I’m trying to find the amount of records with the same name, from a certain column in my Lead table in the database in a period (month). Follow my class, I want to search only from the field "Origin"

    public class Lead
{

    internal Lead(INotificationHandler notificationHandler)
    {
        _notificationHandler = notificationHandler;
    }
    public string Name { get; set; }
    public string Telephone { get; set; }
    public string Email { get; set; }
    public TreatmentOfInterest TreatmentOfInterest { get; set; }
    public string Origin { get; set; }
    public Guid PatientId { get; set; }
    public Guid CalendarId { get; set; }
    public virtual Calendar Calendar { get; set; }
}

My DTO for return:

public class LeadOriginDto 
{
    public LeadOriginDto()
    {
        Origins = new List<string>();
    }
    public List<string> Origins { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public Guid? CalendarId { get; set; }
}

My repository interface left these two methods to know which I would have better performance:

    public interface ILeadRepository : IRepository<Lead>
{
    Task<int> LeadsCountForOrigin(DateTime date);

    IQueryable<Lead> LeadsCountForOrigin(LeadOriginDto origin);
}

And my repository class where I want to create the query:

internal class LeadRepository : Repository<Lead>, ILeadRepository
{

    private readonly DbSet<Lead> _leads;

    public LeadRepository(IDbContext context)
        : base(context)
    {
        _leads = context.Set<Lead>();
    }

    public Task<int> LeadsCountForOrigin(DateTime date)
    {
        throw new NotImplementedException();
    }

    public async IQueryable<Lead> LeadsCountForOrigin(LeadOriginDto origin)
    {
        var date = new DateTime();

        var firstDayOfMonth = new DateTime(date.Year, date.Month, 1);
        var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);

        IQueryable<Lead> query = _leads
            .Include(search => search.Patient)
            .Include(search => search.Calendar);

        //var nameOrigins = (from o in _leads 
        //                   group o by new { o.Origin} into grp
        //                   select new Lead { Origin = grp.Sum(s => s.Origin)}).ToListAsync();

        //var nameOrigins = from o in _leads.Cast<string>()
        //                  group o by o into g
        //                  select new { Lead = g.Key, Count = g.Count() };
        var nameOrigins = await _leads.CountAsync(lead => lead.Created >= firstDayOfMonth && lead.Created <= lastDayOfMonth && lead.Origin)
            .Where(g => g.Count() > 1)
            .Select(g => g.Key);

        //var originsCount = 
        //(nameOrigins.Count() > 0)
        return nameOrigins;
    }
}

The excerpts that are commented was the way I tried to do and I could not actually perform the query that searches the number of names repeated in the Origin column in a certain period of each month.

Example:

In the month of Janeiro was registered

10 - Joao 
5 - Maria
2 - Jose

Fevereiro

20 - Maria
15 - Jose
3 - Joao

Answer 1

I made this select in the database to search the records of the LEAD table in the column Origin and below I will leave my query, which gives me a return with status 200 but without the column data I want.