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
– Vivek Nuna, Commented Jun 5, 2020 at 12:07

Guru Stron · Accepted Answer · 2020-06-05 12:04:58Z

1

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.

answered Jun 5, 2020 at 12:04

Guru Stron

151k11 gold badges186 silver badges232 bronze badges

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

Comments

TanvirArjel · Accepted Answer · 2020-06-05 12:08:14Z

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

AlleXyS · Accepted Answer · 2020-06-05 12:11:09Z

1

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

edited Jun 5, 2020 at 12:11

answered Jun 5, 2020 at 12:07

AlleXyS

2,5983 gold badges20 silver badges44 bronze badges

2 Comments

TanvirArjel Over a year ago

You don't need to write r.User != null ? r.User.Email : null,. EF Core will do necessary things before generating the SQL.

AlleXyS Over a year ago

thanks. answer updated. For me is a good solution to prevent errors for null values. I'm too pessimistic :))

Annosz · Accepted Answer · 2020-06-05 12:08:10Z

0

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

answered Jun 5, 2020 at 12:08

Annosz

1,0329 silver badges26 bronze badges

Collectives™ on Stack Overflow

Entity Framework - Nested Select Query on Selected Entry

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related