SQL Server has been developed over a long time and is very very good at doing things like getting the two records out of 100 million that you want very quickly. I hope DLinq takes advantage of this - I mean, does it as Minh asks do a full scan of the table or does it parse your DLinq query in to T-SQL?
Object databases have been developed for the last 5 years and a few are .net open source projects. Some already handle indexing.
After all, you just inject queries. It still uses the SQL engine.