SQLCE: How to join tables from different SQLCE databases programmatically in C#?

I keep tables on different .sdf files because it's easy to manage them, ie; back up only changed db file, etc, plus in future db size might bigger and there is -4GB limit-

I need to join the tables and this will be my first -possibly LINQ- attempt. I know there are tons of examples/documents but a simple example would be nice to start.

This is the query for MS SQL Server:

SELECT personID, personPin, personName, seenTime 
FROM db1.person开发者_如何学运维List
LEFT JOIN db2.personAttendances on personID = seenPersonID
ORDER BY seenTime DESC

I think LINQ will be the way to go as you're querying across 2 different contexts. LINQ joins are quite easy: http://msdn.microsoft.com/en-gb/vcsharp/ee908647

Something like:

var q = from c in db1Context.personList

        join p in db2Context.personAttendances on c.personID equals p.seenPersonID

        select new { Category = c, p.ProductName };

I don't think SqlCE supports linking at the Db (SQL) level.

That means you'll have to use Linq-to-Objects. The example query has no WHERE clause so you can simply load the entire tables into Lists. But when the datasets get bigger that may not be acceptable.