Using PostgreSQL with Entity Framework

The most widespread O/R (object-relational) mapper for the .NET platform is the Entity Framework. It is most often used in combination with Microsoft SQL Server as database. But the architecture of the Entity Framework allows to use it with other databases as well. A popular and reliable is open-source SQL database is PostgreSQL. This article shows how to use a PostgreSQL database with the Entity Framework.

Installing the Data Provider

First you need an Entity Framework data provider for PostgreSQL. It is called Npgsql. You can install it via NuGet. If you use Entity Framework 6 the package is called EntityFramework6.Npgsql:

> Install-Package EntityFramework6.Npgsql

If you use Entity Framework Core for the new .NET Core platform, you have to install a different package:

> Install-Package Npgsql.EntityFrameworkCore.PostgreSQL

Configuring the Data Provider

The next step is to configure the data provider and the database connection string in the App.config file of your project, for example:

<configuration>
  <!-- ... -->

  <entityFramework>
    <providers>
      <provider invariantName="Npgsql"
         type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
    </providers>
  </entityFramework>

  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider"
           invariant="Npgsql"
           description="Data Provider for PostgreSQL"
           type="Npgsql.NpgsqlFactory, Npgsql"
           support="FF" />
    </DbProviderFactories>
  </system.data>

  <connectionStrings>
    <add name="AppDatabaseConnectionString"
         connectionString="Server=localhost;Database=postgres"
         providerName="Npgsql" />
  </connectionStrings>

</configuration>

Possible parameters in the connection string are Server, Port, Database, User Id and Password. Here’s an example connection string using all parameters:

Server=192.168.0.42;Port=5432;Database=mydatabase;User Id=postgres;Password=topsecret

The database context class

To use the configured database you create a database context class in the application code:

class AppDatabase : DbContext
{
  private readonly string schema;

  public AppDatabase(string schema)
    : base("AppDatabaseConnectionString")
  {
    this.schema = schema;
  }

  public DbSet<User> Users { get; set; }

  protected override void OnModelCreating(DbModelBuilder builder)
  {
    builder.HasDefaultSchema(this.schema);
    base.OnModelCreating(builder);
  }
}

The parameter to the super constructor call is the name of the configured connection string in App.config. In this example the method OnModelCreating is overridden to set the name of the used schema. Here the schema name is injected via constructor. For PostgreSQL the default schema is called “public”:

using (var db = new AppDatabase("public"))
{
  var admin = db.Users.First(user => user.UserName == "admin")
  // ...
}

The Entity Framework mapping of entity names and properties are case sensitive. To make the mapping work you have to preserve the case when creating the tables by putting the table and column names in double quotes:

create table public."Users" ("Id" bigserial primary key, "UserName" text not null);

With these basics you’re now set up to use PostgreSQL in combination with the Entity Framework.

 

Advertisements

Text editing tricks

Multiple cursors

Recently I was in a pair programming session, when I noticed that there were three cursors blinking in the editor window of the IDE. I initially assumed it was a bug in the IDE (which is not that uncommon), but it acutally turned out it was a feature. In IntelliJ based IDEs you can place multiple cursors in the editor window, start typing and the typed text gets inserted at all of these positions. To achieve this press Shift+Alt while clicking the mouse to position the cursor carets.

When I start using a new IDE I usually look up and memorize the shortcuts for refactoring operations like “rename” or “extract method” or other essential operations like “quick fix”.

But of course there are many other neat tricks for advanced text editing in most IDEs and programming editors. Here are some of them, in this case for IntelliJ based IDEs.

Rectangular selection

Rectangular selection

This one comes in handy when you have to select a column of text, for example a common prefix of keys in a properties file. I initially knew this type of selection from Vim, but many other programming editors allow it too. For a rectangular selection in IntelliJ based IDEs press Ctrl+Shift+Alt (Shift+Alt+Cmd on Mac) and drag the mouse pointer.

Multiselection

Similar to the multiple cursors feature mentioned at the beginning of this post, you can also select multiple parts of a text at once and then cut, copy or delete them. To achieve this multi-selection press Shift+Alt while selecting text.

Multiple selction

Extending selection

By pressing Ctrl+W repeatedly within a fragment of code the selection will progressively extend. First the current expression under the cursor is selected, then the surrounding code block, then the code block surrounding this code block, etc.

Extending selection

Conclusion

Take some time and look up some of the more advanced text editing capabilities of your IDE or text editor. Adopt them if you find them helpful and share them with your colleagues, for example in a pair programming session.

Kotlin and null-safety

This week I installed the Android Studio 3.0 preview in preparation for the development of an Android tablet app. Android Studio is based on JetBrains’ IntelliJ IDE. Google recently announced that Android Studio will support Kotlin as an official programming language for Android starting with version 3.0. The language has been designed and developed by JetBrains since 2010.

