LINQ to PostgreSQL errors when using DbLINQ

Question

I'm using LINQ to SQL (PostgreSQL) via DbLINQ.

I have a problem doing LINQ to PostgreSQL. I succesfully generated .dbml and .cs files with dbmetal and I think I got all the references, the code compiles. Refs:

DbLinq
DbLinq.PostgreSql
DbLinq.SqlServer
Npgsql

using DbLinq.PostgreSql;

I get an exception:

{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}

inner exception:

{"The system cannot find the path specified"}

connection string:

server=127.0.0.1;database=xxxxx;user id=postgres;password=xxxxx;
ili
server=localhost;database=xxxxx;user id=postgres;password=xxxxx;

If I try to use NpgsqlConnectionStringBuilder I get:

HOST=localhost;PORT=5432;PROTOCOL=3;DATABASE=xxxxx;USER ID=postgres;PASSWORD=xxxxx;SSL=False;SSLMODE=Disable;TIMEOUT=15;SEARCHPATH=;POOLING=True;CONNECTIONLIFETIME=15;MINPOOLSIZE=1;MAXPOOLSIZE=20;SYNCNOTIFICATION=False;COMMANDTIMEOUT=20;ENLIST=False;PRELOADREADER=False;USEEXTENDEDTYPES=False;INTEGRATED SECURITY=False;COMPATIBLE=2.0.12.0;APPLICATIONNAME=

with an exception {"Keyword not supported: 'host'."}

I connect succesfully over SquirrelSQL and jdbc driver on Win7 64-bit, Postgres 9.2 64-bit

Edit: this is fine

xxxxxDC dc = new xxxxxDC("server=127.0.0.1;database=xxxxx;user id=postgres;password=xxxxx;DbLinqProvider=PostgreSql;");
         var q = from r in dc.xxxxx
                 select r;

but i get an error on

dataGridView1.DataSource = q

Now i know this is because the query is not executed immediately. But the problem remains. "The server was not found or was not accessible"

F1!

lp

Answer 1

I think postgresql uses a different default port than 1433. Try specifying the port 5432

Answer 2

You're showing a series of quite different errors.

The first one is because you haven't told LINQ to use nPgSQL, so it's trying to use MS SQL Server and it cannot connect - since there probably isn't any MS SQL Server on the machine. That's why the error says while establishing a connection to SQL Server.

You then show an nPgSQL connection string from NpgsqlConnectionStringBuilder and the resulting error about the host keyword. As best I can guess, that's because the connection string produced by that class is intended for nPgSQL's own connection handling routines, not for LINQ. You need a LINQ connection string that specifies the PostgreSQL provider.

You then show another connection string you say is "fine" that has DbLinqProvider=PostgreSql; appended to it, but say you get "an error" on a statement after that. You do not show the error message, nor do you show the code you used to set the connection up, so we cannot really help you. It's really a different question to what you originally asked anyway; please post a new question for a new problem, rather than rewriting your original question.

You need a LINQ provider for PostgreSQL. See this question and the wikipedia page on LINQ. Look at dbLinq, LINQ to Entities with a PostgreSQL driver for Entity Framework, or dotConnect.

At time of writing, nPgSQL does not include a LINQ provider so you need to add a 3rd party one. AFAIK you cannot simply use LINQ with nPgSQL directly.

Answer 3

It worked after I used ConnectionStringBuilder class to make the connectionstring. Thanks for your help!

Answer 4

FYI, DbLinq project is dead ) In my case it didn't work right away. Your schema may work now, but later you'll likely have to invest your time to update the project )