Getting data out of a model made of multiple tables in a one-to-many relationship

Question

Hi,

I've got a relatively simple question about asp.net MVC models.

I've got a model based on two tables that are linked in a one-to-many relationship.

table AnimalGroup(ID,name)

table AnimalSubGroup(ID,name,AnimalGroupID)

Each AnimalGroup has any number of AnimalSubgroups.

How do I iterate through each AnimalGroup's AnimalSubGroups and get AnimalSubGroup.name (for example)? I'm new to asp.net MVC and have been following various tutorials, but while they're excellent for getting a basic application set up and getting results out of a single table, I'm stuck as to how I'd get results from several tables linked in the same model. I've seen references to ViewModel as a solution, but it seems that ViewModel is more useful for putting data from two unrelated tables into a single View.

Thanks in advance.

Answer 1

First. Do you have foreign keys defined in your database? If yes, edmx model generator will define all conections. If not, do it right now. When it is done, you can select subgroup name by:

1. Taking it directly from context:

   context.AnimalSubGroupSet.Where(sg => sg.AnimalGroupID = requestedAnimalGroupID).Select(sg => sg.Name).ToList;

2. Taking it from AnimalGroup:

   animalGroup.AnimalSubGroups.Select(sg => sg.Name);

This code may need adjustments, but they shouldn't be complicated.

Answer 2

Perhaps you should take a look at LINQ's SelectMany query operator. This sub-iterates over one-to-many data table relationships. Note that you can chain SelectMany calls:

var mymanyselections = datacontext.parenttable.SelectMany(l=>l.Name).SelectMany(m=>m.Name);

This assumes that you have foreign key relationships in your data tables.