.Net 5 Entity Framework removing unneccessary calls from Query involving FK 1:M relationship-CodePudding

I have the following 2 DTOs that are used to return the information in the API in order to filter out fields that I don't want the client to be able to view

public record CommentDto
{
    public int? CommentId { get; set; }
    public string AuthorUid { get; set; }
    public string Text { get; set; }
    public int? ParentCommentId { get; set; }
    public string CommentPn { get; set; }
    public virtual UserDto User { get; set; }
}

public record UserDto
{
    public string Uid { get; set; }
    public string Username { get; set; }
}

I'm querying the postgres database using the following:

            var comments = await dbSet.
            Where(c => c.commentPn == sentPn)
           .Select(c => new CommentDto
            {
                CommentId = c.CommentId,
                CommentPn = c.CommentPn,
                AuthorUid = c.AuthorUid,
                Text = c.Text,
                ParentCommentId = c.ParentCommentId,
                User = new UserDto
                {
                    Username = dbSet.Select(u => u.User.Username).Single(),
                    Uid = dbSet.Select(u => u.User.Uid).Single()
                },
            }).ToListAsync();

While this works and the correct data is returned, I notice what I believe are unnecessary call(s) being included in the query.

  SELECT d1.comment_id AS "CommentId", d1.comment_pn AS "CommentNiin", d1.author_uid AS "AuthorUid", d1.comment_text AS "Text", d1.parent_comment_id AS "ParentCommentId", (
      SELECT u.username
      FROM database_item_comments AS d
      INNER JOIN users AS u ON d.author_uid = u.uid
      LIMIT 1) AS "Username", (
      SELECT u0.uid
      FROM database_item_comments AS d0
      INNER JOIN users AS u0 ON d0.author_uid = u0.uid
      LIMIT 1) AS "Uid"
  FROM database_item_comments AS d1

I know it's due to the way I'm retrieving the user values being incredibly inefficient, what would be the correct way to complete this query while only making a single call to the users table?

Preferably just querying the comment table directly, and returning the full Comment entity sans DTO, without having to map the variables, while creating the UserDto for the Comment and mapping the values

CodePudding user response：

Your Comment entity should have a reference to a User entity, which would see your query corrected to:

        var comments = await dbSet.
        Where(c => c.commentPn == sentPn)
       .Select(c => new CommentDto
        {
            CommentId = c.CommentId,
            CommentPn = c.CommentPn,
            AuthorUid = c.AuthorUid,
            Text = c.Text,
            ParentCommentId = c.ParentCommentId,
            User = new UserDto 
            {
                UId = c.User.UId,
                Username = c.User.Username
            });
        }).ToListAsync();

Your example would potentially fail if you have multiple comments in the DB as to populate the user DTO you were effectively telling it to load all comments (DbSet.Select) to get at the User and expect only 1 result via Single(). Then there is the fact that you are executing that twice, once to select the ID, and then to select the name.