Entity Framework throws SqlDate exception on the *lookup* table

Question

I am using Entity Framework with asp.net mvc, but I don't think mvc plays a big role here.

I have an object Customer, and a lookup table (there are several, and they all behave the same way; so for simplicity I'll pick Territory). Both Customer and Territory have LastUpdated field (Datetime that is set manually in the code).

If I hardcode Territory, and get only Customer data from the View, I don't have any problems:

public ActionResult CreateCustomer([Bind(Exclude = "CustId")] Customer cm) {
    cm.Territory = (from t in repo.Territory where t.ID == 2 select t).First();
    repo.AddToCustomer(cm);
    repo.SaveChanges();
}

As I said, no problems. However, if I use a dropdown in the view with the matching id (Territory.ID) - there is a problem. I have the following line in controller:

ViewData["territory"] = new SelectList(repo.Territory, "ID", "Name", 1);

and corresponding line in the View:

Territory: <%= Html.DropDownList("Territory.ID", (IEnumerable<SelectListItem>)ViewData["territory"])%>

I get good news and bad news: the good news is that I get the territory ID nicely assigned to the appropriate member of Customer object. The bad news is that Territory.LastUpdated value is set to 1/01/0001. Obviously, I don't care about this value - but it looks like EF cares. When I call SaveChanges I am getting the following error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

Looks like EF is trying to retrieve the value from the database, and then compare it with EF value... or maybe something else - but the bottom line is I can't figure out how to prevent it from trying to be so smart.

For now, I name DropDown ID to be something different ("terID" instead of "Territory.ID"), and using FormCollection:

int terID = Int32.Parse(formData["terID"]);
cm.Territory = (from d in repo.Territory
     where d.ID == terID select d).First();

This works (which makes me comfortable with my analysis) but this cannot be the best way. I also can't believe that nobody bumped into such problem - but I couldn't find anything relevant... The best I could find is link text but it's more of a hint without much details

I tried to cut out all unrelated stuff from the code - so if there are some typos, they are in the post; not necessarily in the code itself! Thanks

Answer 1

Territory.LastUpdated is being set to the default value, as EF saves the whole row / what has changed you get an exception.

The easy way to fix this is to set it to DateTime.Now before saving.

Answer 2

I'm assuming you are using EF 1.0 which is not good at direct foreign key maping.

Territory posted from view to action is not bound to Datacontext, hence when you save your customer object - EF saves attached Territory Object as well.

You have to get Territory object from db first and then assign it to customer.

And Your solution is perfectly fine, cos there is no other for EF 1.0 )-: