Amazon Redshift support

[tru3d3v]

Hi @roji ,

I trying connect this EF to AWS Redshift for the getting data i don't have any issue, but somehow when i trying to inserting the data i have an error like below:

{Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "RETURNING"
   at Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadExpecting>d__154`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlDataReader.<NextResult>d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<Execute>d__71.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__92.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)}

================================================
this below is my Startup.cs

namespace WebApplication1
{
    public class User 
    {
        public int ID { get; set; }
        public string UserName { get; set; }
        public string FullName { get; set; }
        public string Password { set; get; }
        public DateTime CreatedDate { set; get; }
        public DateTime ModifiedDate { set; get; }
        public int CreatedBy { set; get; }
        public int ModifiedBy { set; get; }
    }
    public class BlogContext : DbContext
    {
        public BlogContext(DbContextOptions<BlogContext> options) : base(options) { }

        public DbSet<User> Users { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>().ToTable("TUser");
        }
    }

    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {

             var connectionString = Configuration.GetConnectionString("BlogContext");
             services.AddEntityFrameworkNpgsql().AddDbContext<BlogContext>(options => options.UseNpgsql(connectionString));

            services.AddMvc();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseMvc();
        }
    }
}
==========================
and this is my controller file
namespace WebApplication1.Controllers
{
    [Route("api/[controller]")]
    public class ValuesController : Controller
    {
        BlogContext db;
        public ValuesController(BlogContext db)
        {
            this.db = db;        
        }
        // GET api/values
        [HttpGet]
        public IEnumerable<string> Get()
        {
            return this.db.Users.Select(a => a.FullName).ToArray();
           // return new string[] { "value1", "value2" };
        }

        // GET api/values/5
        [HttpGet("{id}")]
        public string Get(int id)
        {
            return "value";
        }

        // POST api/values
        [HttpPost]
        public IEnumerable<string> Post([FromBody]string value)
        {

            this.db.Users.Add(new WebApplication1.User { FullName=value });
            this.db.SaveChanges();
            return this.db.Users.Select(a => a.FullName).ToArray();

        }

        // PUT api/values/5
        [HttpPut("{id}")]
        public void Put(int id, [FromBody]string value)
        {
        }

        // DELETE api/values/5
        [HttpDelete("{id}")]
        public void Delete(int id)
        {
        }
    }
}

Would appreciate if you can help me :)

Thank you.

[roji]

Unfortunately Redshift isn't 100% compatible with PostgreSQL, notably on the query syntax returning an auto-generated column (RETURNING). You should be able to get away with some scenarios, e.g. querying existing data, always set IDs yourself (i.e. no database-generated values).

We can add support for Redshift via some sort of UseRedshift() on the context options - this would be picked up by the SQL generator and we'd use some other means of retrieving the auto-generated ID. Will keep this issue open to track that.

This could be a good PR for someone who wants to contribute.

[tru3d3v]

Thank Roji for your good explanation :)

[cbertolasio]

@tru3d3v - I am curious how you have your connection string setup. I have been unable to open my connection when I structure my connection string as follows:

"Host=my-redshift-hostname.com;Port=5439;Database=myDatabaseName;Username=myUserName;Password=myPassword;Server Compatibility Mode=Redshift"

At this point I am unable to use Npgsql.EntityFrameworkCore.PostgreSQL, 2.1.0-rc1 in an asp.net core 2.1 app to read from redshift.

[roji]

@cbertolasio what error are you getting?

[cbertolasio]

@roji
I have been attempting 3 different scenarios to connect to redshift, all result in the same error when the connection is being opened.

Installed Packages:

• Microsoft.EntityFrameworkCore 2.1.0-rc1-final
• Microsoft.EntityFrameworkCore.Design 2.1.0-rc1-final
• Microsoft.EntityFrameworkCore.Tools 2.1.0-rc1-final
• Npgsql.EntityFrameworkCore.PostgreSQL 2.1.0-rc1
• Npgsql.EntityFrameworkCore.PostgreSQL.Design 2.0.0-preview1

Statements I have tried:

1. linq to entity: var events = from e in dbContext.IrrigationEvents where e.JournalId == 123 select
2. FromSql method: var events = dbContext.IrrigationEvents.FromSql(mySqlString);
3. execute reader:

var cn = dbContext.Database.GetDbConnection();
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = mySqlString;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
	Console.WriteLine(reader[0]);
}

The Error & Stack:

cn.Open()
'cn.Open()' threw an exception of type 'Npgsql.PostgresException'
Code: "42P01"
ColumnName: null
ConstraintName: null
Data: Count = 7
DataTypeName: null
Detail: null
ErrorCode: -2147467259
File: "/home/ec2-user/padb/src/pg/src/backend/catalog/namespace.c"
HResult: -2147467259
HelpLink: null
Hint: null
InnerException: null
InternalPosition: 0
InternalQuery: null
IsTransient: false
Line: "239"
Message: "42P01: relation "pg_enum" does not exist"
MessageText: "relation "pg_enum" does not exist"
Position: 0
Routine: "RangeVarGetRelid"
SchemaName: null
Severity: "ERROR"
Source: "Npgsql"
SqlState: "42P01"
StackTrace: " at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444\r\n at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332\r\n at Npgsql.PostgresDatabaseInfo.LoadBackendTypes(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async) in C:\projects\npgsql\src\Npgsql\PostgresDatabaseInfo.cs:line 251\r\n at Npgsql.PostgresDatabaseInfo.LoadPostgresInfo(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async) in C:\projects\
npgsql\src\Npgsql\PostgresDatabaseInfo.cs:line 72\r\n at Npgsql.PostgresDatabaseInfoFactory.Load(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async) in C:\projects\npgsql\src\Npgsql\PostgresDatabaseInfo.cs:line 43\r\n at Npgsql.NpgsqlDatabaseInfo.Load(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async) in C:\projects\npgsql\src\Npgsql\NpgsqlDatabaseInfo.cs:line 246\r\n at Npgsql.NpgsqlConnector.LoadDatabaseInfo(NpgsqlTimeout timeout, Boolean async) in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 466\r\n at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 438\r\n at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\ConnectorPool.cs:line 246\r\n at Npgsql.NpgsqlConnection.<>c__DisplayClass35_0.<g__OpenLong|0>d.MoveNext
() in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:line 300\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at Npgsql.NpgsqlConnection.Open() in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:line 155"
Statement: {/*** Load enum fields ***/
SELECT pg_type.oid, enumlabel
FROM pg_enum
JOIN pg_type ON pg_type.oid=enumtypid
ORDER BY oid, enumsortorder}
TableName: null
TargetSite: {Void MoveNext()}
Where: null

[tru3d3v]

Hi @cbertolasio

appsettings.json :

"ConnectionStrings": {
"DefaultConnection": "Server=yourhostname:yourport;Database=dbname;Password=pwd;UserId=uid;"
},

[roji]

@cbertolasio your issue has already been fixed (npgsql/npgsql#1927) but not yet released. If you encounter further problems please open a new issue in the Npgsql repo, this issue is about adding Redshift query support in EF Core.

[austindrenski]

For anyone interested in working on this:

We just merged #483 to add PostgresVersion to INpgsqlOptions which can be used to make method translation/SQL generation conditional on the version of PosgreSQL being targeted.

#483 also contains discussion about an enum for BackendType which could be used to specify the type of server (e.g. PostgreSQL, Redshift, etc.) and used to conditionally branch within the SQL generator for cases like RETURNING.

[roji]

Note: 6.0.0 adds an explicit Redshift compatibility mode at the EF level (see #1802). For now, this prevents generation of the x = ANY(@param) construct - which isn't supported by Redshift - but we could make that flag affect other SQL generation as well.

Am closing this - for any additional Redshift compatibility problems, please open specific issues on the problem.