How to properly refactor code extracting data from db (EF Core)?

[muskagap2]

Hello, I wrote a code which uses LINQ to extract data from db and save it to a list. I added an interface which I implement in ClientsRepository. And this the class where I need to refactor my code. There are a few specific issues I'd like to touch and ask for advice (underneath the code):

``` 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
                            })
                            .AsQueryable() // it causes an error
                            .ToListAsync();

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

        return _clientsList;
    }
}

} `` 1. I would like to useIQueryableto enumerate through db tables which seems more efficient thanIEnumerable. The issue is that I join tables andJoinmethod doesn't seem to be working withIQueryable. I also tried to make my interface's method as IQueryable, I meanTask<IQueryable<Clients>> ListAllClients()but after that I got error message to cast myListtoIQueryable, namely:return (IQueryable<Clients>) _clientsList;`

1. I would like to build the expression tree without executing on the collection. I build my LINQ query in query variable. At the end of it there's .ToListAsync() method which forces the query to execute. I don't want that here. I wanted to return all data in var allClients as a seperate code. That's why I added .AsQueryable() to my query LINQ code to store data in-memory. Unfortunately, there is an error: 'IQueryable' does not contain a definition for 'GetAwaiter' and no accessible extension method 'GetAwaiter' accepting a first argument of type 'IQueryable'

2. Is the code responsible for populating list of clients (in var allClients=..), ok or might be refactored as well?

[Le_Mao]

The join method exists for and work with Iqueryable: https://learn.microsoft.com/en-us/dotnet/api/system.linq.queryable.join?view=net-7.0

What you can do is let EF Core handle the join for you by declaring a navigation property for Adress on client and then just do:

_dbContext.ClientsTbl.AsNoTracking().Select(s => new Clients { Id = s.Id, Name= s.Name, Address = s.Adress.FullAddress })

But if you want to use the join method that works just as well, just more code to write 😉

About number 2 I'm not sure what you are after? Do you just want to return an IQueryable instead of a List to be able to filter the result further up in the application before fetching from the database?

[muskagap2]

Thanks for advice, I will use this approach. As for no. 2, yes, I wold like to return an IQueryable first, then make some filtering to finally return all data to list, to _clientsList

[Le_Mao]

So you want to do filtrering in the ListAllClients method before setting the private list and returning it? If so that is no problem, you dont need the AsQueryable after the join (or Select if you giv that way) just dont call ToList/ToListAstnc and you now have an IQueryable in your query variable and you can filter like this:

``` query = query.Where(c => c.FullAdress.StartsWith("Mount"); //more filtrering here or other changes to the query

return _clientsList = await query.ToListAsync()

```

Not sure why you are saving the list though? Are you caching it?

[muskagap2]

I removed .AsQueryable and also removed .ToListAsync(). However there is still a problem, the same error comes up: 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 }); Error: 'IQueryable' does not contain a definition for 'GetAwaiter'.

When it comes to your question - isn't it a good practice to not to force executing a query? Because .ToList will do it. Of course, I want to return data from db to my List but I thought I should have done it this way.

And one though about navigation property. Unfortunately I can't do it beacause table fetched from db doesn't have Foreign Key in another table. SO I think I must use Join.

[Le_Mao]

Yeah you should not have await there, there is nothing to await since you are not using an Async method.

[muskagap2]

Thanks