views:

137

answers:

3

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.

+2  A: 

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.

See also http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Robert Harvey
There isn't any indication on the DBLinq page, whether it is stable, last release is one year old (although the SVN was last updated yesterday). If there isn't anthing better, I will try this.
svick
A: 

NHibernate supports multiple databases and has a recently-released Linq provider.

dahlbyk
Their LINQ provider seems to be quite problematic, they are saying that version 2 shoud be much better.
svick
+1  A: 

Check out ADO.NET Entity Framework. It supports MySQL via ADO.NET data providers and LINQ to Entities (similar to LINQ to SQL). Being a Microsoft tool, it has good Visual Studio integration and support.

Can you also explain why you want to support these databases?

Ronald
You didn't answer my question: do those providers support dynamic SQL generation?I want to use other databases, because many of the free ASP.NET hosts (yes, I don't want to pay for my personal web projects) run on Linux and Mono with MySQL as DB.
svick
Looking at http://stackoverflow.com/questions/42212/how-can-i-use-linq-with-a-mysql-database-on-mono and the documentation (http://dev.mysql.com/doc/refman/5.0/en/connector-net.html) it seems the MySQL Connector/NET has LINQ and Mono support. The Entity Framework though isn't supported on Mono (yet, see http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx)...
Ronald
The last link should be: http://www.mono-project.com/EntityFramework
Ronald
EF will do what you ask in the question (convert Count() to SELECT COUNT(*), yes.
Craig Stuntz