Ormlite select to typed object

Does the anonymous to typed projection support nested objects, having some trouble populating the nested type?

public class Tbl {
  public string Col1 {get;set;}
  public string Col2 {get;set;}
public class Poco {
  public string Col1 {get;set;}
  [Reference] // tried with and without
  public SubPoco Sub {get;set;}
public class SubPoco {
  public string Col2 {get;set;}

public class Query {
  public void Test() {
    // tried nested anonymous obejct
    db.Select<Poco>(db.Select<Tbl>(tbl => new { Col1 = tbl.Col1, Sub = new { tbl.Col2 } });

    // tried this 
    db.Select<Poco>(db.Select<Tbl>(tbl => new { Col1 = tbl.Col1, SubCol2 = tbl.Col2 } });


I don’t get what you’re trying to do with the doube select in a select here?

Did you just want to just want to convert Tbl results to Poco?

var results = db.Select<Tbl>(tbl => new { Col1 = tbl.Col1, Sub = new { tbl.Col2 } });
var pocos = results.Map(x => x.ConvertTo<Poco>());

an oversimplification, the real tables have about 100 and 50 cols respectively and there is a join

I can’t get the Sub object in the poco to be populated from the anonymous object and I’m trying to see if I can avoid creating another intermediate object (again there’s a lot more props in the poco’s and selects)

db.Select<Poco>(db.From<BigTable>().Join<AnotherTbl>(...).Select<BigTable, AnotherTbl>(bt, an) => new { Col1 = bt.Col1, Sub = new { an.Col2 } }); 

Can you provide code that compiles please? cause I still don’t know what it’s trying to do.

Created a gist

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:

public class Supplier
    public string Code { get; set; }

    public string Name { get; set;  }

    public string Address1 { get; set;  }

    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:

    public Address  Address => new  Address { Address1 = Address1, ... }

    public string Address1 { get; set;  }

    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.

ok, I figured I would need a flat structure, just wanted to confirm. :thumbsup:

1 Like