Distinct when using autoquery and join

ServiceStack Auto Query
1

Hi,

i’m using joins in my dtos to be able to filter on subentities.
It works fine but when i retrieve the results i dont want it to return the sub entity records, only the distinct records (without the subentity). You can see in my DTO that i join against BookingResourceRelation

[ApiResponse(HttpStatusCode.Unauthorized, "You were unauthorized to call this service")]
[Authenticate]
public class BookingQuery : QueryDb<Booking, BookingQueryResponse>, IJoin<Booking, BookingResourceRelation>
{

    [ApiMember(
    Description =
        "Query for specific Booked Resources, default is all resources",
    ParameterType = "query",
    IsRequired = false)]
    [DataMember(Name = "BookedResourceIds")]
    public int[] BookingResourceRelationResourceIds { get; set; }

I can do that by using DistinctBy

List<Booking> result = Db.LoadSelect(q).DistinctBy(b=> b.Id).ToList();

But in the return it will not be correct using the Take and Count
This will still have the duplicated rows

    return new QueryResponse<BookingQueryResponse>
        {
            Offset = q.Offset.GetValueOrDefault(0),
            Total = (int)Db.Count(q),
            Results = merged.Select(Translate).ToList()
        };

How can i perform the distict autoquery on the main entity Booking without retrieving the rows on the subentity called BookingResourceRelation but still keep the possibility to filter on BookingResourceRelationResourceIds

Thx!

2

Your mixing client side vs server side querying. In your custom AutoQuery impl try something like:

q.SelectDistinct();
return new QueryResponse<BookingQueryResponse>
    {
        Offset = q.Offset.GetValueOrDefault(0),
        Total = (int)Db.Count(q),
        Results = Db.Select(q)
    };
3

Hi,
thx for your solutions, this doesnt work as it will return 2 results as the joined table has 2 rows.
I only want to return the parent table items (the join is only to be able to filter on the child items).

Regards Kristian

4

Are you sure you added q.SelectDistinct()? Can you enable debug logging and paste the SQL Query that was executed in the logs or if you can get it using an SQL Profiler.