I am successfully "querying" data in a Sql Server CE database using the TableDirect CommandType thanks to a tip from ctacke. (The performance increase is very real.) I am able to query using Index Names well.
(I am creating the column via "Id int IDENTITY(1,1) PRIMARY KEY". I see the crazy index name that Sql Server CE generates, but I'm not sure the best way to "use" it.)
Also, I saw this note in msdn docs (http://technet.microsoft.com/en-us/library/ms173393%28SQL.100%29.aspx):
"SQL Server Compact 3.5 can use indexes to enforce PRIMARY KEY and UNIQUE constraints. We recommend that you do not rely on this behavior nor try to modify any indexes that are created as part of a constraint."
What is the best way to retrieve items (for Update) based on the Primary Key?
UPDATE:
I found that I can give a custom name to the Primary Key like this:
CREATE TABLE TEST2 (Id int CONSTRAINT PKID PRIMARY KEY)
So I could then use PKID as the Index Name ... why would I not be able to "rely on this behavior"?