ASP.Net MVC LINQ question

[cotton31]

So I'm diving in to MVC having used Web Forms previously.

I'm also dabbling with LINQ for the first time!

I'm making a 'Tasks' application, and tasks can have people assigned to them, so in my database I have 3 tables -

Tasks (ID, Description)
People (ID, Name)
PeopleTasks (ID, TaskID, PeopleID, Primary)

I've created a model from this in MVC5 and 'scaffolded' a controller and views for each table.

My issue now is that in the tasks index, which currently just shows the description of the task, I want to add the persons name, but only if the PersonTask Primary field is true (there can only be one Primary person per task).

I've written a LINQ query which filters on the Primary field, but then I don't seem to be able to select both the task and related person.

Hope this makes sense!

 

[Nuluvius]

Is this what you are meaning?

Lets represent your tables as PODs for the sake of a demo thus:

    class Tasks
    {
        public int Id { get; set; }
        public string Description { get; set; }
    }

    class People
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    class PeopleTasks
    {
        public int Id { get; set; }
        public int TaskId { get; set; }
        public int PeopleId { get; set; }
        public bool Primary { get; set; }
    }

Now lets make a simple console application; we fill them with test data and execute the expression like so:

    static class Program
    {
        static void Main(string[] args)
        {
            var tasks = new List<Tasks>
                            {
                                new Tasks { Id = 0, Description = "Some Task A" },
                                new Tasks { Id = 1, Description = "Some Task B" },
                                new Tasks { Id = 2, Description = "Some Task C" },
                            };

            var people = new List<People>
                            {
                                new People { Id = 0, Name = "Noob A" },
                                new People { Id = 1, Name = "Noob B" },
                                new People { Id = 2, Name = "Noob C" },
                            };

            var peopleTasks = new List<PeopleTasks>
                                  {
                                      new PeopleTasks { Id = 0, PeopleId = 0, TaskId = 0, Primary = true },
                                      new PeopleTasks { Id = 1, PeopleId = 0, TaskId = 1, Primary = false },
                                      new PeopleTasks { Id = 2, PeopleId = 0, TaskId = 2, Primary = false },
                                      new PeopleTasks { Id = 3, PeopleId = 1, TaskId = 0, Primary = false },
                                      new PeopleTasks { Id = 4, PeopleId = 1, TaskId = 1, Primary = true },
                                      new PeopleTasks { Id = 5, PeopleId = 1, TaskId = 2, Primary = false },
                                      new PeopleTasks { Id = 6, PeopleId = 2, TaskId = 0, Primary = false },
                                      new PeopleTasks { Id = 7, PeopleId = 2, TaskId = 1, Primary = false },
                                      new PeopleTasks { Id = 8, PeopleId = 2, TaskId = 2, Primary = true },
                                  };

            var relatedData = from t in tasks
                              from p in people
                              from pt in peopleTasks.Where(r => r.PeopleId == p.Id && r.TaskId == t.Id && r.Primary)
                              select new { t, p, pt };

            foreach (var rdr in relatedData)
            {
                Console.WriteLine("Task #{0}: {1}, Allocated to: {2}", rdr.t.Id, rdr.t.Description, rdr.p.Name);
            }

            Console.ReadKey();
        }
    }

 

[touch]

My issue now is that in the tasks index, which currently just shows the description of the task, I want to add the persons name, but only if the PersonTask Primary field is true (there can only be one Primary person per task).

Your database isnt really setup right for what you need. There is nothing in the database structure to stop you adding multiple primary users to a task. You could add checks into your code to control it manually, but as LINQ can work out the database structure and relationships, it will be much easier to structure the database correctly then let LINQ do all of the work in managing relationships.

If you want to keep your database structure as it is, it would be easier for you to display the data in the PeopleTasks index.
You need to use an 'include' in your linq query. Something like:

db.PeopleTasks.Where(pt => pt.Primary == true).Include(Tasks).Include(People)

(you'll need to find examples of this as i cant remember the exact syntax)
Then, when you pass the PeopleTasks objects to the view, you can display the name/description by using "model.People.Name" and "model.Task.Description".

You could change your database to add a primaryPeopleID field to the tasks table. That way you create a relationship where only 1 people can be the primary people of a task.
Then you could use the Task index view to display the data with a query like:

db.Tasks.Include(people)

Then in the view, display the name/description with "model.People.Name" and "model.Description"

 

[cotton31]

So you'd have a field within the Tasks table to record the Primary person, and then PeopleTasks would record additional people?

 

[touch]

That's the way I would do it because it makes the relationships easier to work with in the code.
I'm not sure what best practice in terms of the database structure would be? Maybe someone with more db experience could help?

 

[Nuluvius]

That's the way I would do it because it makes the relationships easier to work with in the code.
I'm not sure what best practice in terms of the database structure would be? Maybe someone with more db experience could help?

Yes exactly how he has it. It's normalized as much and as correctly as possible imo.