Changing the database from SQL Server to PostgreSQL in creating ASP.NET web application?

Question

I am currently using SQL Server for database and Dapper (ORM) for mapping relation to model classes. I have used multiple reads in Dapper so I am able to get multiple tables at one call to the database. But due to a certain circumstance, I have to change my database connection from SQL Server to PostgreSQL. In Postgresql, there are no options facilities for using the power of query multiple reads.

Is there any way to handle the below situation in Postgresql?

using (var multi = conn.QueryMultiple(query, param, commandType: CommandType.StoredProcedure))
{
    obj.InventoryItemOrder = multi.Read<InventoryItemOrder>()
                                  .FirstOrDefault();  //getting single object data (so firstordefault)
    obj.InventoryItemDataModel = multi.Read<InventoryItemDataModel>();    //list
}

Can I use this concept when dealing with PostgreSQL and Dapper in building an ASP.NET application?

Answer 1

I'm not familiar with Dapper, but from a quick look at the doc, it appears that QueryMutiple basically runs multiple statements in the same command (that is, separated by a semicolon) and then maps the results of each statement.

You can certainly do the first part of that with Postgres: combine multiple statements into one separated by a semicolon. They will both be run. However, I am not aware of anything within Postgres itself that will automatically return results of the statements separately. Depending on exactly how you are interfacing with Postgres and getting the results, you could perhaps create a custom method of mapping these results.

From a purely database perspective, you don't gain much, if any, performance advantage from the behavior described in QueryMultiple as long as you issue the separate queries on the same connection.

It's the connection startup and teardown that is expensive, and even Dapper would have to issue and map the results for each query, so there is no performance benefit there. Rather, it's essentially a matter of syntactic sugar.

It's not clear from a quick look at the doc if Dapper is compatible with Postgres. If it is, and its QueryMultiple call is supported there, then it's probably handling the mapping of the multiple statements within the ORM itself.

If Dapper does not support Postgres, however, I would recommend simply issuing the queries separately and handling their results separately.