npgsql LINQ creates SQL Server sql syntax

Question

I'm trying to use LINQ with Npgsql 2.0.11 in a .NET v3.5 project. I'm trying my first simple query from a data table, and I've found that the syntax sent to Postgresql is SQL Server syntax, not Pgsql syntax, causing the server to throw a syntax error.

I have added the factory generation to the project's App.config as suggested by the documentation: <system.data>

<DbProviderFactories>

<add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>

</DbProviderFactories>

</system.data>

Here is a snippet:

DbProviderFactory factory = DbProviderFactories.GetFactory("Npgsql");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = "Server=mydbhost.example.com;UserId=postgres;Database=postgres";
table = new DataContext(connection).GetTable<Project.Model.MyEntity>();

I've found that factory is an instance of Npgsql.NpgsqlFactory (seems right), and connection is an instance of Npgsql.NpgsqlConnection. All that seems good. However, when I attempt to GetTable, the SQL syntax generated contains square brackets and various other SQL Server specific syntax.

What could be missing?

Amy B · Accepted Answer · 2011-01-25 02:47:43Z

3

DataContext

DataContext is LinqToSql. LinqToSql is for SqlServer only.

Perhaps you meant to use LinqToEntities and ObjectContext?

answered Jan 25, 2011 at 2:47

Amy B

110k21 gold badges140 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Moorhouse · Accepted Answer · 2011-02-07 16:56:47Z

0

If one wants to use LINQ to SQL for RDBMS other than Microsoft SQL Server, a third-party assembly is required. DBLinq is a project that provides LINQ to SQL functionality for other (open-source) databases.

http://code.google.com/p/dblinq2007/

Npgsql can be configured as a db provider for Entity Framework following the documentation on this Npgsql blog:

http://npgsql.com/index.php/2009/08/how-to-set-up-entity-framework-npgsql-part-1/

edited Feb 7, 2011 at 16:56

answered Jan 25, 2011 at 18:02

Scott Moorhouse

2393 silver badges9 bronze badges

Collectives™ on Stack Overflow

npgsql LINQ creates SQL Server sql syntax

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related