Ytukyg

I'm extracting information from a SQL Server database using Dapper. The POCO for the information in question is below.

public class Client

    {

        public string ShortName { get; set; }

        public string ContactDetail { get; set; }

        public string Street { get; set; }

        public string District { get; set; }

        public string Town { get; set; }

        public string County { get; set; }

        public string Postcode { get; set; }

    }

When I extract the information for the above object using the query below all the information is mapped correctly, apart from the following line max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail' I believe this may be because I am not simply extracting the data from a table column and I am instead doing some processing using the CASE clause beforehand and therefore Dapper cannot find the correct place to map the information onto.

I thought including the AS clause would help Dapper to find the correct mapping but this didn't actually work.

How can I change my query so that Dapper can map the ContactDetail data correctly?

select 

tp.ShortName as 'ShortName', 

max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail',

tp.Street, 

tp.District, 

tp.Town, 

tp.County, 

tp.PostCode

... (rest of query snipped as unneeded)

I know the query works correctly as I can run it in SSMS and it returns the correct information.

Dapper doesn't have any interest or knowledge of what is inside the query - it only cares about the shape of the results. So: whatever is happening - it is part of the query.

My hunches would be:

• the data genuinely is missing and/or null
  


• something to do with null handling and null-coalescing, perhaps with the max complicating things

Note that you can't just compare to SSMS output, because SSMS and ADO.NET (SqlClient) can have different SET option defaults, which can make significant yet subtle differences to some queries.

To investigate it properly, I'd really need a reproducible example (presumably with fake data); without that, we're kinda guessing.

To reiterate, though:

I strongly suspect that if you execute the same query via ExecuteReader or similar, you'll find that the value coming back in that position is indeed null (or DBNull).

I'm pretty sure the issue is that if you're using the object mapper in Dapper (i.e. Client in <>) then it will simply look for those columns in the matching table. The "ContactDetail" does not exist (it's derived), so it will not find it.

You have a couple of options here, depending on your data and use case.

• Get the raw data from Dapper, and then use Linq (or similar) to derive the ContactDetail using logic within the program.




• Write a stored procedure based on your query, and use Dapper to run it




• Use Dapper's Query command to run your SQL statement, instead of trying to directly map the object.

Let me know if you get stuck on this, or require further explanation.