Are there any free (gratis) providers for databases other MS SQL (e.g. MySQL or SQLite) that work with LINQ and support dynamic SQL query generation? E.g.
table.Count() generates something like
SELECT COUNT(*) FROM table and doesn't first load the whole table and then count the rows.
Here is the DBLinq project: http://code.google.com/p/dblinq2007/
DbLinq is THE LINQ provider that allows to use common databases with an API close to Linq to SQL. It currently supports (by order of appearance): MySQL, Oracle, PostgreSQL, SQLite, Ingres, Firebird
Whether these providers execute
Count() in the way you describe depends on the quality of the provider, I suppose. Presumably some effort is made at optimization.