CODEWITHSUNDEEP
c#sqlentity-frameworkasp.net-mvc-4
Kulahan
Kulahan

Reputation: 522

Are my tables linked correctly for Entity Framework?

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

Upvotes: 1

Views: 221

Answers (1)

schlonzo
schlonzo

Reputation: 1406

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

Upvotes: 3

Related Questions