How to parse a postgresql UUID into a C# string using Dapper FluentMap?

Question

I have a function that is returning (among other things) a UUID. I am using FluentMap to handle some return columns that include spaces, but the column in question (invoice_id) doesn't have that issue.

An abbreviated version of the db query:

CREATE FUNCTION fnSearchInvoices (
    _SearchTerm TEXT
) 
RETURNS TABLE(
    store_id INTEGER,
    customer_id TEXT,
    "Customer Name" TEXT,
    "Store Name" TEXT,
    "Email" TEXT,
    invoice_id UUID
) AS $$
SELECT
I.store_id,
I.customer_id,
C.name AS "Customer Name",
S.name AS "Store Name",
C.email AS "Email",
I.id AS invoice_id
FROM Invoice I
JOIN Customer C
ON I.customer_id = C.id
JOIN Store S
ON I.store_id = S.id
WHERE
{bunch of conditions for matching search term}
;
$$ LANGUAGE SQL;

The request class:

public class SearchRequest
{
    public string SearchTerm { get; set; }
    public int PagingOffSet { get; set; }
    public int PageSize { get; set; }
    public string SortField { get; set; }
    public string SortDirection { get; set; }
}

The result class:

public class SearchResult
{
    public int StoreID { get; set; }
    public string CustomerID { get; set; }
    public string CustomerName { get; set; }
    public string StoreName { get; set; }
    public string Email { get; set; }
    public string InvoiceID { get; set; }
    public int TotalRecords { get; set; }
}

The EntityMap:

public class SearchResultMap : EntityMap<SearchResult>
{
    public SearchResultMap()
    {
        Map(p => p.CustomerID).ToColumn("Customer Name");
        Map(p => p.StoreName).ToColumn("Store Name");
    }
}

And finally, the call into the database from C#:

IDbConnection dbConnection = new NpgsqlConnection(strDbConnectionString)

string strCommand = "SELECT *, count(*) OVER() AS total_records ";
strCommand += "FROM fnSearchInvoices(:SearchTerm) ";
strCommand += "ORDER BY \"" + cSearchRequest.SortField + "\" " + cSearchRequest.SortDirection + " ";
strCommand += "LIMIT :PageSize OFFSET :PagingOffSet;";

cSearchResponseList = dbConnection.Query<T>(strCommand, cSearchRequest).ToList();

When I run the code above with exception handling, I get the following error (this is an exception message plus the inner message):

Error: Error parsing column 6 (invoice_id=12345678-abcd-1234-abcd-1234567890ef - Object) Details: System.InvalidCastException: Object must implement IConvertible. at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at Deserializeabcdef12-abcd-1234-abcd-abcdef123456(IDataReader )

I can remove InvoiceID from the SearchResponse class and the query works fine; it ignores that column and everything else maps as expected.

The db function is being used elsewhere and I'd rather not change it, although as a last resort I could cast in the function. It would just require extra changes elsewhere that are outside the scope of what I'm working on. And I realize I should be able to change the SELECT in my C# to explicitly cast the UUID to TEXT outside the function call by getting all the columns by name instead of with *, which I am going to try next. But for future reference, is there a way to get Dapper to correctly map a UUID return from the query into a C# string?

A simple fix would be to cast the UUID into a NVARCHAR(MAX) so that Dapper will assign to your property as if it were a simple string. You need the query to manually cast before Dapper reads. — Greg, Mar 30 '18 at 18:19
And why not use `Guid` instead of `string` for invoice id on C# side? — Evk, Mar 30 '18 at 18:35

Greg · Answer 1 · 2018-03-30T20:22:16.790

Sadly Dapper will attempt to associate the type via what the database specifically has for a type. Your invoice column is designated as a string type.

If you have the query return column:

CAST([Invoice] AS NVARCHAR(MAX)) AS [Invoice]

Dapper can now correctly map to your object that you designated as a string. That should rectify your issue. Your safest best would be to have the database correctly cast the column for you into a NVARCHAR(MAX).

Otherwise you would need to change your object property.

public Guid Invoice { get; set; }

How to parse a postgresql UUID into a C# string using Dapper FluentMap?

1 Answers1