ServiceStack AutoQuery join use

Question

After reading the documentation, I am not sure but I have come to the conclusion that when creating QueryDb, you cannot choose the columns to join by? And I am under the impression, you must have DTO object to copy to? You cannot copy to a regular object or a dynamic object?

public class SampleAutoQueryDb : QueryDb<MailResponseDetailOrm, object>, ILeftJoin<MailResponseDetailOrm, MailResponseOrm> { }

Can anyone provide any insight on joining my MailResponseOrm to MailResponseDetailOrm. MailResponseDetailOrm has 5 fields namely the Email address. And I would like MailResponseOrm to be joined to it by Email as well. I also, for good measure do not want to alter either columnname. Would I have to create a custom implementation or a service to do this?

UPDATE

Here is my code as posted below:

        [Alias("MailReportsDetail")]
        public class MailResponseDetailOrm
        {
            public string Email { get; set; }

            public int ID { get; set; }

            [Alias("RespDate")]
            public DateTime? AddedDateTime { get; set; }

            [Alias("DLReport")]
            public string Action { get; set; }

            public string ActionDetail { get; set; }

            public string IP { get; set; }

            public string UserAgent { get; set; }

            public string EmailReferrer { get; set; }
        }

    [Alias("MailReports")]
    public class MailResponseOrm
    {
        public int ID { get; set; }

        public string Email { get; set; }

        public string Address1 { get; set; }

        public string Address2 { get; set; }

        public string City { get; set; }

        public string Company { get; set; }

        public string Contact { get; set; }

        public string Country { get; set; }

        [Alias("LastMail")]
        public DateTime? ModifiedDateTime { get; set; }

        [Alias("LastReport")]
        public string Action { get; set; }

        public DateTime? OptOut { get; set; }

        public string Part { get; set; }

        public string Phone { get; set; }

        public string PostalCode { get; set; }

        public string Source { get; set; }

        public string State { get; set; }

        public string Title { get; set; }

        #region Obsolete

        [Obsolete]
        public string Class { get; set; }

        [Obsolete]
        public string IP { get; set; }

        #endregion
    }

public class SampleAutoQueryDb : QueryDb<MailResponseDetailOrm> { }

public class MyQueryServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    // Override with custom implementation
    public object Any(SampleAutoQueryDb query)
    {
        var q = AutoQuery.CreateQuery(query, base.Request);
        q.Join<MailResponseDetailOrm, MailResponseOrm>((x, y) => x.Email == y.Email)
            // .Select<MailResponseDetailOrm, MailResponseOrm>((x, y) => new { x.ID, y.Email })
            ;
        return AutoQuery.Execute(query, q);
    }
}

mythz · Accepted Answer · 2018-05-24T20:36:39.173

2

Joins in AutoQuery needs to use OrmLite's Joins Reference conventions and all AutoQuery Services results are returned in a Typed DTO, which by default is the table being queried or you can use the QueryDb<From,Into> base class to return a custom result of columns from multiple joined tables.

You would need to use a Custom AutoQuery Implementation or your own Service implementation if you need customizations beyond this, e.g:

public class SampleAutoQueryDb : QueryDb<MailResponseDetailOrm> { }

public class MyQueryServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    // Override with custom implementation
    public object Any(SampleAutoQueryDb query)
    {
        var q = AutoQuery.CreateQuery(query, base.Request);
        q.Join<MailResponseDetailOrm,MailResponseOrm>((x, y) => x.Email == y.Email);
        return AutoQuery.Execute(query, q);
    }
}

edited May 24 '18 at 20:36

answered May 23 '18 at 18:55

mythz

141,670
29
246
390

I have updated the code but the lamdas are complaining that they are not in context. Why would that be? – petrosmm May 24 '18 at 20:36
So lol, I was close however. It still complains `x.Email= error CS0103: The name 'x' does not exist in the current context` and `y.Email= error CS0103: The name 'y' does not exist in the current context` – petrosmm May 24 '18 at 21:22
@petrosmm Please update your question with the exact source code you’re using and the error. Note you cannot use object in your AutoQuery Request DTO, it needs to be a concrete POCO Type, with the table you want to query on and if you want to return custom results, a POCO with all the fields you want returned. – mythz May 24 '18 at 23:01
1

@petrosmm That code does not have any build errors. – mythz May 29 '18 at 21:06
apologies, it seems my build may have been cached because I just tried again and was successful. Seems like this question is in order and has been answered properly. – petrosmm May 30 '18 at 15:08

score 2 · Answer 2 · answered May 23 '18 at 19:19

2

// The query to join 2 objects on field names not specifically set in the class.

var q = Db.From<MailResponseDetailOrm>().Join<MailResponseDetailOrm>(x,y) => x.Email = y.Email);

// Run the query

var results = Db.Select(q);

answered May 23 '18 at 19:19

Bob K

470
3
8

Hi Bob, I was specifically asking about AutoQuery to be clear, unless I can re-use this in a service implementation. – petrosmm May 23 '18 at 19:45

ServiceStack AutoQuery join use

2 Answers2