ok it’s clearer, your first example used db.Select<Poco>(db.Select<
and was thrown off with the ,
typo in your second example.
This is much clearer:
return db.Select<Supplier>(
db.From<PLSUPP>().Join<NDMAS>((plsupp, ndmas) => plsupp.PLSUP_NDCODE == ndmas.NDM_NDCODE)
.Select<PLSUPP, NDMAS>(
(plsupp, ndmas) => new
{
Code = plsupp.PLSUP_SUPPLIER,
Address = new {
Address1 = ndmas.NDM_ADDR1,
Address2 = ndmas.NDM_ADDR2,
Address3 = ndmas.NDM_ADDR3,
Address4 = ndmas.NDM_ADDR4,
Address5 = ndmas.NDM_ADDR5,
Postcode = ndmas.NDM_POSTCODE,
Country = ndmas.NDM_COUNTRY,
},
AddressType = ndmas.NDM_ADDTYPE,
Area = ndmas.NDM_AREA,
Description = ndmas.NDM_DESC,
Email = ndmas.NDM_EMAIL,
LastDate = ndmas.NDM_LASTDATE,
LastTime = ndmas.NDM_LASTTIME,
Name = ndmas.NDM_NAME,
Telephone = ndmas.NDM_TELEPHONE,
Telex = ndmas.NDM_TELEX
}));
So this Custom Select is not a C# type projection, it’s a mapping of all RDBMS fields you want to select and what aliases you want them returned in, the returned dataset is then what gets mapped back to the Supplier
DTO.
So you can only select a flat structure with the Values containing the RDBMS fields you want to select and the Keys containing the aliases you want them returned in.
So you’d either need the same flat structure on the Type you want to map to, e.g:
[DataContract]
public class Supplier
{
[DataMember]
public string Code { get; set; }
[DataMember]
public string Name { get; set; }
[DataMember]
public string Address1 { get; set; }
[DataMember]
public string Address2 { get; set; }
....
}
If you wanted the nested structure in my DTOs, you could have an intermediate POCO that matches the flat structure returned in the query then use a custom AutoMapping converter to map it to my DTO, e.g:
AutoMapping.RegisterConverter((DbSupport from) => {
var to = from.ConvertTo<Support>(skipConverters:true); // avoid infinite recursion
to.Address = from.ConvertTo<Address>();
return to;
});
var results = db.Select<DbSupport>(...);
var dtos = results.ConvertTo<List<Support>>();
You could also reuse the same type and use [IgnoreDataMember]
to hide the flat structure and a computed property to return the nested Address
, e.g:
[DataMember]
public Address Address => new Address { Address1 = Address1, ... }
[IgnoreDataMember]
public string Address1 { get; set; }
[IgnoreDataMember]
public string Address2 { get; set; }
Or instead of the intermediate POCO you can access the results in a dynamic result set like a Dictionary and populate your DTOs from that.