title: Can Dapper pass a user-defined composite type to a PostgreSQL function?
body: <p>I'm trying to figure out how to use Dapper to pass in a user-defined composite type to a PostgreSQL function. I know this is possible with SQL Server and I've already got working examples using Dapper+SQL Server, however, I'm coming up short on how to do the same with PostgreSQL. </p>
  
  <p>From some of the things I've read, I'm not sure if this is even possible with Dapper+PostgreSQL, but I know that it does work with plain Npgsql (and I've got a working example of that as well). </p>
  
  <p>So, <strong>how does one call a PostgreSQL function that takes in a user-defined composite type using Dapper?</strong></p>
  
  <h2>Example user-defined composite type</h2>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TYPE hero AS (
      first_name text,
      last_name text
  );
  </code></pre>
  
  <h2>Example PostgreSQL function that takes in the user-defined composite type</h2>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION testfuncthattakesinudt(our_hero hero)
      RETURNS SETOF characters 
      LANGUAGE 'sql'
  
      STABLE
      ROWS 1000
  AS $BODY$
  
      SELECT  *
      FROM    characters
      WHERE   first_name = COALESCE(our_hero.first_name, '')
      AND     last_name = COALESCE(our_hero.last_name, '');
  
  $BODY$;
  </code></pre>
  
  <h2>Theoretic C# Example</h2>
  
  <pre class="lang-cs prettyprint-override"><code>[Test]
  public void UsingDapper_Query_CallFunctionThatTakesInUserDefinedCompositeType_FunctionUsesUserDefinedCompositeType()
  {
      // Arrange
      using (var conn = new NpgsqlConnection(_getConnectionStringToDatabase()))
      {
          var funcName = "testfuncthattakesinudt";
          var expect = CharacterTestData.First();
  
          // Act
          var result = conn.Query&lt;Character&gt;(funcName,
              new
              {
                  our_hero = new
                  {
                      first_name = CharacterTestData.First().first_name,
                      last_name = CharacterTestData.First().last_name
                  }
              },
              commandType: CommandType.StoredProcedure
          );
  
          // Assert
          Assert.AreEqual(expect, result);
      }
  }
  </code></pre>
  
  <p>I know that using plain Npgsql, it would be necessary to create the parameter something similar to:</p>
  
  <pre class="lang-cs prettyprint-override"><code>var udtCompositeParameter = new NpgsqlParameter
  {
      ParameterName = "our_hero",
      Value = new
      {
          first_name = CharacterTestData.First().first_name,
          last_name = CharacterTestData.First().last_name
      },
      DataTypeName = "hero"
  };
  </code></pre>
  
  <p>But using Dapper, I haven't found a way to set <code>DataTypeName</code> or something similar. I've tried many different ways to shape the parameter for Dapper (for example using something like <code>DynamicParameter</code> and specifying <code>dbType: DbType.Object</code>), but regardless, I always get some similar error related to the composite type. I've also looked at the <a href="https://github.com/StackExchange/Dapper" rel="noreferrer">Dapper source</a>, but from what I saw, it was light on PostgreSQL specific tests and those tests that seemed to be inline with what I'm trying to do were tailored to SQL Server.</p>