SubSonic Dynamic Schema: SelectList

Question

Scenario

I am using SubSonic for my latest project. First things first; this project is constrained to using .net 3.0 and SubSonic has been GREAT. I love it.

However, I'm having some trouble with customization. The customer has requested that we use two SQL Server 2005 databases, one of which will replicate the other, minus some columns.

At first glance this seems like a no brainer. However, SubSonic grabs all the columns by default, causing SQLExceptions. I would like to elegantly limit the select list on a table by table basis.

For instance, the following code will do the trick, but I would rather not need to do so every time I new up a query:

Query q = Post.Query().WHERE(Post.Columns.PageId, page_id);
if(UsingReplicatedDB)
    q.SetSelectList(ReplicatedPostColumnList);
return q.ExecuteReader();

The Question

The above method bloats my code and does not work for the builtin FetchByX methods generated by SubSonic. Is there a way to elegantly set the default SelectList on a table by table basis?

Notes

I have tried removing columns from the Schema at runtime in my partial classes, but columns looking like ColNameColumn look for specific indices in the Columns collection so my plan was foiled.

I know, I know

Please no "You are an idiot, why would you do this?" answers. I understand there are most likely better ways and this seems a bit hacky, but this is the point I'm at. I need a solution not badgering.

Answer 1

I don't think there's an easy way to get this to work. You could create two SubSonic providers pointing to each DB, and then based on the situation, use the SubSonic objects to populate a separate model. But SubSonic doesn't work the way you want since it's tied so closely to the db schema.

Answer 2

You do have a pretty sticky situation here. Even if you do find a way to get your query to fetch the columns based on which database version you are using you now have to figure out which database you are using everywhere in your Bussiness Model or UI so that you don't reference columns which do not exist.

I would recommend using two SubSonic Providers and then having the Generated class each implement an Interface that contains Properties for the columns that are common to the two classes as well as the Subsonic Methods that you plan on using to interact with your Database such as Save();.

Sometimes when you want to customize something to work how you want it to work you need to write some custom code :)

Answer 3

Hi Brad - if you're open to using 3.0 (which is about to be released) I'd be happy to work with you on some TT template fixes that would do just this (ignore the rep columns). Ping me at myname at gmail - I've been wanting to do this :).

3.0 would also allow you to use Linq and our neq Query tool - but I'd want to be sure it won't break what you're doing.