locked
Top 5 LINQ query with group by using entity framework mvc 5 RRS feed

  • Question

  • User-471420332 posted

    I need top 5 LINQ query with group by using entity framework and below is sql query like that i need LINQ query.

    select top 5 EventId,Datename(day, EventDate) as Day, CONVERT(varchar(3), EventDate, 100) as Month,  Datename(yy,EventDate) as year,Title,Description,ThumbImage  from EventGalleries group by EventId,Title,EventDate,Description,ThumbImage order by EventDate desc

    This is my table structure in sql

    enter image description here

    Below is my MVC Action Method and i need linq query to return list to my view.

     public ActionResult Gellery()
        {
            var list = db.EventGallerys.ToList();// i need linq query here in place of this list
            return View(list);
    
        }

    Below is my Model classs

    public class EventGallery
    {
        [Key]
        public int Sno {get;set;}
        public string ImageName { get; set; }
        public string ThumbImage { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public DateTime EventDate { get; set; }    
        public string EventId { get; set; }
        public string GalleryType { get; set; }  
        public string IP { get; set; }
        public string SubmittedBy { get; set; }
        public DateTime GalleryCreatedDate { get; set; }
        public string Status { get; set; }
       //  Sno,ImageName,ThumbImage,Title,Description,EventDate,GalleryCreatedDate,EventId,GalleryType,Status
        public EventGallery()
        {
        GalleryCreatedDate = DateTime.Now;
        Status = "Active";
    
        }

    Thank you in advance for your effort.

    Friday, August 3, 2018 3:58 PM

All replies

  • User1577371250 posted
    public ActionResult Gellery() 
    { 
    
    var query = (from p in db.EventGallery 
    group p by new { p.EventId, p.EventDate, p.Title, p.Description, p.ThumbImage } into r 
    select new {
    EventId = r.Key.EventId, Day = r.Key.EventDate.Day, Month = r.Key.EventDate.ToString("MMM"), Year = r.Key.EventDate.Year, Title = r.Key.Title, Description = r.Key.Description, ThumbImage = r.Key.ThumbImage }).OrderByDescending(c=> c.EventDate).ToList().Take(5); return View(query); }

    Friday, August 3, 2018 5:40 PM
  • User-471420332 posted

    I am getting below error

    LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

    and below is my Action method

    public ActionResult Gellery()
    {

    var list = (from p in db.EventGallerys
    group p by new { p.EventId, p.EventDate, p.Title, p.Description, p.ThumbImage } into r
    select new
    {
    EventId = r.Key.EventId,
    Day = r.Key.EventDate.Day,
    Month = r.Key.EventDate.ToString("MMM"),
    Year = r.Key.EventDate.Year,
    Title = r.Key.Title,
    Description = r.Key.Description,
    ThumbImage = r.Key.ThumbImage,
    EventDate = r.Key.EventDate
    }).OrderByDescending(c => c.EventDate).ToList().Take(5);


    return View(list);


    }

    Saturday, August 4, 2018 6:46 AM
  • User1724605321 posted

    Hi mazhar khan ,

    Seems your question is answered in SO threads . But you need to add ".AsEnumerable()" function , otherwise you will get the same error above . So the Linq will be :

     var list = db.EventGallerys.GroupBy(eg => new {
                    EventId = eg.EventId,
                    Title = eg.Title,
                    EventDate = eg.EventDate,
                    Description = eg.Description,
                    ThumbImage = eg.ThumbImage
                })
                                .OrderByDescending(eg => eg.Key.EventDate)
                                .Take(5).AsEnumerable()
                                .Select(eg => new
                                {
                                    EventId = eg.Key.EventId,
                                    Day = eg.Key.EventDate.ToString("D"),
                                    Month = eg.Key.EventDate.ToString("MMM"),
                                    Year = eg.Key.EventDate.ToString("yyyy"),
                                    Title = eg.Key.Title,
                                    Description = eg.Key.Description,
                                    ThumbImage = eg.Key.ThumbImage
                                }).ToList();

    Best Regards,

    Nan Yu

    Monday, August 6, 2018 3:07 AM
  • User1577371250 posted

    You can try this

    public ActionResult Gellery() 
    { 
    
    var query = (from p in db.EventGallery.ToList() 
    group p by new { p.EventId, p.EventDate, p.Title, p.Description, p.ThumbImage } into r 
    select new 
    { 
    EventId = r.Key.EventId,
     Day = r.Key.EventDate.Day,
     Month = r.Key.EventDate.ToString("MMM"),
     Year = r.Key.EventDate.Year,
     Title = r.Key.Title,
     Description = r.Key.Description,
     ThumbImage = r.Key.ThumbImage
    
     }).ToList().OrderByDescending(c=> c.EventDate).Take(5); 
    
    return View(query);
    
     }

    Tuesday, August 14, 2018 6:23 PM
  • User1204533129 posted

    try this 

    var query = (from t in Example
    group t by t.EventId
    orderby t.EventDate desc
    select new
    {
    EventId,
    Datename,
    Etcc
    };).Take(5);

    best regards,

    kamal.

    Thursday, October 4, 2018 7:42 PM