CODEWITHSUNDEEP
asp.net-mvclinqmvvm
tyrone reddy
tyrone reddy

Reputation: 1

Viewing Data from Multiple tables MVC5 using LINQ

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

Upvotes: 0

Views: 37

Answers (2)

Ivan Gritsenko
Ivan Gritsenko

Reputation: 4236

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.

Upvotes: 1

James Haug
James Haug

Reputation: 1497

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.

Upvotes: 1

Related Questions