CustomSelect with From for correlated sub-query

ServiceStack.OrmLite
1

I’ve just migrated our legacy v3 API project to v4.

I previously inheritted SqlExpressionVisitor<T> (now SqlExpression<T> I think) and provided a hacky Subquery method as follows.

q.Subquery(e => Sql.As(@"(
                SELECT COUNT(*)
                FROM tblTreatment
                WHERE treatmentCaseID = caseId
                AND (ISNULL(treatmentClosed, 0) <> 1 AND treatmentDateTime < GETDATE()))",
            "unclosedConsultations"));

On my model object, I’d then have

public class CaseList {
  /*...
    20 other props 
   ...*/
  [Alias("unclosedConsultations")]
  public int UnclosedConsultation {get;set;}
}

and then I could do

q.Where(x => x.UnclosedConsultations > 2)
Db.Select(q);

I’m trying to work out the best approach to do this in v4. I see you’ve added [CustomSelect] and Sql.Custom but neither seems to quite fill the gap I have. CustomSelect doesn’t seem to apply to From<T> (or SqlExpression<T>) with joins, and Sql.Custom can’t be queried DB Side, and also involves me typing out lots of column names as well.

I’ve got about 20 of these to rectify, and am looking for the best solution, which ideally doesn’t involve manual SQL. Any ideas very welcome!

2

I can’t follow what this SQL is trying to do here, can you provide the full generated SQL it produces in v3?

Also what does the Subquery() impl look like?

3

I’ve given the full SQL, so a little noisy I’m afraid.

