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

score 2 · Answer 1 · edited May 23 '17 at 11:43

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.

I am sorry if I was unclear. I am using (or want to use) DBlinq as provider. I have used program DBmetal to generate .dbml and .cs files. I suspected its trying to connect to "SQL server" but I have no idea what i've done wrong, I just added all the references I was told by the compiler. — user1941235, Jan 02 '13 at 07:33

score 0 · Answer 2 · answered Jan 01 '13 at 19:16

0

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

answered Jan 01 '13 at 19:16

Michael Christensen

1,768
1
13
11

I tried that, no luck. I don't know what is the problem. How can I even test the connection string? – user1941235 Jan 01 '13 at 19:21
How did you try it? If the code generator ran it must have connected to a limited degree – Michael Christensen Jan 01 '13 at 19:47
I tried modifying connection string to server=127.0.0.1:5432;database=xxxxx;user id=postgres;password=xxxxx server=localhost:5432;database=xxxxx;user id=postgres;password=xxxxx server=127.0.0.1;port=5432;database=xxxxx;user id=postgres;password=xxxxx The last one doesnt work (unsupported keyword 'port') – user1941235 Jan 01 '13 at 22:26
While it's true that PostgreSQL uses port 5432 by default, the underlying assumption in this answer is totally wrong. PostgreSQL doesn't support the MS SQL Server network protocol, so you can't just connect to it as if it were MS SQL Server. If the PostgreSQL provider nPgSQL were being used, it'd default to the PostgreSQL default port 5432, not the MS SQL Server default port. So the real problem is that the wrong LINQ provider is being used. – Craig Ringer Jan 01 '13 at 23:41
Indeed, theres also alot more information in the question than originally – Michael Christensen Jan 01 '13 at 23:46
Still not enough information though; I've closevoted as it's a rambling and incomplete story more than it is an actual question. I didn't downvote because it's pretty clear you're trying to take a stab at answering a garbled monstrosity of a question. – Craig Ringer Jan 01 '13 at 23:56

score 0 · Answer 3 · answered Jan 02 '13 at 22:32

0

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

answered Jan 02 '13 at 22:32

user1941235

113
1
10

score 0 · Answer 4 · answered Jan 08 '13 at 20:05

0

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 )

answered Jan 08 '13 at 20:05

Timur Sadykov

10,859
7
32
45

LINQ to PostgreSQL errors when using DbLINQ

4 Answers4