Linq To Sql with PostgreSQL

.net linq linq-to-sql postgresql mono
Chernikov picture Chernikov · Jan 31, 2011 · Viewed 22.1k times · Source

Is it possible to use LinqToSql with PostgreSQL (preferably with Mono)? Can you recommend any articles where it described step-by-step?

Answer

CedX picture CedX · Dec 7, 2011

I use LINQ to SQL since about 2 years with MySQL and PostgreSQL databases (using DbLinq on Windows, using Mono on Linux and Mac OS X).

So LINQ to SQL is NOT limited to SQL Server, but as stated above, you need to use external libraries.

On Mono, DbLinq is the foundation of the System.Data.Linq namespace: you don't need to deploy/register DbLinq assemblies. Idem for the Npgsql assembly: it is already provided by the latest versions of Mono.

You should be aware that the support is incomplete (I had numerous problems when I tried to bind an ObjectDataSource control to a PostgreSQL data context: I solved them by doing manual data binding with the generated entities). But this is enough for most SQL queries (I've almost never had any failed queries : complex joints can be simulated by several simpler queries).

There is little difference between DbLinq and LINQ to SQL. Only the connection string must be customized by supplying informations about the data provider. See these short samples (I never found any real tutorial about the use of DbLinq):

http://www.mono-project.com/Release_Notes_Mono_2.6
http://www.jprl.com/Blog/archive/development/mono/2009/Mar-12.html

Personally, with Mono 2.10.6, I use this type of connection string : "Server=localhost; Database=MyDB; User Id=postgres; Password=MyPassword; DbLinqProvider=PostgreSql; DbLinqConnectionType=Npgsql.NpgsqlConnection, Npgsql, Version=4.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"

Note: on Mono, the DbMetal tool is replaced by the "sqlmetal" command, which has the same parameters (it's just a wrapper around DbMetal).

Related questions

LINQ: When to use SingleOrDefault vs. FirstOrDefault() with filtering criteria

Consider the IEnumerable extension methods SingleOrDefault() and FirstOrDefault() MSDN documents that SingleOrDefault: Returns the only element of a sequence, or a default value if the sequence is empty; this method throws an exception if there is more than one element …

Read More
LINQ - Left Join, Group By, and Count

Let's say I have this SQL: SELECT p.ParentId, COUNT(c.ChildId) FROM ParentTable p LEFT OUTER JOIN ChildTable c ON p.ParentId = c.ChildParentId GROUP BY p.ParentId How can I translate this into LINQ to SQL? I got …

Read More
How to select only the records with the highest date in LINQ

I have a table, 'lasttraces', with the following fields. Id, AccountId, Version, DownloadNo, Date The data looks like this: 28092|15240000|1.0.7.1782|2009040004731|2009-01-20 13:10:22.000 28094|61615000|1.0.7.1782|2009040007696|2009-01-20 13:11:38.000 28095|95317000|1.0.7.1782|2009040007695|2009-01-20 13:10:18.000 28101|15240000|1.0.7.1782|2009040004740|2009-01-20 14:10:22.000 28103|61615000|1.0.7.1782|2009040007690|2009-01-20 14:11:38.000 28104|95317000|1.0.7.1782|2009040007710|2009-01-20 14:10:18.000 How can I, in LINQ to SQL, only get …

Read More