Group By Multiple Column In LINQ in C#

asp.net-mvc c# entity-framework linq

Question

I have a class like as follows:

public class ActualClass
{
    public string BookName { get; set; }
    public string IssuerName { get; set; }
    public DateTime DateOfIssue { get; set; }
    public bool Status { get; set; }
}

It has following data in the table: enter image description here

I would like to group them by IssuerName and DateOfIssue for the following viewModel class:

public class ViewModel
{
   public string IssuerName { get; set; }
   public DateTime DateOfIssue { get; set; }
   public List<string> Books { get; set; }
}

And data will be displayed as follows: (Screenshot data will be replaced by the previous table data after successful grouping) enter image description here

Special attention: Is there anything wrong in my ViewModel according to my expectation?

I tried a lot after following some stackoverflow answers but none did work for me. Any help will be highly appreciated.

The code I have tried:

var viewmodel = from b in db.BookIssues
                group b by new
                {
                    b.IssuerName,
                    b.DateOfIssue
                }
                into g
                select new ViewModel()
                {
                    Name = g.key.IssuerName,
                    DateOfIssue = g.Key.DateOfIssue,
                    Books = g.ToList() //Actually this line of code is not working
                };
1
11
1/1/2019 10:37:53 AM

Accepted Answer

Books = g.ToList() //Actually this line of is not working

Probably because Books property is type of List<string>, not List<ActualClass>.

Can you please try this query, I added b.Select(bn => bn.BookName).ToList() to extract only names of books:

var books = new List<ActualClass>
{
   new ActualClass { BookName = "A", DateOfIssue = new DateTime(2015, 10, 10, 10, 10, 0), IssuerName = "1" },
   new ActualClass { BookName = "B", DateOfIssue = new DateTime(2015, 10, 10, 10, 10, 0), IssuerName = "1" },
   new ActualClass { BookName = "C", DateOfIssue = new DateTime(2015, 10, 10, 10, 10, 0), IssuerName = "1" },
   new ActualClass { BookName = "D", DateOfIssue = new DateTime(2015, 10, 10, 10, 10, 0), IssuerName = "2" },
   new ActualClass { BookName = "E", DateOfIssue = new DateTime(2015, 10, 10, 12, 10, 0), IssuerName = "2" },
   new ActualClass { BookName = "F", DateOfIssue = new DateTime(2015, 10, 10, 12, 10, 0), IssuerName = "2" }
};

var result = books.GroupBy(x => new { x.IssuerName, x.DateOfIssue })
                .Select(b => new ViewModel
                {
                    Books = b.Select(bn => bn.BookName).ToList(),
                    // Accessing to DateOfIssue and IssuerName from Key.
                    DateOfIssue = b.Key.DateOfIssue,
                    IssuerName = b.Key.IssuerName
                });

I grouped by: x.IssuerName, x.DateOfIssue. I did that by passing anonymous type in GroupBy() with following manner: x => new { x.IssuerName, x.DateOfIssue }.

Now they are in key and you can access to IssuerName and DateOfIssue from KEY in SELECT statement like in following: b.Key.IssuerName and b.Key.DateOfIssue.

13
11/23/2016 4:44:00 AM

Popular Answer

if you need to select list of books from group result, you need Books = v.Select(c=>c.BookName).ToList() also note that in case of you have time in issue date time you may need to group by only the date part using EntityFunctions.TruncateTime function. if you only storing date only then you can ignore this function.

var viewmodel  = db.BookIssues.GroupBy(x=>new {IssuerName =x.IssuerName, DateOfIssue=EntityFunctions.TruncateTime(x.DateOfIssue) })
.Select(v=>new ViewModel(){IssuerName =v.Key.IssuerName, DateOfIssue = v.Key.DateOfIssue, Books  = v.Select(c=>c.BookName).ToList() })
.ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow