I have a table with a similar schema:
CREATE TABLE [dbo].[Property](
[id] [uniqueidentifier] NOT NULL,
[PropertyOccupantTypeId] [uniqueidentifier] NULL,
[PropertyStatusId] [uniqueidentifier] NOT NULL
-- Other potential columns
)
It has a lot of lookup values on it that my user doesn't need to know anything about. They just need the PropertyStatus
not the PropertyStatusId
Assuming the lookup tables have 2 columns each (guid: id, varchar: value) Is there a way to write a query similar to:
SELECT p.id,
po.value as OccupantType,
ps.value as PropertyStatus
-- Other potential columns
FROM Property p
join PropertyOccupantType po on p.PropertyOccupant = po.id
join PropertyStatus ps on p.PropertyStatusId = ps.id
and have that map to a Property
object which looks like:
public class Property
{
public Guid id;
public string PropertyOccupant;
public string PropertyStatus;
}
Or do I need to query the additional tables manually and map the values that way?
This should work fine. Dapper doesn't care what query you write. It simply matches column names from the result set with the property names of your object.