Aggregating clients and client types

Question

The code I have put together achieves what I want, but I don't know if it is the right way to go about tackling the problem.

I am dealing with 2 tables (CLIENT, CLIENTTYPE) each of which I have an auto-generated entity for in my models folder. As I don't actually want to send all the data from each entity back to the user, I have created a viewmodel for each in a ViewModels folder.

A CLIENT can have multiple CLIENTTYPE, so I have created the a List property of ClientTypeViewModels in my ClientViewModel

    public ClientsWithTypesViewModel getClientAndTypesByCode(string code)
    {
        var x =
            from c in _context.Cases
            where c.ClientID == code
            select new ClientsWithTypesViewModel
            {
                ClientID = c.ClientID,
                Title = c.Title,
                Name = c.FullName,
                ClientTypes = (from w in _context.ClientTypes where w.ClientType == c.ClientType select new ClientTypeViewModel { ClientType = w.ClientType, Description = w.Description }).ToList()
            };
        return x.FirstOrDefault();
    }

Answer 1

You should not need to populate the ClientTypes property with your own query. Usually it is handled by a navigation property that already knows how to handle related tables but for this to work you first need to set the foreign key properly. Since you did not post the models I cannot say if it would be possible in your case.

You can read this article explaining how it should work: Don’t use Linq’s Join. Navigate!

Then you should be able to load the relations with Include:

var x =
    from c in _context.Cases.Include("ClientTypes")
    where c.ClientID == code
    select c;

See also Loading Related Objects