Can we use POCO class iteself as Response Object in AutoQuery

ServiceStack Auto Query
1

Hi Team,

I have created my custom query with “Join” and “AND” condition. I used to have same POCO class as response object, on which table I am firing my custom query. The query formed is correct, however result through AutoQuery seems different than query results obtained from SSMS using same query.
When I created a different response object and used to store result of AutoQuery response, it gives me correct results.
However my client is developed in accordance with data structure as of results obtained from POCO classes and I don’t want to change it. Can you suggest me any way to use same POCO class as response object with correct data.

I am explaining you the case with following example-

POCO class-

[Alias("SourceDevice")]
public partial class SourceDevice : IHasId<int> 
{
    [Alias("Id")]
    [AutoIncrement]
    public int Id { get; set; }
    [References(typeof(SourceDeviceCategory))]
    public int? SourceDeviceCategoryId { get; set; }
    [References(typeof(OperatingSystem))]
    public int? OperatingSystemId { get; set; }
 }

My custom query is-

private const string customQuery = "SourceDevice " +
" LEFT JOIN SourceDeviceCategory ON SourceDeviceCategory.Id = SourceDevice.SourceDeviceCategoryId" + 
" LEFT JOIN OperatingSystem ON OperatingSystem.Id = SourceDevice.OperatingSystemId" + 
" LEFT JOIN NetworkIPAddress ON NetworkIPAddress.SourceDeviceId = SourceDevice.Id AND IsPrimary=1"

And my response object, with POCO class is-

public class GetAllSourceDeviceDto : QueryDb<SourceDevice, SourceDevice>

This gives me results with more number of NetworkIPAddress data & that too of “IsPrimary=False” (probably, may be due to using same type as response) (and my UI is developed in accordance to data structure by this response).

However, when I used newly created rsponse object like-

public class SourceDeviceResponse
{
    public int Id { get; set; }
    public int NetworkIPAddressId { get; set; }
    public string NetworkIPAddressIPAddress { get; set; }
    public string NetworkIPAddressVLANName { get; set; }
    public int? NetworkIPAddressUseId { get; set; }
    public int? NetworkIPAddressVLANNumber { get; set; }
    public int? NetworkIPAddressSourceDeviceId { get; set; }
    public string NetworkIPAddressDescription { get; set; }
    public string OperatingSystemName { get; set; }
    public int? OperatingSystemTypeId { get; set; }
    public string OperatingSystemVendor { get; set; }
}

It gives me correct networkIpaddress data but its flat.

So, my question is- can we use POCO class ‘SourceDevice’ as response object along with its structure like types and relationships ?

2

AutoQuery only maps to a flat POCO response in the simple {Table}{Field} convention mentioned in Joining Tables.

The only time it can return nested related results is when you’re using OrmLite References support.

3

Thanks mythz for quick response.
However, as part of curiosity I would like to know, in case of OrmLite References support, how come SQL query claims other data due to different response object ?
For e.g when I use flat response object for the joined custom query (which includes ‘AND’ condition to fetch ‘true’ valued data i.e. IsPrimary=1), it gives me correct rows in query result. But when I use OrmLite References support/POCO class as response object for the same joined custom query, it gives me data which are Non-Primary or IsPrimary=0 too.

4

Are you using Custom SQL for your [References] support because that would interfere with being able to load references.

5

Yes, I am using Custom SQL for [References] support. I am overriding Autoquery by Custom Query

6

Yes that would interfere with loading references.