Entity Framework pulling null in three tables

Question

I have three tables in my database. Every table works with different Entity Class, the code:

public class Quizs
{
    [Key]
    public int QuizId { get; set; }            
    public string Title { get; set; }    
    public string Instructions { get; set; }    
    public string IsTimerEnabled { get; set; }    
    public virtual ICollection<Question> Questions { get; set; }
}

public class Question
{
    [Key]
    public int QuestionId { get; set; }    
    public virtual ICollection<Answers> Answers { get; set; }    
    public int RightAnswer { get; set; } // Key of Answers[]    
    public int QuestionType { get; set; } // 1 = One choise, 2 = Multiple Choise    
    public string Explantion { get; set; } // To appear after answering the question    
    public int MaxTime { get; set; } // In seconds
}

public class Answers
{
    [Key]
    public int asId { get; set; }    
    public int Id { get; set; }    
    public string Text { get; set; }
}

So now i need to output all the data in this three tables to the WebApi page.

Here is the code that pulling out the data in the controller:

// GET api/QuizsData/
    public IEnumerable<Quizs> Get()
    {

        return dba.Quiz.Include("Questions").Include("Questions.Answers").ToList();
    }

This is what I get when I go to the route of the api:

{"Message":"An error has occurred.","ExceptionMessage":"Invalid column name 'Question_QuestionId'.\r\nInvalid column name 'Question_QuestionId'.","ExceptionType":"System.Data.SqlClient.SqlException","StackTrace":" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\r\n at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\r\n
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"}

I got this Exception Message.. any help?

Thanks!

Answer 1

I don't think you've set up your relationships the way you think you have.

Try these changes:

public class Quizs
{
    // no attribute
    public virtual ICollection<Question> Questions { get; set; }
}

public class Question
{
    // no attribute
    public virtual ICollection<Answers> Answers { get; set; }

    // add
    [ForeignKey("Quiz")]
    public int QuizId { get; set; }
    public virtual Quizs Quiz { get; set; }
}

public class Answers
{
    // add
    [ForeignKey("Question")]
    public int QuestionId { get; set; }
    public virtual Question Question { get; set; }
}

(Also, consider turning your class names to singular. It'll make for less semantic confusion.)

---

A little more explanation on what's what:

What you're trying to achieve here is called a one-to-many relationship. For example, you have many questions pointing to one quiz. Or you have many answers pointing to one question. In the quiz-question relationship, the quiz is the "one" side and the question is the "many" side. In the question-answer relationship, the question is the "one" side and the answer is the "many" side.

To achieve such a relationship in Entity Framework, all you have to do is go to the "many" side of the relationship and add something like this:

public virtual Quiz Quiz { get; set; }

And then go to the "one" side of the relationship and add something like this:

public virtual ICollection<Question> Questions { get; set; }

Entity Framework understands that the first declaration means "create a foreign key to the table Quiz" and the other means "multiple records of the Question table will point here".

This works just like the relationships between normal classes, except that you make the properties virtual so that the Entity Framework can override them at runtime with a property that actually implements this relationship.

As things are now, from your ("many" side) model you have no access to the foreign key created in the database. You do have access to the record it refers to, but not to the foreign key itself (at least not without fetching the other record). To fix this, you can tell the Entity Framework to use an explicitly declared property as the foreign key, like this:

[ForeignKey("Quiz")]
public int QuizId { get; set; }

The ForeignKey attribute says that the following int property (QuizId) will be the actual foreign key used by the property Quiz, instead of letting it implicitly create its own foreign key in the table that would be inaccessible to us.

To put it another way: a foreign key was going to be created anyway, since you referenced another model. The attribute is there to denote that you want to have access to that foreign key yourself. In most cases, you'll want this.

Answer 2

With entity framework you can eager load child entities:

 return dba.Quiz.Include("Questions").Include("Questions.Answers").ToList();

Or with lambda syntax (will not break if you will rename property again):

 return dba.Quiz.Include(q => q.Questions.Select(x => x.Answers)).ToList();

See remarks on Include method to understand its syntax.

---

Also you should fix your key on Answer entity. It should be asId because you are using Id as foreign key for question (weird naming btw):

public class Answers
{
    [Key]
    public int asId { get; set; }  // use this property as PK
    public int Id { get; set; }  // because this is FK
    public string Text { get; set; }
}

Answer 3

Use Include method for eager loading related entities, like described here: http://msdn.microsoft.com/en-US/data/jj574232