Npgsql LINQ creates SQL Server sql syntax

Question

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

I added a factory generator to the App.config project, as stated in 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 found that the factory is an instance of Npgsql.NpgsqlFactory (seems correct), and the connection is an instance of Npgsql.NpgsqlConnection. It all seems good. However, when I try to GetTable, the generated SQL syntax contains square brackets and other SQL Server syntax.

What may be missing?

Answer 1

Datacontext

DataContext is LinqToSql. LinqToSql is for SqlServer only.

Perhaps you wanted to use LinqToEntities and ObjectContext?

Answer 2

If you want to use LINQ to SQL for an RDBMS other than Microsoft SQL Server, you need a third-party build. 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 the db provider for the Entity Framework after the documentation in this Npgsql blog:

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