c# - I can not get a message from the database without loading all message

Question

I first loaded all the messages from the database. Then I chose what I need.

But it's not correctly loading all messages.

var ldetails = _context.RegistrationUsers.Include(i => i.ListFriends).ThenInclude(z=>z.MessagesDetails).SingleOrDefault(c => c.UserName == Context.User.Identity.Name);

I wanted to load only one message by criterion but I constantly get an error. Please show me a ways to do it.

I have tried:

var ldetails = _context.RegistrationUsers.Include(i => i.ListFriends).ThenInclude(z=>z.MessagesDetails.SingleOrDefault(g=>g.TextMessage == messagesDetail.TextMessage)).SingleOrDefault(c => c.UserName == Context.User.Identity.Name);

But I get an error. My code looks like:

public class RegistrationUser
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int PMId { get; set; }
        [Required]
        [Column(TypeName ="varchar(16)")]
        public string UserName { get; set; }
        [Required]
        [Column(TypeName = "varchar(16)")]
        public string Password { get; set; }
        [Column(TypeName = "varchar(480)")]
        public string Token { get; set; } 




        public ICollection<ListFriend> ListFriends { get; set; }


        public RegistrationUser()
        {
            ListFriends = new List<ListFriend>();

        }
    }


public class ListFriend
{
    [Key,Column(Order = 0)]

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }    
    [Required]
    [Column(TypeName ="varchar(16)")]
    public string UserFriendName { get; set; }



    public ICollection<MessagesDetail> MessagesDetails { get; set; 
    }

    public ListFriend()
    {

        MessagesDetails = new List<MessagesDetail>();
    }


}


public class MessagesDetail
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

    [Key,Column(Order = 0)]
    public int PMIdMes     { get; set; }
    [Required]
    [Column(TypeName ="nvarchar(240)")]
    public string  TextMessage{ get; set; }
    [Required]
    [Column(TypeName = "varchar(16)")]
    public string SenderUser { get; set; }
    [Required]
    [Column(TypeName = "varchar(16)")]
    public string CatcherUser { get; set; } 
}


//My method

[Authorize]
public async Task DeleteMessage(string frend ,MessagesDetail 
messagesDetail)
        {

            var ldetails = _context.RegistrationUsers.Include(i => 
            i.ListFriends).ThenInclude(z=>z.MessagesDetails.
 SingleOrDefault(g=>g.TextMessage == messagesDetail.TextMessage)).
 SingleOrDefault(c => c.UserName == Context.User.Identity.Name);


//.....
         }


I expect to receive a message for the condition:

g=>g.TextMessage == messagesDetail.TextMessage;

Answer 1

You are probably trying to filter in Include or IncludeThen which is not supported. You would have to create a projection by using Select:

var ldetails = _context.RegistrationUsers.Select(x=>new RegistrationUser
{
  PMId=x.PMId,
  UserName=x.UserName,
  Password=x.Password,
  Token=x.Token,
  ListFriends=x.ListFriends.Select(q => new ListFriend
    {
      UserId=q.UserId,
      UserFriendName=q.UserFriendName,
      MessagesDetails=q.Where(a => a.TextMessage == messagesDetail.TextMessage).ToList()
    }).ToList()              
}).SingleOrDefault(c => c.UserName == Context.User.Identity.Name);

Take a look at this issue for more information.