CODEWITHSUNDEEP
c#sql.netlinq
SliderUK
SliderUK

Reputation: 167

SQL Query to LINQ Query Syntax

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(),
                                       };

Upvotes: 0

Views: 145

Answers (2)

Pastor Cortes
Pastor Cortes

Reputation: 192

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

Upvotes: 1

Mehang Rai
Mehang Rai

Reputation: 92

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'

Upvotes: 0

Related Questions