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

• Free2Rhyme2k: Thanks - will do some reason up on that. I've actually simplified the condition to populate the ClientTypes property. In reality it is several lines long. Using navigation properties, would I use the single condition as in the code I posted originally and then do some more work to get rid of the stuff I don't want in my ClientTypes list?