Kotlin is a language for the Java ecosystem like Scala, Groovy or Clojure that targets both the JVM and Google’s Dalvik VM, which is used for Android. It’s a statically typed language and it has a similar feature set as Scala and C#. Compared to Java it adds things like operator overloading, short syntax for properties, type inference, extension functions, string templates and it supported lambda expressions since before Java 8. But it also fixes some of Java’s inconsistencies. For example, it provides a unified type system with the Any type at the top of the type hierarchy and without special raw types. Arrays in Kotlin are invariant, and it uses declaration-site variance instead of use-site variance (see my other blog post for an explanation of these terms: Declaration-site and use-site variance explained). However, in my opinion the most interesting of Kotlin’s features is null-safety.

Null-safety

In Kotlin all types are non-nullable by default. You can’t assign null to a variable declared as

var s: String = "hi";

If you really want to be able to assign null to a variable you have to declare it with a question mark after the type, for example

var s: String? = null;

However, if you want to access a member of nullable reference or call a method on it, you have to perform a null check before doing so. This is enforced by the compiler. 

if (s != null) {
    return s.toUpperCase();
}

The compiler keeps track of the null checks before accessing a member of a nullable reference. Without the check the code wouldn’t compile. Kotlin offers some additional operators to simplify these null checks, like the safe navigation operator ?. (also known from Groovy and C# 6.0) or the “Elvis” operator ?:

person?.address?.country?.name
s?.toUpperCase() ?: ""

With Kotlin’s null-safety feature NullPointerExceptions are a thing of the past.

Kotlin has a lot more to offer and we haven’t decided yet if we will use it for our new Android app project, but it’s definitely an option to consider.

Monitoring long running operations in Oracle databases

We regularly work with database tables with hundreds of millions of entries. Some operations on these table can take a while. Not necessarily queries, but operations in preparation to make queries fast, for example the creation of materialized views or indexes.

The problem with most SQL tools is: once you run your SQL statement you have no indication of how long it will take to complete the operation. No progress bar and no display of the remaining time. Will it take minutes or hours?

Oracle databases have a nice feature I learned about recently that can answer these questions. Operations that take longer than 6 seconds to complete are considered “long operations” and get an entry in a special view called V$SESSION_LONGOPS.

This view does not only contain the currently running long operations but also the history of completed long operations. You can query the status of the current long operations like this:

SELECT * FROM V$SESSION_LONGOPS 
  WHERE time_remaining > 0;

This view contains columns like

  • TARGET (table or view on which the operation is carried out)
  • SOFAR (units of work done so far)
  • TOTALWORK (total units of work)
  • ELAPSED_SECONDS (number of elapsed seconds from the start of the operation)

Based on these values the view offers another column, which contains the estimated remaining time in seconds: TIME_REMAINING.

This remaining time is really just an estimate, because it assumes long running operations to be linear, which is not necessarily true. Also some SQL statements can spawn multiple consecutive operations, e.g. first a “Table Scan” operation and then a “Sort Output” operation, which will only become visible after the first operation has finished. Nevertheless I found this feature quite helpful to get a rough idea of how long I will have to wait or to inform decisions such as whether I really want to perform an operation until completion or if I want to cancel it.

Analyzing iOS crash dumps with Xcode

The best way to analyze a crash in an iOS app is if you can reproduce it directly in the iOS simulator in debug mode or on a local device connected to Xcode. Sometimes you have to analyze a crash that happened on a device that you do not have direct access to. Maybe the crash was discovered by a tester who is located in a remote place. In this case the tester must transfer the crash information to the developer and the developer has to import it in Xcode. The iOS and Xcode functionalities for this workflow are a bit hidden, so that the following step-by-step guide can help.

Finding the crash dumps

iOS stores crash dumps for every crash that occured. You can find them in the Settings app in the deeply nested menu hierarchy under Privacy -> Analytics -> Analytics Data.

There you can select the crash dump. If you tap on a crash dump you can see its contents in a JSON format. You can select this text and send it to the developer. Unfortunately there is no “Select all” option, you have to select it manually. It can be quite long because it contains the stack traces of all the threads of the app.

Importing the crash dump in Xcode

To import the crash dump in Xcode you must save it first in a file with the file name extension “.crash”. Then you open the Devices dialog in Xcode via the Window menu:

To import the crash dump you must have at least one device connected to your Mac, otherwise you will find that you can’t proceed to the next step. It can be any iOS device. Select the device to open the device information panel:

Here you find the “View Device Logs” button to open the following Device Logs dialog:

To import the crash dump into this dialog select the “All Logs” tab and drag & drop the “.crash” file into the panel on the left in the dialog.

Initially the stack traces in the crash dump only contain memory addresses as hexadecimal numbers. To resolve these addresses to human readable symbols of the code you have to “re-symbolicate” the log. This functionality is hidden in the context menu of the crash dump:

Now you’re good to go and you should finally be able to find the cause of the crash.

Internationalization of a React application with react-intl

For the internationalization of a React application I have recently used the seemingly popular react-intl package by Yahoo.

The basic usage is simple. To resolve a message use the FormattedMessage tag in the render method of a React component:

import {FormattedMessage} from "react-intl";

class Greeting extends React.Component {
  render() {
    return (
      <div>
        <FormattedMessage id="greeting.message"
            defaultMessage={"Hello, world!"}/>
      </div>
    );
  }
}

Injecting the “intl” property

If you have a text in your application that can’t be simply resolved with a FormattedMessage tag, because you need it as a string variable in your code, you have to inject the intl property into your React component and then resolve the message via the formatMessage method on the intl property.

To inject this property you have to wrap the component class via the injectIntl() function and then re-assign the wrapped class to the original class identifier:

import {intlShape, injectIntl} from "react-intl";

class SearchField extends React.Component {
  render() {
    const intl = this.props.intl;
    const placeholder = intl.formatMessage({
        id: "search.field.placeholder",
        defaultMessage: "Search"
      });
    return (<input type="search" name="query"
               placeholder={placeholder}/>);
  }
}
SearchField.propTypes = {
    intl: intlShape.isRequired
};
SearchField = injectIntl(SearchField);

Preserving references to components

In one of the components I had captured a reference to a child component with the React ref attribute:

ref={(component) => this.searchInput = component}

After wrapping the parent component class via injectIntl() as described above in order to internationalize it, the internal reference stopped working. It took me a while to figure out how to fix it, since it’s not directly mentioned in the documentation. You have to pass the “withRef: true” option to the injectIntl() call:

SearchForm = injectIntl(SearchForm, {withRef: true});

Here’s a complete example:

import {intlShape, injectIntl} from "react-intl";

class SearchForm extends React.Component {
  render() {
    const intl = this.props.intl;
    const placeholder = intl.formatMessage({
        id: "search.field.placeholder",
        defaultMessage: "Search"
      });
    return (
      <form>
        <input type="search" name="query"
               placeholder={placeholder}
               ref={(c) => this.searchInput = c}/>
      </form>
    );
  }
}
SearchForm.propTypes = {
  intl: intlShape.isRequired
};
SearchForm = injectIntl(SearchForm,
                        {withRef: true});

Conclusion

Although react-intl appears to be one of the more mature internationalization packages for React, the overall experience isn’t too great. Unfortunately, you have to litter the code of your components with dependency injection boilerplate code, and the documentation is lacking.

Platform independent development with .NET

We develop most of our projects as platform independent applications, usually running under Windows, Mac and Linux. There are exceptions, for example when it is required to communicate with special hardware drivers or third-party libraries or other components that are not available on all platforms. But even then we isolate these parts into interchangeable modules that can be operated either in a simulated mode or with the real thing. The simulated modes are platform independent. Developers usually can work on the code base using their favorite operating system. Of course, it has to be tested on the target platform(s) that the application will run on in the end.

Platform independent development is both a matter of technology choices and programming practices. Concerning the technology the ecosystem based on the Java VM is a proven choice for platform independent development. We have developed many projects in Java and other JVM based languages. All of our developers are polyglots and we are able to develop software with a wide variety of programming languages.

The .NET ecosystem

Until recently the .NET platform has been known to be mainly a Microsoft Windows based ecosystem. The Mono project was started by non-Microsoft developers to provide an open source implementation of .NET for other operating systems, but it never had the same status as Microsoft’s official .NET on Windows.

However, recently Microsoft has changed course: They open sourced their .NET implementation and are porting it to other platforms. They acquired Xamarin, the company behind the Mono project, and they are releasing developer tools such as IDEs for non-Windows platforms.

IDEs for non-Windows platforms

If you want to develop a .NET project on a platform other than Windows you now have several choices for an IDE:

I am currently using JetBrains Rider on a Mac to develop a .NET based application in C#. Since I have used other JetBrains products before it feels very familiar. Xamarin Studio, MonoDevelop, VS for Mac and JetBrains Rider all support the solution and project file format of the original Visual Studio for Windows. This means a .NET project can be developed with any of these IDEs.

Web applications

The .NET application I am developing is based on Web technologies. The server side uses the NancyFX web framework, the client side uses React. Persistence is done with Microsoft’s Entity Framework. All the libraries I need for the project like NancyFX, the Entity Framework, a PostgreSQL driver, JSON.NET, NLog, NUnit, etc. work on non-Windows platforms without any problems.

Conclusion

Development of .NET applications is no longer limited to the Windows platform. Microsoft is actively opening up their development platform for other operating systems.