How to get only few properties from related data in EF core?

Question

This is ASP .net EF Core.

I have 2 classes. Nomination and User with one to many relationship.

Nomination will have foreign key pointing to User ID.

Classes:

public class Nomination
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int NominationId { get; set; }
    public int NomineeUserId { get; set; }
    public int NominationYear { get; set; }

    public User Nominee { get; set; }
}
public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string EmailId { get; set; }
    public byte[] PasswordHash { get; set; }
    public byte[] PasswordSalt { get; set; }
}
public class UserDto
{
    public int UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasKey(x => x.UserId);
        modelBuilder.Entity<User>().Property(x => x.UserId).ValueGeneratedOnAdd();

        modelBuilder.Entity<Nomination>().HasKey(x => x.NominationId);
        modelBuilder.Entity<Nomination>().Property(x => x.NominationId).ValueGeneratedOnAdd();
        modelBuilder.Entity<Nomination>().HasOne(x => x.Nominee).WithMany().HasForeignKey(x => x.NomineeUserId).OnDelete(DeleteBehavior.Restrict);
    }

Controller GETALL:

public async Task<ActionResult<IEnumerable<Nomination>>> GetNomination()
    {
        return await _context.Nomination
            .Include(n => n.NominationDetails)
            .Include(n => n.Nominee)
            .ToListAsync();
    }

When I do GETALL for Namination, I am receiving the complete object of "Nominee"(User) including the password stuff. I need UserDTO instead of User. I do not need other properties of User

How should I change my code to accomplish this?

Matt U · Answer 1 · 2019-11-26T23:44:31.303

2

If you're not using a mapping library such as AutoMapper, you can use LINQ to select and return a new type like this:

Let's say we create a NominationDto as well:

NominationDto.cs

public class NominationDto
{
    public int NominationId { get; set; }
    public int NomineeUserId { get; set; }
    public int NominationYear { get; set; }
    public UserDto Nominee { get; set; }
}

var nominations = _context.Nomination
    .Include(n => n.NominationDetails)
    .Include(n => n.Nominee)
    .Select(nomination => new NominationDto
    {
        NominationId = nomination.NominationId,
        NomineeUserId = nomination.NomineeUserId,
        NominationYear = nomination.NominationYear,
        Nominee = new UserDto
        {
            UserId = nomination.Nominee.UserId,
            FirstName = nomination.Nominee.FirstName,
            LastName = nomination.Nominee.LastName
        }
    }).ToListAsync();

However, if possible, I would recommend using AutoMapper or another mapping library. It will handle mapping the Nomination and User entity objects to the NominationDto and UserDto objects. Then you wouldn't need to do it by hand.

(I haven't tested the above code, but I believe that should put you in a good spot.)

EDIT I updated the LINQ section of my code above after the comment on my original answer.

edited Nov 26 '19 at 23:44

answered Nov 26 '19 at 16:00

Matt U

4,970
9
28

2

This will cause in memory projection after the query has been materialized (with all the related data) against the database. To minimize the DB impact (the query that will go to the DB) the select function should be added before `.ToListAsync()`. – Vedran Mandić Nov 26 '19 at 21:46
1

Oops! You are correct! Thank you for that. I had thought about that when writing it out, but for whatever reason I decided to put the Select after the ToListAsync(). – Matt U Nov 26 '19 at 23:42
Thank you for your answer! I will try it out as soon as possible and mark the answer correct. About the AutoMapper. I have created a mapper file and I can use CreateMap. How would I continue this approach in Controller? I shouldmap it before using _context? Apologies for the silly questions. New to .net. – Athreya M R Bhatt Nov 27 '19 at 09:42
1

@AthreyaMRBhatt you would inject and `IMapper mapper` and assign it to a local member e.g. `_mapper` in the Controller constructor, and then in the Action method you'd type sth like `_mapper.Map(sourceObject)`. Please read the instructions on how to set up AutoMapper: https://www.codementor.io/zedotech/how-to-using-automapper-on-asp-net-core-3-0-via-dependencyinjection-zq497lzsq – Vedran Mandić Nov 27 '19 at 13:19

Vedran Mandić · Accepted Answer · 2020-09-22T07:59:27.703

Write a select projection using LINQ's .Select() function before your .ToListAsync().

This will create a SQL query to fetch only those DB columns you have selected in the LINQ.

For e.g. NominationDto

public class NominationDto
{
    public int NominationId { get; set; }
    public int NomineeUserId { get; set; }
    public int NominationYear { get; set; }
    public UserDto Nominee { get; set; }
}

Your query could be:

var nominations = await _context.Nomination
        .Include(n => n.NominationDetails)
        .Include(n => n.Nominee)
        .Select(nomination => new NominationDto
    {
        NominationId = nomination.NominationId,
        NomineeUserId = nomination.NomineeUserId,
        NominationYear = nomination.NominationYear,
        Nominee = new UserDto
        {
            UserId = nomination.Nominee.UserId,
            FirstName = nomination.Nominee.FirstName,
            LastName = nomination.Nominee.LastName
        }
    }).ToListAsync();

Note that you might not need .Include(n => n.NominationDetails) as in this query nothing is fetched / selected / projected from the NominationDetails.

Additionally if you are not planning to track the changes of these entites then for better performance you can use after .Select() and before .ToListAsync() the method .AsNoTracking() to tell EF you are not going to track changes for the retrieved data in the EF's object graph.

You can find more about query tracking here: https://learn.microsoft.com/en-us/ef/core/querying/tracking

Thank you for your answer! I will try it out as soon as possible and upvote/mark the answer correct. — Athreya M R Bhatt, Nov 27 '19 at 09:42

How to get only few properties from related data in EF core?

2 Answers2