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

Question

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.personList
LEFT JOIN db2.personAttendances on personID = seenPersonID
ORDER BY seenTime DESC

LINQ is not a must. I can use datasets if possible. I couldn't find a code snippet yet. I think I'm gonna load queries from db to datasets then join datasets. — Nime Cloud, Aug 24 '11 at 07:10

score 2 · Accepted Answer · answered Aug 24 '11 at 06:56

2

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 };

answered Aug 24 '11 at 06:56

Doobi

4,844
1
22
17

Looks OK but did you test it? I'm not sure Linq (SQL|EF) can handle this. Something is trying to generate a (normally 1) SQL query from this. – H H Aug 24 '11 at 07:24
I've done something similar a few times, it's an in memory join, it does not generate a SQL join. (Technically more LINQ to Objects http://msdn.microsoft.com/en-us/library/bb397919.aspx) – Doobi Aug 24 '11 at 23:49

score 1 · Answer 2 · answered Aug 24 '11 at 06:58

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.

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

2 Answers2