Filter with Left Join in Entity Framework

Question

I have User, Contact and ContactOfUser entities in an ASP.NET Core API project. I want to filter users based on input over these tables.

My entity classes are like this:

    public class User
    {
        public int Id { get; set; }

        [MaxLength(50)]
        public string Name { get; set; }

        [MaxLength(50)]
        public string Surname { get; set; }

        [MaxLength(60)]
        public string Username { get; set; }
}

public class Contact
{
    public int Id { get; set; }

    [MaxLength(50)]
    public string Value{ get; set; }
}

public class ContactOfUser
{
    public int Id { get; set; }

    public int UserId { get; set; }
    [ForeignKey(nameof(UserId))]
    public User User { get; set; }

    public int ContactId { get; set; }
}

I want to get filtered users based on this FilterModel object:

public class FilterModel
{
     public string Name { get; set; }
     public string Surname { get; set; }
     public string Username { get; set; }
     public List<int> ContactId { get; set; }
}

How can I make this filtering process in Entity Framework with Linq methods considering that don't apply special filter data when that data will be accepted as null?

I did something like that method but it is not properly working:

List<User> GetFilteredUsers(FilterModel filter)
{
    var query1 = dbContext.Users
                          .Where(u => u.Name.Contains(filter.Name ?? string.Empty) &&
                                      u.Surname.Contains(filter.Surname ?? string.Empty) &&
                                      u.Username.Contains(filter.Username ?? string.Empty));

    var query2 = from u in query1
                 join cu in dbContext.ContactOfUsers on u.Id equals cu.UserId
                 into res
                 from item in res.DefaultIfEmpty()
                 where filter.Contacts.Contains(item.ContactId)
                 select new InitialUserModel
                         {
                             Id = u.Id,
                             Name = u.Name,
                             Surname = u.Surname,
                             Username = u.Username
                         };
}

Answer 1

You can achieve it in this way

• Using GroupBy to get userId and ContactIds accordingly.

var userContactIds = _dbContext.ContactOfUser.GroupBy(p => p.UserId).Select(g => new { UserId = g.UserId,  ContactIds = g.Select(p => p.ContactId).ToList() });

• Get the result :

var result = _dbContext.User.Select(p => new FilterModel 
{ 
   Name = p.Name, Surname = p.Surname, Username = p.Username, 
   ContactId = userContactIds.Where(c => p.Id == c.UserId).ToList() 
});

Updated

List<User> GetFilteredUsers(FilterModel filter)
{
     return (from u in _dbContext.User
            join c in _dbContext.ContactOfUser on u.Id equals c.UserId
            join fContactId in filter.ContactId on c.ContactId equals fContactId 
            where u.Name == filter.Name && u.Surname == filter.Surname && u.Username == filter.Username 
            select u).ToList();
}