Group By Multiple Column In LINQ in C#

Question

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:

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)

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
                };

TanvirArjel

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

Damith

View more on Stack Overflow

kat1330 · Accepted Answer · 11/23/2016 4:44:00 AM

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.

Group By Multiple Column In LINQ in C#

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related