MVC 4 Entity Linq Groupby Custom Group Model Error?

I found this cool article about displaying a View with Grouped table data here: http://ole.michelsen.dk/blog/grouping-data-with-linq-and-mvc/

I have a slightly different data source than the simple hard-coded example above. I have two Entity tables (Category, CategorySub) in which I need the same View display as the example above achieves.

I'm unsure on the 'Group' model for my solution. I have created a new model called 'CategoryViewModel, which combines both Category and CategorySub models.

I receive the following pre-run error in the Linq (Values = g })
ERROR: Error An explicit conversion exists (are you missing a cast?)

[Models]
    public class CategoryViewModel
    {
        public Category category { get; set; }
        public CategorySub categorySub { get; set; }
    }

    public partial class Category
    {
        public Category()
        {
            this.CategorySubs = new HashSet<CategorySub>();
        }
   
        public System.Guid CategoryID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
   
        public virtual ICollection<CategorySub> CategorySubs { get; set; }
    }

    public partial class CategorySub
    {
        public System.Guid CategorySubID { get; set; }
        public System.Guid CategoryID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
   
        public virtual Category Category { get; set; }
    }

[Controller]
    public class ExpenseController : Controller
    {
        BudgetProjectEntities context = new BudgetProjectEntities();

        public ActionResult Index()
        {
            var categories = (from c in context.Catagories
                              join s in context.CategorySubs on c.CategoryID equals s.CategoryID
                              group c by c.CategoryID into g
                              select new Group<Guid, CategoryViewModel> { Key = g.Key, Values = g }); // ERROR: Error An explicit conversion exists (are you missing a cast?)
 
            return View(categories.ToList());
        }
    }

[View]
@using BudgetProjectUI.Models
@model List<Group<string, BudgetProjectDB.CategoryViewModel>>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<table>
<thead><tr><th>Author</th><th>Title</th><th>Price</th></tr></thead>
<tbody>
@foreach (var group in Model)
{
    <tr><th colspan="3">@group.Key</th></tr>
    foreach (var catagory in group.Values)
    {
        <tr><td>@category.category.Name</td><td>@category.categorySub.Name</td><td>@category.categorySub.Description</td></tr>
    }
}
</tbody>
</table>
WorknHardrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
You have two joined entities--context.Catagories and context.CategorySubs--and CategoryViewModel would need to be defined in one of those types.

Are you trying to create a new CategoryViewModel from the Categories and CategorySubs?

Also CategoryViewModel doesn't fit the declaration for the Group class.
0
WorknHardrAuthor Commented:
Okay I'm learning as we go and need to backup a bit regarding the example link I gave above.
More in a minute...

Okay, I've recreated the error, this time with the original example link. Now we're working with real-world relational data we can both see.

The example works perfectly with the data in one List. I have taken the one List and made two Lists and changed the LINQ accordingly. Here are the results...
    1. The LINQ errors on the "Values = g"
    2. Error: Cannot implicitly convert type 'System.Linq.IGrouping<string,LinqGrouping.Models.Book>' to 'System.Collections.Generic.IEnumerable<LinqGrouping.Models.MyBooks>'. An explicit conversion exists (are you missing a cast?)

[Models]
namespace LinqGrouping.Models
{
    public class Group<K, T>
    {
        public K Key;
        public IEnumerable<T> Values;
    }

    public class Book
    {      
        public string Genre;
    }

    public class Authors
    {
        public string Title;
        public string Author;
        public string Genre;
        public decimal Price;
    }

    public class MyBooks
    {        
        public Book book { get; set; }
        public Authors authors { get; set; }
    }
}

[Action/Controller]
namespace LinqGrouping.Controllers
{
    public class GroupingController : Controller
    {
        public ActionResult Index()
        {            
            var books = new List<Book>();
            books.Add(new Book { Genre = "Fiction" });
            books.Add(new Book { Genre = "Biography" });
            books.Add(new Book { Genre = "Photography" });

            var authors = new List<Authors>();
            authors.Add(new Authors { Author = "Douglas Adams", Title = "The Hitchhiker's Guide to the Galaxy", Genre = "Fiction", Price = 159.95M });
            authors.Add(new Authors { Author = "Scott Adams", Title = "The Dilbert Principle", Genre = "Fiction", Price = 23.95M });
            authors.Add(new Authors { Author = "Douglas Coupland", Title = "Generation X", Genre = "Fiction", Price = 300.00M });
            authors.Add(new Authors { Author = "Walter Isaacson", Title = "Steve Jobs", Genre = "Biography", Price = 219.25M });
            authors.Add(new Authors { Author = "Michael Freeman", Title = "The Photographer's Eye", Genre = "Photography", Price = 195.50M });

            var booksGrouped = from b in books
                               join a in authors on b.Genre equals a.Genre
                               group b by b.Genre into g
                               select new Group<string, MyBooks> { Key = g.Key, Values = g };

            return View(booksGrouped.ToList());
        }
    }
}

[View]
@using LinqGrouping.Models
@model List<Group<string, Book>>

@{
    ViewBag.Title = "LINQ Grouping";
}

<h2>Grouping books by Genre</h2>

<table>
<thead><tr><th>Author</th><th>Title</th><th>Price</th></tr></thead>
<tbody>
@foreach (var group in Model)
{
    <tr><th colspan="3">@group.Key</th></tr>
    foreach (var book in group.Values)
    {
        <tr><td>@book.Author</td><td>@book.Title</td><td>@book.Price.ToString("c")</td></tr>
    }
}
</tbody>
</table>
0
Bob LearnedCommented:
With that example, here is what I changed to get it to compile:

Add a constructor

 public class MyBooks
    {
        public MyBooks(Authors authors1, Book book1)
        {
            authors = authors1;
            book = book1;
        }

        public Book book { get; set; }
        public Authors authors { get; set; }
    }

Open in new window


Add a let statement to create an instance of MyBooks, and group by the joined MyBooks, instead of "b":

 var booksGrouped = from b in books
                               join a in authors on b.Genre equals a.Genre
                               let j = new MyBooks(a, b)
                               group j by j.book.Genre into g
                               select new Group<string, MyBooks> { Key = g.Key, Values = g };
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

WorknHardrAuthor Commented:
Excellent!

I must confess, the LINQ 'Let' clause is new to me. I'll be studying more examples and run your code in LINQpad to view it's SQL equivalence..

Also change the View to this:

@using LinqGrouping.Models
@model List<Group<string, MyBooks>

@foreach (var group in Model)
{
    <tr><th colspan="3">@group.Key</th></tr>
    foreach (var book in group.Values)
    {
        <tr><td>@book.authors.Author</td><td>@book.authors.Title</td><td>@book.authors.Price.ToString("c")</td></tr>
    }
}
0
WorknHardrAuthor Commented:
I'm assuming that I could Join any number of Lists in this manner?
0
Bob LearnedCommented:
If you need more lists, then it would be easier to use the object initializer form, and not the constructor form, since it would require a new argument every time you want to add one.

Here is an object initializer example:

 let j = new MyBooks { author = a, book = b}

The "let" keyword gives you intermediate results, which is required to combine author and book into a single entity, which can be grouped in the subsequent operation.
0
WorknHardrAuthor Commented:
Thx again...

Now coding like this, as suggested:

[LINQ]
    let j = new MyBooks { book = b, authors = a, prices = p }

 [Model]
    public class MyBooks
    {
        public Book book { get; set; }
        public Authors authors { get; set; }
        public Prices prices { get; set; }
    }
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.