PetaPoco over multiple databases

Question

Don't ask why but there are four databases. One of which I have rights to modify the schema. Let's call it external. Again, it's a legacy deal but there are about 60 tables in one of the other three databases, called main. Each record in those tables has a field that links it to a record in a corresponding table in external.

PetaPoco will make quick work of a lot of the trouble. Tentatively, I've tried multiple Database.tt files to manipulate all four databases. Is there a better way?

1. Should I create synonyms or views in external that refer to the goods in the other databases? And then only use one Database.tt on external?
2. Is a combined POCO for the linked tables reasonable?

Answer 1

The Database.tt is only used to pre-generate some poco out of your schema. I can hardly believe you are going to leave it there without modification. Normally I would start there and change to make more reasonable linked (with property complex properties for linked tables)

As to linked table queries, as they must be executed in 1 query, thus you have to only keep connection to only 1 db, thus a linked table is necessary. But be ware of low performances. Cross database table joins can sometime be 10 times slower than local joins, depending on sqls. If you have nested select cross multiple db tables, better to make temp table to avoid performance issue.