How to populate List with data from db using LINQ?

Question

I need to add data from db to a List<Clients>. I fetched data from db and now I'm not sure how to properly do it, what's the good pattern/standard for this kind of operation. Below you can find my code. I need to populate List<Clients>. I don't think I follow good practice here, I guess I could do it easier but don't know how exactly. And I don't think I return list with data here, I think I don't populate it properly.

    public class Clients
    {
        public Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
    }

    public interface IClientsRepository
    {
        Task<List<Clients>> ListAllClients();
    }

    public class ClientsRepository : IClientsRepository
    {
        private List<Clients> _clientsList;


        public ClientsRepository(MainDbContext dbContext) : base(dbContext)
        {
        }

        public async Task<List<Clients>> ListAllClients()
        {
            _clientsList = new List<Clients>();

            var query = await _dbContext.ClientsTbl
                                .AsNoTracking()
                                .Join(_dbContext.AddressTbl.AsNoTracking(),
                                client => client.Id,
                                address => address.ClientId,
                                (client, address) => new
                                {
                                    client.Id,
                                    client.Name,
                                    address.FullAddress
                                })
                                .ToListAsync();

            var allClients = query
                            .Select(s => new Clients 
                            { 
                                Id = s.Id,
                                Name= s.Name,
                                Address = s.FullAddress
                            });
            
            return _clientsList;
        }
    }
}

Answer 1

Since your Address entity has the property address.ClientId,and your client model contains the property Address

The Address model and Client model has the one-to-one relationship

public partial class Client
    {
        public int Id { get; set; }
        public string? Name { get; set; }
        public Address Address { get; set; }
    }

    public  partial class Address
    {
        public int Id { get; set; }
        public string? FullAddress { get; set; }
        public int ClientId { get; set; }

        public Client Client { get; set; }
    }

you could check this document and select a solution to read the related data

for example:

var clients = await _context.Client.Include(x=>x.Address).ToListAsync();

now you could read read fulladdress through the navigation property:

clients[0].Address.FullAddress

Have you tried as below:

I added a property in client entity

public string? FullAddress { get; set; }

var clients = await _context.Client.AsNoTracking().Join(_context.Address.AsNoTracking(), x => x.Id, y => y.ClientId, (client, address) => new Client{ Id=client.Id, Name=client.Name, FullAddress=address.FullAddress }).ToListAsync(); 

Result: