I have a peculiar issue that I cannot seem to find any answers on. It seems that when I introduce a join to a stored procedure, Dapper's mapping refuses to work properly.

My custom object:

public class CustomObject
{
    public string ColumnOne { get; set; }
    public string ColumnTwo { get; set; }
    public DateTime? ColumnThree { get; set; }
    public string ColumnFour { get; set; }
}

In my repository call, the customObjects is always returning 0 results.

My repository call with Dapper:

public IEnumerable<CustomObject> GetTheThings(int? integerParameter)
{
    List<CustomObject> customObjects;

    using (var connection = "connection string syntax"))
    {
        string sql = "[storedProcedureName]";

        connection.Open();

        customObjects = connection.Query<CustomObject>(sql,
                new { IntegerParameter = integerParameter },
                commandType: CommandType.StoredProcedure).ToList();
    }

    return customObjects;
}

Stored procedure's basic syntax:

SELECT  
    t1.column_one AS [ColumnOne],         -- column_one is nvarchar data type
    t2.column_two_thing AS [ColumnTwo],   -- column_two_thing is nvarchar data type
    t2.column_three AS [ColumnThree],     -- column_three is DateTime data type
    t2.column_four AS [ColumnFour]        -- column_four is nvarchar data type
FROM 
    Table1 t1 WITH (NOLOCK)
JOIN 
    Table2 t2 WITH (NOLOCK) ON t1.Identifier = t2.Identifier -- Identifiers are both uniqueidentifier data types
WHERE 
    t1.column_one = @IntegerParameter;

When I execute this stored procedure directly in SSMS, it returns the values that I'm expecting and in the format I'm expecting

When I execute via my application at runtime with Dapper, the results that return are 0.

For some reason, when I remove the join, and I manually type values for the columns that rely on the join, I receive results back.

Script that returns results and maps appropriately:

SELECT  
    t1.column_one AS [ColumnOne],         -- column_one is nvarchar data type
    'Literally anything' AS [ColumnTwo],  -- column_two_thing is nvarchar data type
    GETDATE() AS [ColumnThree],           -- column_three is DateTime data type
    'Anything here too' AS [ColumnFour]   -- column_four is nvarchar data type
FROM 
    Table1 t1 WITH (NOLOCK)
WHERE 
    t1.column_one = @IntegerParameter;

I've also tried replacing each column with a hard coded 'Literally anything' as [ColumnName] to ensure it wasn't just a property being lost in Dapper translation, but no results would return from my method at runtime so long as the join was within the query.

I've been reading the Dapper documentation and perusing through StackOverflow and other resources for any suggestions, but cannot find where I have gone wrong.

Any and all help or suggestions would be tremendously appreciated!

CodePudding user response：

The fact that you are getting no rows at all suggests that there are no results in your two tables which can be joined.

The JOIN statement will be an inner join by default. This will return only records that have matching values in both tables.

EDIT: In this case, the inconsistency between running the query in SSMS and Dapper was because of the permissions of the accounts in the connection string were not the same. Because of this the data in one of the tables could not be accessed, so the JOIN returned no rows.