Viewing Data from Multiple tables MVC5 using LINQ

Question

I have the following two tables

public class Book
{
    public int Id { get; set; }

    [Required]
    [StringLength(255)]
    public string BookTitle { get; set; }

    [Required]
    [StringLength(255)]
    public string Author { get; set; }

    [Required]
    [StringLength(400)]
    public string Description { get; set; }
}

and,

public class Rating
{
    public int Id { get; set; }

    [Required]
    public int Rate { get; set; }

    public Book Books { get; set; }

    [Required]
    public int BookId { get; set; }
}

one Book can have many Ratings. I need to write a Query so that i can view the BookTitle, Author, Description and the Average Rating for each book. I know I can use a View Model but I dont know how to structure the LINQ Query

and help would be appreciated

James Haug · Accepted Answer · 2017-02-19 23:02:44Z

1

One approach is to setup a navigation property on Book:

public class Book
{
    public ICollection<Rating> Ratings { get; set; }
}

Then, using Linq, you could use the reference property:

_context.Books.Select( c => new
{
    c.BookTitle,
    c.Author,
    c.Description,
    c.Ratings.Select( e => e.Rate ).Sum() / c.Ratings.Count()
});

If using a DbContext from Entity Framework, this will translate into an SQL query.

answered Feb 19, 2017 at 23:02

James Haug

1,50712 silver badges28 bronze badges

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

Comments

Ivan Gritsenko · Accepted Answer · 2017-02-19 23:08:18Z

1

Lets introduce a viewModel class first:

public class BookViewModel
{
    public string BookTitle { get; set; }
    public string Author { get; set; }
    public string Description { get; set; }
    public double AvgRating { get; set; }
}

We can execute the following LINQ then.

var bookViewModels = context.Books.GroupJoin(context.Ratings, x => x.Id, y => y.BookId, 
    (book, rates) => new BookViewModel
{
    Author = book.Author,
    BookTitle = book.BookTitle,
    Description = book.Description,
    AvgRating = rates.Average(r => r.Rate)
});

It may be easier for you to have Ratings navigation property in Book class.

answered Feb 19, 2017 at 23:08

Ivan Gritsenko

4,2262 gold badges23 silver badges35 bronze badges

1 Comment

tyrone reddy Over a year ago

Thanks very much! worked well. output is as expected.

Collectives™ on Stack Overflow

Viewing Data from Multiple tables MVC5 using LINQ

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related