Add property to LINQ that is combination of multiple tables?

Question

I currently have 3 separate tables : Course, Category, and CourseCategory. CourseCategory is the connection only having the CourseID and CategoryId. I need a way in LINQ-to-SQL to add a Category property to Course objects that abstracts past the multiple tables. We need to change the application to only allow one category, but we don't want to change the database in case we need to switch it back later.

How can I add a property to Course that will get the first category in the CourseCategory table?

Answer 1

Just extend your Course entity with a new property. The following will expose the single category (not the CourseCatergory, but you can easily change that if you want the CourseCategory) assocciated with the course, return null if there is none and throw an exception if there is more than one.

public partial class Course
{
   public Category Category
   {
      get { return this.CourseCategories.SingleOrDefault().Category; }
   }
}

You could also add a setter to make the property writable.

UPDATE

You should even use Single() instead of SingleOrDefault() if having no category assocciated is not allowed, too.