Difference between join and loadreference

ServiceStack Auto Query
1

I think I’m missing something here, given the following models:

   [Alias("V_FreeField")]
   public class CustomField
   {
      [PrimaryKey]
      [AutoIncrement]
      [Alias("FreeFieldId")]
      public int Id { get; set; }

      [Alias("FreeFieldColumn")]
      public string Column  { get; set; }

      [Alias("FreeFieldDescription")]
      public string Description { get; set; }

      [Reference]
      public List<CustomFieldLookup> Values { get; set; }
   }

   [Alias("V_FreeFieldFieldLookUp")]
   public class CustomFieldLookup
   {
      [PrimaryKey]
      [Alias("FieldLookupId")]
      public int? Id { get; set; }

      [Alias("FreeFieldId")]
      public int CustomFieldId { get; set; }

      [Alias("FieldlookupActive")]
      public bool? Active { get; set; }

      [Alias("FieldLookupGroupId")]
      public int GroupId { get; set; }

      [Alias("FieldlookupSortOrder")]
      public int? SortOrder { get; set; }

      [Alias("FieldlookupValue")]
      public string Value { get; set; }
   }

If I do the following:

 var fields = Db.LoadSelect<CustomField>();

The fields are loaded and the Values property is populated correctly. However if I try to do like this:

 var joinedfields = Db.Select<CustomField>(Db.From<CustomField>().Join<CustomFieldLookup>());

The Values property is not populated. I’m guessing this is the reason I cannot get my AutoQuery response dto to populate the values property either. It doesn’t matter if I use the IJoin interface or not.

In the end I wan’t to be able to do an autoquery on the above nested models and return an identical structure as response dto with the nested reference list loaded. Also, i wan’t to be able to do autoquery searches on the nested field properties like Active.

How can I achieve this?

2

var joinedfields = Db.Select(Db.From().Join());

You might be joining multiple tables but you’re still only selecting from the primary table. Only LoadSelect<T> loads any property references. If you want to select from multiple tables you’d need a combined POCO, see docs on Selecting multiple columns across joined tables.

3

Does this mean that an AutoQuery based service can never return a nested structure?

4

AutoQuery uses LoadSelect which is how it can return related results.

5

Hmm, this seems to work like you say. But when using the <From, Into> notation my nested list is lost.

Changing the request DTO from:

public class FindCustomFields:
      QueryBase<CustomField, FindCustomFieldsResponse>{ ...

To

public class FindCustomFields:
      QueryBase<CustomField>

Makes everything work as expected.

CustomFieldsResponse looks like this:

 public class FindCustomFieldsResponse
   {
      [ApiMember(Description = "Custom field id")]
      public int Id { get; set; }

      ...

      [ApiMember(Description = "")]
      public List<CustomFieldLookup> Values { get; set; }
      
   }

But the values list never gets populated when using the <From, Into> notation. Using AutoQuery without the response dto and only the <Into> notation works.

6

These are 2 completely different things. References are used to define a relationship which if you use LoadSelect will populate the reference properties based on the defined relationships.

The custom POCO is a custom model that gets populated sourced from all the different tables in the query as per the conventions defined in OrmLite - it has nothing to do with reference properties.

7

So what is the correct way for me to populate the values property if I want the chain:
AutoQuery Request DTO<from, into> > AutoQuery Service > Response (of type into) with values

If <from, into> maps properties by name etc.Why is it skipping the value property then? Because the source model have a List<CustomFieldLookup> Values property and the target type have the exact same field and type.

8

It’s only mapping it directly from the executed SQL query which doesn’t have any values field, not from the source POCO which is ignored. I.e. OrmLite only looks at the target POCO its mapping to, what relationships that are defined on the source POCO has no influence.

Have you tried re-defining the relationships on the target POCO?