Combining SQL results using LINQ

Question

I have a database of company registrants (for a search/directory funtionality). We've added a new table to hold "enhanced" information for registrants that pay for this feature (such as advertisements/additional images/logos etc). Right now the new table just holds the registrants unique identifier, and a few additional fields (paths to images etc). A user can search for users with specific criteria, and the enhanced listings should appear at the top of the list. The results should not show any registrant twice (so if a user has an enhanced listing they should only appear in the top "enhanced listing" area). How can I accomplish this?

Answer 1

Left outer join from the old table to the new table.

Prepend to your query's "order by" "case when new_table.id is null then 1 else 0 end"

So if you had this:

select foo, bar from old_table 
order by bar, foo;

You'd have this:

select a.foo, a.bar from old_table a 
 left join new table b on (a.customer_id = b.customer_id) 
order by 
 case when new_table.customer_id is null then 1 else 0 end, 
 bar, foo;

Edit: I left out the "left" from the outer join in the code.

Answer 2

If you are using LINQtoSQL and the designer-generated entities, you should have an entity set of related information on your registrant entity -- assuming you have set up the proper foreign key relationship. If you added this later you may need to add this by hand (see here) or delete/re-add your entities to the designer for it to pick up the new relationship. Then your query would be something like:

var registrants = db.Registrants.Where( ... selection criteria here ... );

registrants = registrants.OrderByDescending( r => r.EnhancedData.Count() )
                         .ThenBy( r => r.Name );  // or normal sort order

Presumably count will be either 0 or 1 so this should put the ones with enhanced data at the top of your result.