Return list object with list object

Question

I have three tables Car Makes, Car Model and CarsandModel. I have the Carsand Model table because a model could be built by multiple manufactures. I want to return a list of car makes that has a list of car models. The longic I have now is not filtering the list on car makes for the car model.

I have tried to add a where statement but I am still not getting the correct return

public class CarMake
{
    public int CarMakeId { get; set; }
    public string CarMakeName { get; set; }
    public List<CarModel> CarModel { get; set; }

}
public class CarModel
{
    public int CarModelId { get; set; }
    public string CarModelName { get; set; }
}
public class CarsandModel
{
    public int CarMakeId { get; set; }
    public int CarModelId { get; set; }
}


var CarModel = (from cmake in db.CarModel
               select new CarModel
               {
                   CarModelId = cmake.CarMakeId,
                   CarModelName = cmake.CarMakeName,
                   CarMake= (from cmake in db.Carmake
                               join cam in db.CarsandModel on cmake.CarMakeId equals cam.CarMakeId
                               where (camodel.CarMakeId == cmake.CarMakeId)
                               select new Asset
                               {
                                   CarMakeId = cmodel.CarMakeId,
                                   CarMakeName = cmodel.CarMakeName
                               }).ToList()
               }
            ).ToList();

Answer 1

When using entity framework people tend to (group-)join the tables themselves, while it is much easier to use the ICollections instead.

First of all: stick to the Entity Framework Code-First Conventions!

For example:
Every CarMake has zero or more CarModels. You decided to declare this as a List<CarModel>. Are you sure that CarModel[4] has a defined meaning? And I wonder what CarModel.Insert(4, new CarModel()) would mean in your code. Better stick to the interface that your database can really handle: ICollection<CarModel>.

The relation between CarMakes and CarModels seems to be a many-to-many: every CarMake has zero or more CarModels, Every CarModel is made by zero or more CarMakes.

In relational databases a many-to-many relationship is implemented using a junction table. Your class CarsAndModel represents a row in this junction table. However, when using entity framework you don't need to mention the junction table. When you design your classes properly, entity framework recognizes the relations between your tables and creates the proper junction table for it.

class CarMake
{
    public int CarMakeId { get; set; }
    public string CarMakeName { get; set; }

    // every CarMake makes zero or more CarModels (many-to-many)
    public virtual ICollection<CarModel> CarModels { get; set; }
}
class CarModel
{
    public int CarModelId { get; set; }
    public string CarModelName { get; set; }

    // every CarModel is made by zero or more CarMakes (many-to-many)
    public virtual ICollection<CarMake> CarMakes {get; set;}
}

In entity framework the columns of the tables are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)

For completeness the DbContext:

class CarContext : DbContext
{
    public DbSet<CarMake> CarMakes {get; set;}
    public DbSet<CarModel> CarModels {get; set;}
}

This is all that entity framework needs to know to detect the many-to-many relationship. Entity framework will create the junction table for you and will keep this table up-to-date as needed.

Only if you have good reasons to deviate from the code-first conventions (and you can convince your project leader) you'll need attributes or fluent API to inform entity framework about your deviations.

But how am I supposed to join the tables if I can't access the junction table?

Answer: don't do a (group-)join, use the ICollections!

Requirement: Give me the CarModels, each with their CarMakes

var result = dbContext.CarModels
    .Where(carModel => ...)       // only if you don't want all CarModels
    .Select(carModel => new
    {
         // Select only the properties you actually plan to use!
         Id = carModel.CarModelId,
         Name = carModel.CarModelName,
         ...

         CarMakes = carModel.CarMakes
             .Where(carMake => ...)     // only if you don't want all CarMakes of this model
             .Select(carMake => new
             {
                  // again: select only the properties you plan to use
                  Id = carMake.CarMakeId,
                  Name = carMake.Name,
                  ...
             })
             .ToList(),
    }

Entity Framework knows your many-to-many relation and will create the proper (group-)join for you.