EntitySpaces/C#: How to use a subquery in a CASE statement?

Question

I am trying to emulate a snippet of SQL using EntitySpaces. I'm just about ready to fall back to good ol' raw SQL, but would rather learn how to do this properly...

This is the SQL I am trying to reproduce:

SELECT 
    CASE WHEN GL.SOURCE = 'AP' THEN (SELECT COMPANY FROM VEND WHERE VEND.ID = GL.ID)
        WHEN GL.SOURCE = 'AR' THEN (SELECT COMPANY FROM CUST WHERE CUST.ID = GL.ID)
        WHEN GL.SOURCE = 'SB' THEN (SELECT COMPANY FROM SBMASTER WHERE SBMASTER.ID = GL.ID)
        ELSE '' END AS COMPANY
FROM GL
    LEFT OUTER JOIN ACCT ON GL.ACCT = ACCT.ACCT

I've been playing around with the code a bit with no luck. This is what I've got at the moment:

    GlQuery qryGl = new GlQuery("qryGl");
    AcctQuery qryAcct = new AcctQuery("qryAcct");
    AcctQuery qryAcctSub = new AcctQuery("qryAcct");
    VendQuery qryVendSub = new VendQuery("qryVend");
    CustQuery qryCustSub = new CustQuery("qryCust");
    SbmasterQuery qrySbmasterSub = new SbmasterQuery("qrySbmaster");

    qryGl.Select
    (
        qryGl.Source.Case()
            .When("AP").Then(qryVendSub.Select(qryVendSub.Company).Where(qryVendSub.Id == qryGl.Id))
            .When("AR").Then(qryCustSub.Select(qryCustSub.Company).Where(qryCustSub.Id == qryGl.Id))
            .When("SB").Then(qrySbmasterSub.Select(qrySbmasterSub.Company).Where(qrySbmasterSub.Id == qryGl.Id))
            .Else("")
            .End().As("COMPANY")
    );
    qryGl.LeftJoin(qryAcct).On(qryGl.Acct == qryAcct.Acct);

which gives me the following (obviously wrong!) output:

SELECT 
    [COMPANY] = CASE  
        WHEN 'AP' THEN MyProject.Com.Data.VendQuery 
        WHEN 'AR' THEN MyProject.Com.Data.CustQuery 
        WHEN 'SB' THEN MyProject.Com.Data.SbmasterQuery 
        ELSE '' 
    END   
FROM [GL] qryGl 
    LEFT JOIN [ACCT] qryAcct ON qryGl.[ACCT] = qryAcct.[ACCT]

Any help getting that subquery SQL into the case statement would be appreciated!!

Pretty new to EntitySpaces, so hoping it's just something simple I've missed...

Cheers

Answer 1

Looking at the methods available for the Then() method, I'm thinking it may not be possible.

1. There is a Then(object), which will be what your query is falling back onto and EntitySpaces is converting to a string literal and placing directly into the final query.
2. There is a Then(esQueryItem) which would work with a single column such as qryGl.Source.
3. Lastly there is a Then(esExpression). I'm a little foggy on this one, but I believe it is used to house select statement parameters, so it likely won't work for you either.

I think what you would need is a Then(DynamicQuery) or something similar.

Since EntitySpaces is open source now you could add that overload yourself and then modify the data provider(s) you use to work with the new sub query option. I've done a similar change to handle sub queries elsewhere and it wasn't really too bad of a task. The easiest part is actually the providers, because once you determine that you are dealing with a sub query you basically only have to call the top of the stack again for your new embedded query before continuing on.

However, from what I've seen above I would say if you want to avoid mucking about in the EntitySpaces code you will probably need to fall back to a hardcoded query. Mike from EntitySpaces always recommended a database view or stored procedure for custom loads like that.

Also: keep in mind if you create manual queries on the client side you can still avoid hard coding much of the query by using [TableName]Metadata.ColumnNames.[ColumnNameConstant].