Entity Framework - Nested Select Query on Selected Entry

Question

I am using Entity Framework Core 3.1.4. My Model is like-

public class Review
{
    [Key, Required]
    public Guid Id { get; set; }

    public int Rating { get; set; }
    public DateTime WatchDate { get; set; }

    public virtual Guid UserId { get; set; }
    public virtual User User { get; set; }
}

My controller is like this-

public async Task<IActionResult> Index()
{
    var data = await _context.Reviews
                        .Include(r=>r.User)
                        .Select(r => new Review {
                            Id = r.Id,
                            WatchDate = r.WatchDate,
                            User = r.User,
                            Rating = r.Rating
                        })
                        .OrderByDescending(r => r.Rating)
                        .ToListAsync();
    return View(data);
}

It is doing fine, but it is querying all data of the User table like this (red marked areas)-

But I need only email, so I like to select only email (yellow marked one) from there. I am doing Select in the upper level, but can't do the same thing in the inside element. As far as I know, there was a way in Entity Framework like this. But the code is not working as version changed.

Can anyone please help, how can I accomplish it?

How can I include only user email in the list, not all data? So how can I select nested entry?

you can do something like this. .Select(p => new { P.User.Email }) .AsEnumerable() — Vivek Nuna, Jun 05 '20 at 12:07

score 1 · Accepted Answer · answered Jun 05 '20 at 12:04

Try next:

var data = await _context.Reviews
                    .OrderByDescending(r => r.Rating)
                    .Select(r => new Review {
                        Id = r.Id,
                        WatchDate = r.WatchDate,
                        User = new User { Email = r.User.Email},
                        Rating = r.Rating
                    })                       
                    .ToListAsync();

But I would say better design would be to create specific DTO which will contain only needed properties and fill it in the Select clause.

Also .Include(r=>r.User) call is not needed cause you have Select clause.

AlleXyS · Answer 2 · 2020-06-05T12:11:09.470

You need to map your entity in a dto (data transfer object) with exactly same fields, less User, where you add as parameter only UserEmail

public async Task<IActionResult> Index()
{
    var data = await _context.Reviews
                        .Include(r=>r.User)
                        .Select(r => new Dtos.Review {
                            Id = r.Id,
                            WatchDate = r.WatchDate,
                            UserEmail = r.User.Email,
                            Rating = r.Rating
                        })
                        .OrderByDescending(r => r.Rating)
                        .ToListAsync();
    return View(data);
}

So, Reviews from Context have type Review Entity, but new Review from your query have type Dto (both have same parameters, less User)

Anyway, a best practice is to not send entities directly to clients and map it into a dto.

Entity:

public class Review {
  public int Id {get; set;}
  public DateTime WatchDate {get; set;}
  public int UserId {get; set;}
  public User User {get; set;}
  public decimal Rating {get; set;}
}

Dto:

public class Review {
  public int Id {get; set;}
  public DateTime WatchDate {get; set;}
  public string UserEmail {get; set;}
  public decimal Rating {get; set;}
}

You don't need to write `r.User != null ? r.User.Email : null,`. EF Core will do necessary things before generating the SQL. — TanvirArjel, Jun 05 '20 at 12:11
thanks. answer updated. For me is a good solution to prevent errors for null values. I'm too pessimistic :)) — AlleXyS, Jun 05 '20 at 12:14

score 1 · Answer 3 · answered Jun 05 '20 at 12:08

It's always better to use ViewModel for the view. So First, make a ViewModel class as follows:

public class ReviewViewModel
{
    public Guid Id { get; set; }

    public int Rating { get; set; }
    public DateTime WatchDate { get; set; }

    public Guid UserId { get; set; }
    public string UserEmail { get; set; }
}

Then project your query to the ViewModel type as follows:

public async Task<IActionResult> Index()
{
    var data = await _context.Reviews
                        .Select(r => new ReviewViewModel {
                            Id = r.Id,
                            WatchDate = r.WatchDate,
                            Rating = r.Rating,
                            UserId = r.User.Id,
                            UserEmail = r.User.Email,
                        })
                        .OrderByDescending(r => r.Rating)
                        .ToListAsync();
    return View(data);
}

score 0 · Answer 4 · answered Jun 05 '20 at 12:08

As a best practice, I would suggest to create a DTO, which only contains the necessary data and not the database objects:

public class UserReviewDTO
{
    public Guid Id { get; set; }
    public int Rating { get; set; }
    public DateTime WatchDate { get; set; }
    public string UserEmail { get; set; }
}

You can make your query like this:

var data = await _context.Reviews
    .Select(r => new UserReviewDTO {
        Id = r.Id,
        WatchDate = r.WatchDate,
        UserEmail = r.User.Email,
        Rating = r.Rating
    })
    .OrderByDescending(r => r.Rating)
    .ToListAsync();

Entity Framework - Nested Select Query on Selected Entry

4 Answers4