Simple way to use Foreign Key values for sorting?

Question

Disclaimer: I jumped to C# 2008 recently and SubSonic 3 (3.0.0.4) at the same time. I haven't used Linq for much of anything in the past.

Is there an easy way to use the foreign key display value for sorting, rather than the FK Id (which is numeric)?

I've added a new Find method in my ActiveRecord.tt to help with sorting based on a string field name but after doing some testing I realized that even though its working as it should be, I am not handling foreign key fields at all (they are just sorting by their value).

Even if I need to change how I am accessing the data it is early enough in the project to do that. Just looking for suggestions.

Answer 1

So you have table A which has id of table B as a foreign key and you want to sort table A by the DisplayName column of table B rather than the id of table B?

The only way to achive this is by a join.

SELECT tableA.* FROM tableA INNLER JOIN tableB ORDER BY tableB.DisplayName

In SubSonic2 you can do that, and still be able to update your records if you use the DB.Select(...).ExecuteCollection() method. I think this should be possible with subsonic3, too.

Howevery, if you don't use the foreign key and the display name is unique, you should just use this value as your foreign key.

Answer 2

LINQ is your friend in this situation, you just need to join your two objects and then sort by the property from your foreign object:

var primaryObjectsSorted =
  from primaryObjects in PrimaryObject.All()
  join foreignObjects in ForeignObject.All() 
    on primaryObjects.ForeignId equals foreignObjects.Id
  orderby foreignObjects.PropertyYouWantToSortOn
  select primaryObjects;