Implement an SQL query in LINQ

Question

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.

Taking a query that works well in SQL and rewriting it for linq is a step backwards. EF will let you send raw SQL to the server. If you have something even remotely complicated and you already have the SQL, use that ability. — Joel Coehoorn
– Joel Coehoorn, Commented May 13, 2022 at 19:41
please share some data example, because your second code line is correct. maybe its a string to datetime conversion issue on some rows — Power Mouse
– Power Mouse, Commented May 13, 2022 at 19:48
If you don't need the grouping data you can do a DistinctBy instead of a GroupBy. — GuyVdN
– GuyVdN, Commented May 13, 2022 at 19:50
Call EOMONTH(pay_date) rather than performing a string conversion just to quantize dates to a month level granualrity — Caius Jard
– Caius Jard, Commented May 13, 2022 at 21:00
Convert.ToDateTime(x.PayDate) - pay_date / x.PayDate already appears to be a date — Caius Jard
– Caius Jard, Commented May 13, 2022 at 21:01

Caius Jard · Accepted Answer · 2022-05-13 21:21:15Z

1

(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

edited May 13, 2022 at 21:21

answered May 13, 2022 at 21:15

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Agu Fortini Over a year ago

works, just what I needed! Thank you so much

Collectives™ on Stack Overflow

Implement an SQL query in LINQ

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related