SELECT "customerforename", 
       "customersurname", 
       "customertitle", 
       "customerhometelephone", 
       "customerworktelephone", 
       "customermobiletelephone", 
       "customeremail", 
       "customercategory", 
       "customerdateofbirth", 
       "customeraddressline1", 
       "customeraddressline2", 
       "customeraddressline3", 
       "customeraddressline4", 
       "customeraddressline5", 
       "customerpostcode", 
       "locationname", 
       "practitionertitle", 
       "practitionerforename", 
       "practitionersurname", 
       "caseid", 
       "casecustomerid", 
       "casepractitionerid", 
       "casecreatedbyuserid", 
       "casedischargedbyuserid", 
       "casemedicalcontactid", 
       "casedateofadmission", 
       "casereferralreason", 
       "casediagnosis", 
       "casediagnosiscode", 
       "casedateofdischarge", 
       "casepatientoutcome", 
       "casepractitioneroutcome", 
       "casedischargestatus", 
       "casenumberofappointments", 
       "casereference", 
       "caseadmissioncode", 
       "casereviewdate", 
       "casereferraldate", 
       "casebodysite", 
       "casebodyside", 
       "casetimepresent", 
       "casetimepresentunit", 
       "caseinjurycause", 
       "casepreexistingconditions", 
       "caseadmissionpainscale", 
       "caseadmissionletterdate", 
       "casedischargepainscale", 
       "casedischargeletterdate", 
       "caseclinicnotes", 
       "caseagreedfunctionaloutcome", 
       "caseclosed", 
       "casecloseduser", 
       "casecloseddatetime", 
       "casepatienttype", 
       "caseauditnotes", 
       "caseauditdate", 
       "casestandardpathwayfollowed", 
       "caselocationid", 
       "caseofinterest", 
       "casefirstappointmentid", 
       "caseservicetypeid", 
       "caselinkedcaseid", 
       "casestatus", 
       "caseautosaved", 
       "casedata", 
       "caseadmissiontemplateid", 
       "casedischargetemplateid", 
       "caseadmissionhighlight", 
       "casedischargehighlight", 
       "casetype", 
       (SELECT Count(appointmentid) 
        FROM   tblappointment appointment 
        WHERE  appointment.appointmentcaseid = caseid)    AS "appointmentCount", 
       (SELECT Count(alertid) 
        FROM   tblalert alert 
        WHERE  alert.alertobjectid = customerid 
               AND ( alert.alertdiscriminatortype = 'P' ) 
               AND alert.alertcomplete = 0)               AS "alerts", 
       (SELECT Count(*) 
        FROM   tblappointment 
               LEFT OUTER JOIN tbltreatment 
                            ON treatmentappointmentid = appointmentid 
                               AND treatmentcaseid = appointmentcaseid 
        WHERE  appointmentstartdatetime < Getdate() 
               AND treatmentid IS NULL 
               AND appointmentcaseid = tblcase.caseid)    AS 
       "appointmentsWithoutConsultations", 
       (SELECT Count(*) 
        FROM   tbltreatment 
        WHERE  treatmentcaseid = caseid 
               AND ( Isnull(treatmentclosed, 0) <> 1 
                     AND treatmentdatetime < Getdate() )) AS 
       "unclosedConsultations", 
       (SELECT TOP 1 treatmentdatetime 
        FROM   tbltreatment 
        WHERE  treatmentcaseid = caseid 
        ORDER  BY treatmentdatetime DESC)                 AS 
       "LastConsultationDate", 
       (SELECT statusText = CASE casestatus 
                              WHEN 1 THEN 'Awaiting First Appointment' 
                              WHEN 3 THEN 'In Treatment' 
                              WHEN 7 THEN 'On Hold' 
                              WHEN 8 THEN 'Discharged' 
                              WHEN 12 THEN 'Closed - Awaiting Review' 
                              ELSE 'Closed and Reviewed' 
                            END)                          AS "StatusText", 
       COALESCE((SELECT 
                Stuff((SELECT N'; ' + ( authcode + ': ' 
                               + Cast(authmaxtreatments - authusedtreatments AS 
                                        VARCHAR 
                                                ) 
                               + ' remaining' ) 
                               FROM   tblauthcode 
                               WHERE  authcaseid = caseid 
                               FOR xml 
path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, N'')), ' ') 
                                          AS "auth", 
COALESCE((SELECT Stuff((SELECT N', ' + groupname 
               FROM   tblcustomergroup 
               WHERE  groupid IN(SELECT authgroupid 
                                 FROM   tblauthcode 
                                 WHERE  authcaseid = caseid) 
               FOR xml path(''), type).value('text()[1]', 
        'nvarchar(max)'), 1, 2 
                 , N'')), ' ')            AS "groupName", 
COALESCE((SELECT Stuff((SELECT N', ' + Cast(groupid AS VARCHAR) 
               FROM   tblcustomergroup 
               WHERE  groupid IN(SELECT authgroupid 
                                 FROM   tblauthcode 
                                 WHERE  authcaseid = caseid) 
               FOR xml path(''), type).value('text()[1]', 
        'nvarchar(max)'), 1, 2 
                 , N'')), ' ')            AS "groupId" 
FROM   "tblcase" 
       LEFT OUTER JOIN "tblcustomer" 
                    ON "tblcustomer"."customerid" = "tblcase"."casecustomerid" 
       LEFT OUTER JOIN "tbllocation" 
                    ON "tbllocation"."locationid" = "tblcase"."caselocationid" 
       LEFT OUTER JOIN "tblpractitioner" 
                    ON "tblpractitioner"."practitionerid" = 
                       "tblcase"."casepractitionerid" 
WHERE  ( "caselocationid" IN ( 1, 2, 3, 4, 
                               5, 6, 7, 8, 
                               9, 10, 1040, 1041, 
                               1042, 1043, 1044, 1045, 
                               2045, 2046 ) 
         AND "casepractitionerid" IN ( 5 )
         AND "unclosedConsultations" > 2

 )

And the class for the visit, slightly abbreviated…

namespace Services.Framework
{
    public class BzSqlExpressionVisitor<T> : SqlServerExpressionVisitor<T>
    {
        private List<string> _subQueryColumns = new List<string>();

        public virtual SqlExpressionVisitor<T> Subquery<TKey>(Expression<Func<T, TKey>> fields)
        {
            _subQueryColumns.Add(Visit(fields).ToString());
            return this;
        }

        public override string ToSelectStatement()
        {
            var subQuery = _subQueryColumns.Join(", ");
            if (!string.IsNullOrEmpty(subQuery))
                SelectExpression = SelectExpression.Insert(SelectExpression.LastIndexOf("\nFROM"), ", " + _subQueryColumns.Join(", "));
           /* unrelated stuff */
          return selectString;
     }
}
4

ok that’s a pretty complex query, I can’t see how you could be using OrmLite’s Typed API for this as it’s very SQL Server specific. What’s the v3 OrmLite .Select() code look like?

5

That particular one is probably our most complex, and our join and subquery implementation is SQL Server only. I’ll choose a slightly simpler query.

[The join approach is different from how v4 does it. So Queryable ~= From which was v3 naming anyway. But it takes a DTO that has the joined columns on it Queryable<TRes>. (whereas v4 is From<T>.Select<TRes>). I’ve ported all that over to v4 style anyway so is probably irrelevant.]

          var q = Db.Queryable<InvoiceWithCustomerBillingLocation>();
            q.LeftJoin<Location>(c => c.LocationId, l => l.Id);
            q.LeftJoin<Account>(c => c.AccountId, l => l.Id);
            q.LeftJoin<Customer>(x => x.CustomerId, x => x.Id);

            q.Where(x => x.AccountTypeId == req.GroupId && x.AccountType == "G");
            q.DateRange(req.Date, x => x.Date >= req.Date.From, x => x.Date < req.Date.To);
            q.Subquery(e => Sql.As("(SELECT SUM(billingItemTotal) FROM tblBillingItem b2 WHERE b2.billingItemInvoiceID = invoiceId AND billingItemTotal >=0)", "Debit"));
            q.Subquery(e => Sql.As("(SELECT SUM(0 - billingItemTotal) FROM tblBillingItem b2 WHERE b2.billingItemInvoiceID = invoiceId AND billingItemTotal < 0)", "Credit"));

            return Db.Select(q);

The model looks like.


public class InvoiceWithCustomerBillingLocation : Invoice
    {
        [Alias("locationName")]
        public string LocationName { get; set; }

        [Ignore]
        public decimal Debit { get; set; }
        [Ignore]
        public decimal Credit { get; set; }

        [Alias("accountType")]
        public string AccountType { get; set; }

        [Alias("accountTypeId")]
        public int AccountTypeId { get; set; }

        [Alias("customerForename")]
        public string CustomerForename { get; set; }
        [Alias("customerTitle")]
        public string CustomerTitle { get; set; }
        [Alias("customerSurname")]
        public string CustomerSurname { get; set; }

        [Ignore]
        public string CustomerName
        {
            get { return (CustomerTitle.Safe().Trim() + " " + CustomerForename.Safe().Trim() + " " + CustomerSurname.Safe().Trim()); }
        }
    }

  [Alias("tblInvoice")]
    public class Invoice : IHasId
    {
        [AutoIncrement]
        [Alias("invoiceId")]
        public int Id { get; set; }
        [Alias("invoiceStatus")]
        public string Status { get; set; }
        [Alias("invoiceReference")]
        public string Reference { get; set; }
        [Alias("invoiceDate")]
        public DateTime Date { get; set; }
        [Alias("invoiceDueDate")]
        public DateTime DueDate { get; set; }
        [Alias("invoiceFullyPaidDate")]
        public DateTime? FullyPaidDate { get; set; }
        [Alias("invoiceGrandTotal")]
        public decimal GrandTotal { get; set; }
        [Alias("invoiceAmountPaid")]
        public decimal AmountPaid { get; set; }
        [Alias("invoiceCustomerId")]
        public int? CustomerId { get; set; }
        [Alias("invoiceAccountId")]
        public int AccountId { get; set; }
        [Alias("invoiceLocationId")]
        public int LocationId { get; set; }
        [Alias("invoiceAddressTo")]
        public string AddressTo { get; set; }
        [Alias("invoiceAddressLine1")]
        public string AddressLine1 { get; set; }
        [Alias("invoiceAddressLine2")]
        public string AddressLine2 { get; set; }
        [Alias("invoiceAddressLine3")]
        public string AddressLine3 { get; set; }
        [Alias("invoiceAddressLine4")]
        public string AddressLine4 { get; set; }
        [Alias("invoiceAddressLine5")]
        public string AddressLine5 { get; set; }
        [Alias("invoicePostCode")]
        public string PostCode { get; set; }
        [Alias("invoiceReminder")]
        public DateTime? Reminder { get; set; }
        [Alias("invoiceSecondReminder")]
        public DateTime? SecondReminder { get; set; }
        [Alias("invoiceTotalExcludingVat")]
        public decimal TotalExcludingVat { get; set; }
        [Alias("invoiceVatAmount")]
        public decimal VatAmount { get; set; }
        [Alias("invoiceBatchId")]
        public int? BatchId { get; set; }
	[Alias("invoiceNotes")]
	public string Notes { get; set; }
    }
6

Can we try break the query down as to what limitations Sql.Custom() has that would prevent it from working?

I’ve created a live demo on gistlyn so we have a runnable example we can customize to see what it’s limitations are.

Currently this query:

var q = db.From<Custom1>()
    .LeftJoin<Custom2>()
    .Select<Custom1, Custom2>((t1, t2) => new
    {
        t1,
        t2,
        StatusText = Sql.Custom(@"(CASE Custom1Id 
            WHEN 1 THEN 'Awaiting First Appointment' 
            WHEN 3 THEN 'In Treatment' 
            WHEN 7 THEN 'On Hold' 
            WHEN 8 THEN 'Discharged' 
            WHEN 12 THEN 'Closed - Awaiting Review' 
            ELSE 'Closed and Reviewed' 
        END)")
    });

Generates this SQL:

SELECT "Custom1"."Id", "Custom1"."Field1", "Custom1"."Field2", "Custom2"."Id", "Custom2"."Custom1Id", "Custom2"."Field3", "Custom2"."Field4", (CASE Custom1Id 
            WHEN 1 THEN 'Awaiting First Appointment' 
            WHEN 3 THEN 'In Treatment' 
            WHEN 7 THEN 'On Hold' 
            WHEN 8 THEN 'Discharged' 
            WHEN 12 THEN 'Closed - Awaiting Review' 
            ELSE 'Closed and Reviewed' 
        END) AS StatusText 
FROM "Custom1" LEFT JOIN "Custom2" ON
("Custom1"."Id" = "Custom2"."Custom1Id")

Can you modify and save the gist showing why Sql.Custom() doesn’t work for you?

7

http://gistlyn.com/?gist=1099a2dd4572d557e859e2c21097a61d

I added Line 47…

I think CustomSelect would allow that, but it doesn’t seem to work with the Typed API.

8

I hadn’t realised you could do that - that is useful for me!

9

The issue is that the field doesn’t exist on the table and when you add it, i.e:

public class Custom1
{
    public int Id { get; set; }
    public string Field1 { get; set; }
    public string Field2 { get; set; }
    [Ignore]
    public string StatusText { get; set; }
}

It will fail with:

no such column: Custom1.StatusText

Since OrmLite will use a fully-qualified column reference as the table contains joins.

You can really only use a Custom SQL condition at that point:

    .Where("StatusText = 'On Hold'");
10

Since OrmLite will use a fully-qualified column reference as the table contains joins.

My fudged joining didn’t, so we got away with it until now!!

OK - I’ll try custom sql approach, and report back if I run into anything else.

Thanks for your help.

1 Like
11

FYI if it helps I’ve made a change to OrmLite so that using a [CustomSelect] attribute wont include the table prefix in this commit.

Which will let you now do:

public class Custom1
{
    public int Id { get; set; }
    public string Field1 { get; set; }
    public string Field2 { get; set; }

    [CustomSelect(@"(CASE Custom1Id 
        WHEN 1 THEN 'On Hold' 
        WHEN 3 THEN 'In Treatment' 
        WHEN 7 THEN 'Awaiting First Appointment' 
        WHEN 8 THEN 'Discharged' 
        WHEN 12 THEN 'Closed - Awaiting Review' 
        ELSE 'Closed and Reviewed' 
    END)")]
    public string StatusText { get; set; }
}

var q = db.From<Custom1>()
    .LeftJoin<Custom2>()
    .Where(x => x.StatusText == "On Hold")
    .Select<Custom1, Custom2>((t1, t2) => new
    {
        t1,
        t2,
    });

This change is available from v4.5.9 that’s now available on MyGet.

12

Awesome - thanks - I’ll check it out