Select Specific Column from a Linked Server Table

I have the following C# code to select a column from a table that is on a linked server:

        var query2 = $@"select [FileName] from [AMS_H2H].[H2H].[dbo].[FileReconciliation] where ProductCode = @productCode";
        LayZConnection(); //make the db connection
        var candidates = _dbConnection.Query<int>(query2, new { productCode = "ACHDH" });

When running it, I get the following error:

"Input string was not in a correct format."

If my query is instead the following, where I select all columns, it works:

var query2 = $@"select * from [AMS_H2H].[H2H].[dbo].[FileReconciliation]

What is the correct format to select just the FileName. Btw, the first query works fine from MSSMS.

Answer

You’re specifying a type of int in Query<int>, which will cause Dapper to try and map the result of the query to an integer, however your query is returning a filename in select [FileName], which would suggest that it is a string.

Changing the type Query<string> should solve the issue.

More information on Dapper’s Query method is available in Dapper’s documentation