SQL Query to LINQ for MVC Controller

Question

I am trying to convert he following SQL statement to LINQ to use in an MVC Controller.

SELECT COUNT(*) AS LoginCount, InsertDate, CertificateNumber
FROM dbo.LogIns
GROUP BY InsertDate, CertificateNumber

I have made several attempts with no luck.

Could you show us an attempt where you got furthest in your opinion? Otherwise this question seems to be, can you do it for me. — Daniel Casserly
– Daniel Casserly, Commented Mar 24, 2015 at 11:44
I have been trying to resolve this since yesterday. My brain is fried and my attempts are no longer in ctrl z or ctrl y. Next time I shall save all my attempts as there seems to be people that would say do it for me here but I am not one of those. — Doug Farrell
– Doug Farrell, Commented Mar 24, 2015 at 12:15

Rahul Singh · Accepted Answer · 2015-03-24 11:57:15Z

1

Try this:-

var result = db.LogIns.GroupBy(x => new { x.InsertDate, x.CertificateNumber })
                      .Select(x => new 
                             { 
                                   InsertDate = x.Key.InsertDate,
                                   CertificateNumber = x.Key.CertificateNumber,
                                   LoginCount = x.Count() 
                              });

Or with query syntax:-

var result = from x in db.LogIns
             group x by new { x.InsertDate, x.CertificateNumber } into g
             select new 
             {
                 InsertDate = g.Key.InsertDate,
                 CertificateNumber = g.Key.CertificateNumber,
                 LoginCount = g.Count() 
             };

edited Mar 24, 2015 at 11:57

answered Mar 24, 2015 at 11:46

Rahul Singh

21.8k6 gold badges43 silver badges61 bronze badges

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

7 Comments

Doug Farrell Over a year ago

Thanks Getting 'object' does not contain a definition for 'InsertDate' from the view. I am passing the results via a ViewBag.

Rahul Singh Over a year ago

Why? You don't have InsertDate property in LogIns type?

Doug Farrell Over a year ago

Thought I Did 'public partial class LogIn { public int ID { get; set; } public Nullable<System.DateTime> InsertDate { get; set; } public string IP { get; set; } public string CertificateNumber { get; set; } }'

Rahul Singh Over a year ago

Okay, Just read your comment properly, since you are passing it via ViewBag, you can't access it directly. You need to type-cast it like this:- IEnumerable<LogIn> LogIns = (IEnumerable<LogIn)ViewBag.LogIn;

Doug Farrell Over a year ago

I appreciate your help and excuse my ignorance but am getting lost. I am attempting to return the results of the query to a list in the view. Where does the line above go, I tried it in the controller and the view.

|

Collectives™ on Stack Overflow

SQL Query to LINQ for MVC Controller

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related