This may have just been a result of my PHP background trying to tell me how to do things and so it is quite possible that this post is common sense and obvious to others in the .NET / LINQ community.

I have been working on an ASP.NET MVC prototype application (Preview 3) and I ran into a very frustrating problem. Part of the system allows users who are logged in to vote a post up or down, a user can only vote once per post. There is a Posts table, a Users table and a Votes table and Votes contains a foreign key for both Posts and Users.

Now when a use submitted a vote the system created a new Vote object, passed in the PostID, the UserID of the current user and the result. LINQ then stuffed it in the data context and saved it back to the database. When a user came back to the post or a listing of posts, it didn’t display as having registered their vote although if they voted again the system blocked it which told me the data was there and LINQ could read it.

After quite a bit of head scratching and googling I came across Chris Rock’s post “LINQ TO SQL Caching Gotcha” which identified a similar problem and it did seem like mine was a caching issue. I tried his suggestion but to no avail so I ruled out caching.

So what was the solution? Well, let me do a before and after for you:

Before

Vote vote = new Vote();
vote.PostID = id;
vote.Result = (result == “up” ? true : false);
vote.UserID = CurrentUser.UserID;
DBDataContext.Votes.InsertOnSubmit(vote);
DBDataContext.SubmitChanges();

After

Vote vote = new Vote();
vote.Post = DBDataContext.Posts.Single(p => p.PostID == id);
vote.Result = (result == “up” ? true : false);
vote.User = CurrentUser;
DBDataContext.Votes.InsertOnSubmit(vote);
DBDataContext.SubmitChanges();

The key is on lines 2 and 4. In the first snippet I am assigning the numerical IDs to the foriegn key fields PostID and UserID, in the second I am passing in actual objects; the Post object found using the id variable and the User object found by using the CurrentUser property.

LINQ does not seem to connect the Vote to the User or Post if you directly assign the ID values, evidently you need to pass the object which is being referenced in order to get LINQ to recognise the connection and perform the required updates, despite the DB knowing exactly what is going on.

As I said before, perhaps this is well documented and I am just being slow to catch on but that took the better part of a week to find and fix.