LINQ2SQL: Update an entity field that has a reference to another entity

Question

I have the following situation in LINQ: I have an object (a blog "Post") that has a "Status" field which can take on different values, and there's a table listing all these values (this table just has Id, Desc).
Now, since the relationship between both tables is created, in the Model, Post.Status is not an int, but an instance of "PostStatus".

Thus, if I want to set the status of a Post to, say, 20, I can't just set the field to 20, I need to have an instance of PostStatus to assign. These status values are all hardcoded with the logic, so it's just fine to have "20" hardcoded in my code.
Is there a better way to do it that this?

switch (Action) {
 case "Ignore":
  post.PostStatus = (from s in db.PostStatus where s.Id == 90 select s).First();
  break;
 case "Assign":
  post.PostStatus = (from s in db.PostStatus where s.Id == 20 select s).First();
  break;
 case "MarkDone":
  post.PostStatus = (from s in db.PostStatus where s.Id == 30 select s).First();
  break;
 case "MarkPublished":
  post.PostStatus = (from s in db.PostStatus where s.Id == 40 select s).First();
  post.Public = true;
  break;
}

I hate this code, honestly, first of all because it needlessly queries the DB to get a PostStatus instance, but mostly because it's just so verbose.
Is there a better way to do this?

Thanks!
Daniel

Answer 1

It sounds like PostStatus is more-or-less an enum. We do things like this all the time by having a related table in the DB (as you do), but then remove the relationship in the DBML and change the CLR type on the child column to an enum type. The enum type is declared with the same values as the rows in the parent DB table (in your case, Assign = 20, MarkDone = 30, etc). I wrote a tool that fills the table's values from the code for all our enums- that way the code is the "master" (it bombs if someone tries to change a value or name of an existing value, but new values "just work"). Now when you want to do something like what you're doing, the data you need is already there in the enum- just set Post.PostStatus = Action (assuming you also change Action from a string to the same enum type)- no DB hit necessary.

We also have some of these that are a little more dynamic- those we cache once from the DB on app startup and drop into a Dictionary so we can just set the IDs without the DB hit. As long as you're not talking about millions of values, it's very efficient.