AWS RedShift - .NET Core (ODBC Support?)

Question

How can I connect and run queries against AWS RedShift using .NET Core? Code sample please. I have gone through the AWS docs and .Net Core docs but no luck.

Answer 1

This answer is one for a particular point in time and won't age well...

The EntityFramework Core project is the one I'd keep the closest eye on. The lack of ODBC is well known, especially for those who want to connect to Oracle. For the time being, you may need to fork an Oracle client for .NET core and make modifications as necessary.

I found these projects after a quick Google search that may be able to help you for now...

- https://github.com/LinqDan/oracleclientcore - https://github.com/LinqDan/Mono.Data.OdbcCore

Longer term, you'll want to keep an eye on these two GitHub issues which are tracking it for EntityFramework Core and the .NETStandard APIs..

• https://github.com/dotnet/corefx/issues/13035
• https://github.com/aspnet/EntityFramework/issues/7432

Update 6/23/2017:

This is now possible through the Npgsql.EntityFrameworkCore.PostgreSQL NuGet package and associated Entity Framework Core packages. Apparently the PostgreSQL team was tired of waiting around for ODBC support (which still isn't available in the latest netstandard2.0 yet) and wrote their own driver using netstandard - back in the November timeframe. The getting started page on the npgsql website covers it's usage in the old JSON project format - but the dependencies listed are still valid.

Here is how you would use the package...

using (var conn = new NpgsqlConnection("Host=myserver;Username=mylogin;Password=******;Database=music"))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand())
    {
        cmd.Connection = conn;

        cmd.CommandText = "SELECT name FROM artists";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader.GetString(0));
            }
        }
    }
}

One thing to keep in mind when using this driver - it's written for PostgreSQL, not Redshift. While Redshift is based on PostgreSQL, it's underlying engine is much more like Cassandra than anything else. As a result, Amazon had to make some choices in the development to drop certain things that PostgreSQL does support - such as SQL variables. Because of this, you will have a fairly limiting experience for certain things that you might be used to in other Entity Framework implementations. As long as you stay with using the direct access *Connection, *Command, and DataReader classes and write your own SQL, you should be fine.