Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ASP.NET linq Select with multiple joins

I've this database from EF code first, and now i have a difficult time getting my data.

I have the following entities:

Program : is a workout program that contains Workouts.

Workout : is a day routine that contains a list of Sets.

Set : is an Exercises repeated x times with y load.

Exercise: is an gym exercise which contains a Region.

Region : is a region on the human body and contains Muscles.

Muscle : is a muscle on the human body.

Model examples

Here are 3 model examples

    public class Workout
{
    [Key]
    public int WorkoutId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public virtual ICollection<Set> Sets { get; set; }
}
 public class Set
{
    [Key]
    public int SetId { get; set; }
    //Foreign key for Exercise
    public int ExerciseId { get; set; }
    [ForeignKey("ExerciseId")]
    public Exercise Exercise { get; set; }
    public decimal Load { get; set; }
    public decimal Order { get; set; }
}

    public class Exercise
{
    [Key]
    public int ExerciseId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    //Foreign key for Region
    public int RegionId { get; set; }
    public Region Region { get; set; }
}

And the scaffolded controller

// GET: api/Workouts
    public IQueryable<Workout> GetWorkouts()
    {          
        return db.Workouts;
    }

So that only gives me a Workout object with its iCollection Sets is null.

// GET: api/Workouts
    public IQueryable<Workout> GetWorkouts()
    {          
        return db.Workouts.Include(w => w.Sets);
    }

By including Sets i can get the corresponding Sets.

But what if i would like to get a Workout, including the Sets, and those Sets would include Exercises, and those Exercises would include Regions which in the end had a list of Muscles?

And what if i want a workout from id, like this:

        [ResponseType(typeof(Workout))]
    public IHttpActionResult GetWorkout(int id)
    {
        Workout workout = db.Workouts.Find(id);
        if (workout == null)
        {
            return NotFound();
        }

        return Ok(workout);
    }

How will i then include Sets?

EDIT And from the Visual Studio Template /Help page, i can see the following:

GET api/Workouts/{id}

Should provide med with:

{
   "workoutId": 1,
   "name": "sample string 2",
   "description": "sample string 3",
   "sets": [
   {
     "setId": 1,
     "exercise": {
     "exerciseId": 1,
     "name": "sample string 2",
     "description": "sample string 3",
     "regionId": 4,
     "region": {
     "regionId": 1,
     "regionName": "sample string 2",
     "muscles": [
        {
          "muscleId": 1,
          "latinName": "sample string 2",
          "dkName": "sample string 3",
          "enName": "sample string 4",
          "description": "sample string 5"
        },
        {
          "muscleId": 1,
          "latinName": "sample string 2",
          "dkName": "sample string 3",
          "enName": "sample string 4",
          "description": "sample string 5"
        }
      ]
    }
  },

But it just wont happen, im open for anything that returns me a full Program in JSON. skype, teamwievr, stack, another solution, anyhting.

like image 684
Kasper Sølvstrøm Avatar asked Nov 17 '15 22:11

Kasper Sølvstrøm


1 Answers

And finally i got it:

 public class Exercise
 {
     [Key]
     public int ExerciseId { get; set; }
     public string Name { get; set; }
     public string Description { get; set; }
     //Foreign key for Region
     //public int RegionId { get; set; }
     public virtual Region Region { get; set; }    //had to be virtual -.-
 }
like image 50
Kasper Sølvstrøm Avatar answered Sep 28 '22 04:09

Kasper Sølvstrøm