Adding a custom member to a mapped type

Question

Hi!

I'm using Linq 2 Sql and I'd like to add a custom member on one of my data type. For example: I have tables Companies, Departments and Employees. Departments has a FK in Companies and Employess has a FK in Deparments. Very simple.

My goal is to add an EmployeeCount property on Company that will, of course, returns the number of employee in the company (by suming the number of employee in company's departments).

As you may know, it's really trivial to do: just add a member on the partial class and do the stuff. The problem is, I now want to sort on this custom member.

How can I write this custom member for Linq to be able to translate the logic into a valid SQL orderby? I tried with a SPROC but did not success.

Any help appreciated! Fabian

Answer 1

I guess your may need to add this column to your schema, in order for SQL server to recognize it and take in under consideration in his optimizations.

I would have add a trigger that will update this column automaticly upon changes on your data.