Home / Questions / 514586
Accepted
Wilmilcard
Asked: 2022-07-13 06:42:58 +0800 CST 2022-07-13 06:42:58 +0800 CST

How to add custom query (COUNT) from SQL to linq C#?

  • 772

I have a doubt with adding a custom field that is done in SQL but I don't know in linq.

In itself it is a select where I filter a user and group by Keyword... but I need to bring the keyword that is repeated the most, hence the count.

SELECT 
     UserID, 
     KeywordID, 
     COUNT(KeywordID) as count 
FROM 
     LogKeywords lk 
WHERE 
     UserID = 83 
GROUP BY 
      KeywordID ;

That query brings me this:

enter image description here

I already have that in C#, but I don't know how to nail the count inside .Select().

My code in C# is like this:

var keyword = _context.LogKeywords
                    .Where(x => x.UserID == employee.UserID)
                    .Select(x => new
                    {
                        x.UserID,
                        x.Keyword.Name,
                        count = x.KeywordID.Count() //<--- aqui me aparece un error
                    })
                    .GroupBy(x => x.KeywordID);

Any ideas?

c#

1 Answers

  1. Best Answer

    I found a solution doing it like this:

    var lista = from l in _context.LogKeywords.Where(x => x.UserID == employee.EmployeeID).ToList()
                        group l by l.KeywordID into grupo
                        select new
                        {
                            keywordId = grupo.Key,
                            Count = grupo.Count()
                        };
    • 0