How can i show data from other tables using id from existing ones

Question

I am using .NET Core MVC, and the situation goes like this.

I have a database, with a many to many relationship.

I have a "Courses" table. And i have a "Lecturers" table.

I also have a table "CourseLecturers" with composite key, which consists of 2 id's. LecturerId, and CoursesID.

I am using Entity Framework, and Model View Controller to show my data.

The model for my Courses table looks like this :

public partial class Courses
{
    [Key]
    public int CourseId { get; set; }
    public string CourseTitle { get; set; }
}

The model for my Lecturers table looks similair :

public partial class Lecturers
{
   [Key]
    public int LecturerId { get; set; }
    public string Lecturer { get; set; }
}

And the model for my CourseLecturers table looks like this :

public partial class CourseLecturers
{
    public int CourseId { get; set; }
    public int LecturerId { get; set; }  
}

The issue i have, or, what i want to do, is : i want to use my CourseLecturers controller, so that it will show CourseTitle, and Lecturer. Not CourseId and LecturerId. Is there any way i can access those attributes?

This is my Index method in my CourseLecturers controller :

 public IActionResult Index()
    {
        /*
        CourseLecturers courseLecturers = new CourseLecturers();
        List<String> lista=new List<String>();
        foreach (var item in _context.CourseLecturers)
        {
            lista.Add(_context.Courses.Where(c => c.CourseId == courseLecturers.CourseId).ToString());
        }
        */
        return View(_context.CourseLecturers.ToList());
    }

I am wildly experimenting with controller, and this will return just the default integers and id's. (since i commented the code) I am not sure who to turn to. Is it even possible to have two strings appear, instead of integers, since my CourseLecturers model has two integers ? It seems foolish not to allow something like that. So any help i can get will be greatly appreciated.

After trying stuartd solution

Soo, this is my default context for CourseLecturers table :

 modelBuilder.Entity<CourseLecturers>(entity =>
        {
            entity.HasKey(e => new { e.LecturerId, e.CourseId })
                .HasName("PK_courseLecturers");

            entity.ToTable("courseLecturers");

            entity.Property(e => e.LecturerId).HasColumnName("lecturerID");

            entity.Property(e => e.CourseId).HasColumnName("courseID");
        });

Which throws build errors. The changes i made to that code were as follows :

modelBuilder.Entity<CourseLecturers>(entity =>
        {
            entity.HasKey(e => new { e.Lecturers.LecturerId, e.Courses.CourseId })
                .HasName("PK_courseLecturers");

            entity.ToTable("courseLecturers");

            entity.Property(e => e.Lecturers.LecturerId).HasColumnName("lecturerID");

            entity.Property(e => e.Courses.CourseId).HasColumnName("courseID");
        });

Which is sintactically correct, but throws an exception : An exception of type 'System.ArgumentException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code

Additional information: The properties expression 'e => new <>f__AnonymousType1`2(LecturerId = e.Lecturers.LecturerId, CourseId = e.Courses.CourseId)' is not valid. The expression should represent a property access: 't => t.MyProperty'. When specifying multiple properties use an anonymous type: 't => new { t.MyProperty1, t.MyProperty2 }'.

So, there is that.

Sounds like it doesn't like the way the composite PK is declared. I havent used this particular syntax, but according to [this page](https://msdn.microsoft.com/en-gb/data/jj591583.aspx#Composite) _When you have composite keys, Entity Framework requires you to define an order of the key properties. You can do this using the Column annotation to specify an order._. Or you could just add a regular dedicated PK column, which is better practice anyway in my opinion. — stuartd, Jul 23 '16 at 11:55
But still, how do i access the course name, to show it in the junction table? Instead of two ID's ? I have mapped it, but am still not sure how to actually display the data. I possess both parts of the composite key, now just how to use them to get the data from the Courses table. — Marin, Jul 23 '16 at 22:06

score 0 · Answer 1 · answered Jul 23 '16 at 00:06

0

Your CourseLecturers class should contain references to the actual Course and Lecturer, rather than the Id:

public partial class CourseLecturers
{
    public Course Course { get; set; }
    public Lecturer Lecturer { get; set; } 
}

answered Jul 23 '16 at 00:06

stuartd

70,509
14
132
163

(Your class names should be singular, rather than plural) – stuartd Jul 23 '16 at 00:06
I reversed engineered my model by following the instructions from the official Core documentation http://ef.readthedocs.io/en/latest/platforms/aspnetcore/existing-db.html#reverse-engineer-your-model And it named it like this automatically. – Marin Jul 23 '16 at 00:15
That would be be because you named your tables "Courses" and "Lecturers". Not that it really matters: [Table Naming Dilemma: Singular vs. Plural Names](http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names) has lots of good discussion on the subject. Noting also that the class names and table names don't need to be the same. – stuartd Jul 23 '16 at 00:24
Using your solution has made my Context go haywire. modelBuilder.Entity(entity => { entity.HasKey(e => new { e.LecturerId, e.CourseId }) .HasName("PK_courseLecturers"); entity.ToTable("courseLecturers"); entity.Property(e => e.LecturerId).HasColumnName("lecturerID"); entity.Property(e => e.CourseId).HasColumnName("courseID"); }); I changed from **e.LecturerId** to **e.Lecturers.LecturerId** and it is not functioning. – Marin Jul 23 '16 at 00:25
Ill post detailed example in a few minutes. – Marin Jul 23 '16 at 00:30
Making a change like this will certainly require refactoring elsewhere. But it will allow you to accomplish your stated goal. – stuartd Jul 23 '16 at 01:01
Well, i am not sure what needs refactoring. I mean, if i can just use a class with those two references like that, then i dont need a junction table. Or ? – Marin Jul 23 '16 at 01:05
The class _represents_ the table. In SQL of course you just have the foreign keys to the table, but this allows EF to present the _entities_, allowing you to use `CourseTitle` in code. – stuartd Jul 23 '16 at 01:08
But then, should i change my CourseLecturers table in database ? Or should i leave the composite key as it is ? I understand the solution you presented, but i do not understand, how should i change my table in database. Or is my issue somwhere else. – Marin Jul 23 '16 at 01:19

score 0 · Answer 2 · answered Jul 25 '16 at 18:26

So, the solution which worked for me is :

In junction table you need to add "[ForeignKey(nameof(nameOfTable))]" for each of the parts of the primary key (since i have a composite PK).

My junction table then looks like this :

public partial class CourseLecturers
{
    [Key]
    [ForeignKey(nameof(Courses))]
    public int CourseId { get; set; }
    public Courses Courses { get; set; }

    [Key]
    [ForeignKey(nameof(Lecturers))]
    public int LecturerId { get; set; }
    public Lecturers Lecturers { get; set; }
}

In my junction table controller, into index method, you need to return the list to your view like this :

public IActionResult Index()
    {
        return View(_context.CourseLecturers
       .Include(c => c.Courses).Include(c => c.Lecturers).ToList());
    }

Atleast that is what worked for me.

And in the end, in your view, you have something like this :

 @foreach (var item in Model)
{
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Courses.CourseTitle)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Lecturers.Lecturer)
        </td>
    </tr>
}

And this is how it worked for me in the end.

How can i show data from other tables using id from existing ones

2 Answers2