Implement an SQL query in LINQ-CodePudding

I'm trying implement the follow query in LINQ, but I don't find solution:

SQL:

SELECT COUNT(*) AS AmountMonths
FROM (SELECT SUBSTRING(CONVERT(NVARCHAR(12), pay_date, 112), 1, 6) AS Month
      FROM #tmp 
      GROUP BY SUBSTRING(CONVERT(NVARCHAR(12), pay_date, 112), 1, 6)) AS AmountMonths

What I need is get the amounts of months in which the clients made payments, with the condition that there may be months in which no payments have been made.

In C# I tried the following:

int amountMonths = payDetail.GroupBy(x => Convert.ToDateTime(x.PayDate)).Count();

and

int amountMonths = payDetail.GroupBy(x => Convert.ToDateTime(x.PayDate).Month).Count();

But I am not getting the expected result.

CodePudding user response：

(Assuming you're using EF Core)

You're almost there. You could do:

var amountMonths = context.AmountMonths.GroupBy(c => new { c.PayDate.Year, c.PayDate.Month }).Count();

This will translate to something like:

SELECT COUNT(*)
  FROM (
      SELECT DATEPART(year, [a].[PayDate]) AS [a]
      FROM [AmountMonths] AS [a]
      GROUP BY DATEPART(year, [a].[PayDate]), DATEPART(month, [a].[Pay_Date])
  ) AS [t]

which I'd find preferable over creating a string and chopping it up. EOMONTH isn't a standard mapped function, alas, otherwise it can be used to convert a date to month level granularity