Can not insert row to database because of relationship problems

Question

First of all I am using ASP.NET MVC 4 and Entity Framework 5. And this is my first project in MVC.

I am trying to insert a row to my WorkLogs Table. I have created my database with code first approach.

This is my WorkLogs Model (Since I created database, I didn't make any change to my model):

public class WorkLogs
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int WorkLogId { get; set; }
    [Required]
    public int Time { get; set; }
    [Required]
    public DateTime Date { get; set; }
    [Required]
    public DateTime CreatedDate { get; set; }
    [Required]
    public bool IsSent { get; set; }

    public virtual Users User { get; set; }
    public virtual Works Work { get; set; }
}

This model has created this table in database:

And these are relationships (It seems correct to me):

I am trying to add a new row with this code in my controller:

WorkLogs log = new WorkLogs()
{
    Time = iTime,
    Date = beginWeek.AddDays(i),
    CreatedDate = DateTime.Now,
    User = userObj, 
    Work = workObj, 
    IsSent = true
};

_db.WorkLogs.Add(log);
_db.SaveChanges();

When I run this, it throws an exception in _db.SaveChanges(); line.

An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

I understand there is a problem with relationships when I see this exception. And I also understand I am trying to insert an object to a column which wants an integer. But I don't understand that it created the User_UserId and Work_WorkId columns and gave them int data type. But in controller, it wants a user object.

My model, controller might be all wrong. But as I said I'm new in EF and MVC.

What changes I should do to insert a WorkLog to my database?

I hope I made myself clear.

Any help would be appreciated.

I recommend these 2 articles: Making Do with Absent Foreign Keys http://msdn.microsoft.com/en-us/magazine/hh708747.aspx and Why does Entity Framework Reinsert Existing Objects into My Database? msdn.microsoft.com/en-us/magazine/dn166926.aspx. Also, we need to see the `Works` and `Users` classes too, and the error message in the `InnerException` — Colin, Nov 28 '13 at 16:52
A few asides - you do not need `[Required]` tags on non-nullable types, `DatabaseGenerated(DatabaseGeneratedOption.Identity)` is redundant because it is the default on integer keys, and the normal naming convention for entity classes is singular. When you follow that convention EF will assume that `WorkLogId` is the `[Key]` for the `WorkLog` class and that attribute becomes redundant too. And your database tables are pluralised automatically. — Colin, Nov 28 '13 at 16:58
Here they are: Works:http://i.imgur.com/nvXREfc.png and Users: http://i.imgur.com/oRLLN8d.png And I made a few changes that Réda Mattar said. Now my inner ex. is: http://i.imgur.com/HPVCyNB.png — gkonuralp, Nov 29 '13 at 06:41
And before I forget, thanks for articles. Reading them immediately. — gkonuralp, Nov 29 '13 at 06:52

score 7 · Accepted Answer · edited May 23 '17 at 12:17

The inner exception is a SqlException. "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value". If you search for that message you will soon find the reason for that:

You have set CreatedDate and Date on WorkLogs to reasonable values so the culprit is likely to be CreatedDate and/or UpdatedDate in the instance of Works.

If you have not set them, then their values will be the default for .NET - DateTime.MinValue - year 0001. Try to insert that to the database and you get the overflow because in Sql Server the minimum value is SqlDateTime.MinValue - year 1753.

I suggest that you set the value of CreatedDate to DateTime.Now in your constructor and change the type of UpdatedDate to a nullable DateTime (the default will them be null rather than DateTime.Min)

Yes! It worked. Thank you very very much. Date was coming as year 0001. Now it writes to db perfectly. I must improve myself about reading errors. — gkonuralp, Nov 29 '13 at 09:31

score 1 · Answer 2 · answered Nov 28 '13 at 15:41

You can use ForeignKeyAttribute to link a navigation property with a foreign key :

public class WorkLogs
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int WorkLogId { get; set; }

    [Required]
    public int Time { get; set; }

    [Required]
    public DateTime Date { get; set; }

    [Required]
    public DateTime CreatedDate { get; set; }

    [Required]
    public bool IsSent { get; set; }

    public int Users_UserId { get; set; }

    public int Works_WorkdId { get; set; }

    [ForeignKey("Users_UserId")]
    public virtual Users User { get; set; }

    [ForeignKey("Works_WorkdId")]
    public virtual Works Work { get; set; }
}

Not sure if it is a good or bad step but it gives: An error occurred while updating the entries. See the inner exception for details. — gkonuralp, Nov 28 '13 at 15:54

score 0 · Answer 3 · edited May 23 '17 at 12:34

It's important to be aware that Entity Framework does not support unsigned data types as following:

How to use unsigned int / long types with Entity Framework?

http://tektutorialshub.com/code-first-conventions-in-entity-framework/

If by chance you have set any Primary Key as UNSIGNED DATATYPE, you not only going to get this specific error, as well as many others related of table relationships on your model.

Can not insert row to database because of relationship problems

3 Answers3