SQL Query to LINQ Query Syntax

Question

I've got the following SQL Query which provides me with Month, Year and Count. How can I do the equivalent in LINQ Query Syntax?

SQL

SELECT 
    MONTH(ExpiryDate) MONTH, 
    YEAR(ExpiryDate) YEAR, 
    COUNT(*) COUNT 
FROM tblItems 
GROUP BY 
    YEAR(ExpiryDate), 
    MONTH(ExpiryDate)

Thanks in advance

[UPDATE WORKING ANSWER]

var query = from a in db.tblItems
                                       group a by new
                                       {
                                           ((DateTime)a.ExpiryDate).Year,
                                           ((DateTime)a.ExpiryDate).Month
                                       } into bca
                                       select new
                                       {
                                           MonthField = bca.Key.Month,
                                           YearField = bca.Key.Year,
                                           CountField = bca.Count(),
                                       };

LINQ is too broad. Please provide more context - LINQ to SQL, LINQ to Entities (EF, EF Core, which version) etc. — Ivan Stoev
– Ivan Stoev, Commented Jun 20, 2017 at 16:34

Pastor Cortes · Accepted Answer · 2017-06-20 16:34:31Z

1

Please try this:

var info =  from a in tblItems
            group a by new
            {
                a.ExpiryDate.Year,
                a.ExpiryDate.Month
            } into bca
            select new ConsolidatedData()
            {
                MonthField = bca.Key.ExpiryDate.Month,
                YearField = bca.Key.ExpiryDate.Year,
                CountField = bca.Count(),
            };

You can also try LINQPad to develop your skills in LINQ, You already have the SQL logic. Have a nice day

answered Jun 20, 2017 at 16:34

Pastor Cortes

1925 silver badges13 bronze badges

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

1 Comment

SliderUK Over a year ago

Thanks for this I've got it working with some minor changes. I've updated the question with my answer. Also thanks for the link to LINQPad didn't know that existed.

Mehang Rai · Accepted Answer · 2017-06-20 16:32:33Z

0

I did not exactly understand what you were asking for but I would use grouping in linq like this.

'from item in tblItems 
group item by year,month into group select group.firstordefault.month,group.firstordefault.year,group.count'

answered Jun 20, 2017 at 16:32

Mehang Rai

924 bronze badges

Collectives™ on Stack Overflow

SQL Query to LINQ Query Syntax

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related