Populating field from enum table

Question

I have the following tables

Entity
id,name,categoryid
21,"Blah",1

EntityCategory(Enum table)
id, name
1,"New Blahs"

I have a FK relationship between Entities->categoryid and EntityCategories->id

I have generated SubSonic classes for both as well a corresponding Model object for Entity
class Entity{ID,Name,CategoryName}

I am trying to return the Model.Entity type with category name filled in i.e.

public Entity GetEntityByName(string name){
  return new 
    Select(
      Entity.IdColumn,    
      Entity.NameColumn,
      EntityCategory.NameColumn)
   .From(Entity.Schema)
   .InnerJoin(Tables.EntityCategory)
   .Where(Entity.NameColumn).IsEqualTo(name)
   .ExecuteSingle<Model.Entity>();

Needless to say this is not working. I actually get a Model.Entity with the Entity.Name set to the EntityCategoryName.

Answer 1

If you use SubSonic 3.0 you can do this with projection:

var result = from e in db.Entities
where e.ID=1
select new Entity{
   ID=e.ID,
   name=e.Name, 
   CategoryName=(CategoryName)e.CategoryID
}

With SubSonic 2.x, I'd say to make it easy on yourself and extend the partial class with a readonly enum:

public partial class Entity{
   public CategoryName{
     return (CategoryName)this.CategoryID;
   }
}