Are my tables linked correctly for Entity Framework?

Question

Forewarning: I know approximately nothing when it comes to MVC/Entity Framework/Linq queries.

I'm having an issue when I try to query the database. Here's the query I'm using:

int? UserId = db.StudentModel
    .Where(c => c.UserName == certUserName)
    .Select(c => c.UserId)
    .FirstOrDefault();

When it searches the database, it successfully retrieves the UserId.

The problem is that I then use the following query:

CompletionsModel student = db.Completions.Find(UserId);

When I do this, it throws an inner exception that states

{"Invalid column name 'UserProfile_UserId'."}

The weird thing is that when I go to my code and mouse over the 'db' part of the command to see what data it's holding, it has CourseModel, StudentModel, and Completions (though the model's actual filename is CompletionsModel - is that a clue?), so it seems like they're linked properly.

Here's the code for my three models and the database context.

CompletionsModel (UserProfile is white text in my code; not sure why it's teal here Same with UserId and CompletionDate):

[Table("Completion")]
    public class CompletionsModel
    {
        [Key]
        public int UserId { get; set; }
        public string PRD_NUM { get; set; }
        public DateTime CompletionDate { get; set; }

        public virtual CourseModel PRD { get; set; }
        public virtual StudentModel UserProfile { get; set; }
    }

CourseModel:

[Table("PRD")]
    public class CourseModel
    {
        [Key]
        public string PRD_NUM { get; set; }
        public string PRD_TIT { get; set; }

        //because any number of students can be enrolled in one course
        public virtual ICollection<CompletionsModel> CompletionsModel { get; set; }
    }

StudentModel:

[Table("UserProfile")]
    public class StudentModel
    {
        [Key]
        public int UserId { get; set; }
        public string UserName { get; set; }

        public virtual ICollection<CompletionsModel> CompletionsModel { get; set; }
    }

DBContext:

public class ClassContext : DbContext
{
    public ClassContext()
        : base("DefaultConnection")
    {
    }

    public DbSet<StudentModel> StudentModel { get; set; }
    public DbSet<CompletionsModel> Completions { get; set; }
    public DbSet<CourseModel> CourseModel { get; set; }
}

And finally, an image of my database layout - maybe this will help things along, too:

Database photo

UserId is the primary key in the database, so you probably shouldn't be using a nullable int for your linq query. — Robert Aguilar, Jul 29 '14 at 19:28
I won't be in the end; this was just to help troubleshoot for now. — Kulahan, Jul 29 '14 at 19:30
Hmmmm, everything looks good in my opinion. Are you sure you are targeting the correct database (via correct connectionstring)? — schlonzo, Jul 29 '14 at 19:31
I'd have to be targeting the correct database; I'm getting my UserId from it. I was pretty sure the error was going to be in the Index view, but it doesn't even make it there. Does anyone know if MVC starts to build the view before you've even hit the 'return'? — Kulahan, Jul 29 '14 at 19:34
Have you tried `CompletionsModel student = db.Completions.Find(c => c.UserId == UserId);`? — Mike, Jul 29 '14 at 19:35
"cannot convert lambda expression to type 'object[]' because it is not a delegate type" — Kulahan, Jul 29 '14 at 19:36
Could you try `CompletionsModel student = db.Completions.Where(p => p.UserId == UserId).Single()` — schlonzo, Jul 29 '14 at 19:43
Though it compiled, that's still giving me the error {"Invalid column name 'UserProfile_UserId'."} — Kulahan, Jul 29 '14 at 19:49

schlonzo · Accepted Answer · 2014-07-29T20:11:59.717

3

I'm too at the beginning of Entity Framework, but what does irritate me is, that you have kind of foreign key relationship between Completion and UserProfile, without really defining, a int column as foreign key in your Completion Table.

You could try to change

public virtual StudentModel UserProfile { get; set; }

to something like

public virtual int StudentId { get; set; }
[ForeignKey("StudentId")]
public virtual StudentModel UserProfile { get; set; }

The same for PRD

But maybe a change in your database is not what you want. What you could also do, is to remove these lines

public virtual CourseModel PRD { get; set; }
public virtual StudentModel UserProfile { get; set; }

Hope that helps.

EDIT: I guess the problem is, that you are missing the ForeignKey Attribute at your UserProfile property in your Completions table. So use

[ForeignKey("UserId")]
public virtual StudentModel UserProfile { get; set; }

instead of

public virtual StudentModel UserProfile { get; set; }

if the UserIds are representing the same user

edited Jul 29 '14 at 20:11

answered Jul 29 '14 at 20:01

schlonzo

1,409
13
16

I added the foreign keys and it worked. I guess that, without them, the program couldn't figure... something out. I dunno; Entity Framework is black magic. – Kulahan Jul 29 '14 at 20:33
1

Maybe Entity framework auto generates the name for the foreign key, if you don't tell it to it. In your case the name was this mysterious UserProfile_UserId. But I am glad I could help you! – schlonzo Jul 29 '14 at 20:48
Ah, that makes sense. I didn't think about that, but I'm sure it has something to do with it. It probably auto-generates it based on how I named those "public virtual"s. Thanks again! – Kulahan Jul 29 '14 at 21:46

Are my tables linked correctly for Entity Framework?

1 Answers1

Linked