OrderBy on a column from a joined table

[Tim Straub]

I am doing a table1.FindAllBy(Id: id) along with two joins.  I am trying to use the OrderBy(db.table2.column) to sort the data but I keep getting a "column not found" error since I am trying to order by a column fromone of the join tables (eg, table2) instead of the original table (table1).

I am guessing  I can switch to a FindAll, change the table order and then use .Where, but I was wondering if there was any way to order by a joined table/column or if that is something that is not currently supported.

Thanks,

-Tim

[Mark Rendle]

Hi Tim,

Could you please post the whole Simple.Data query block? It's hard to diagnose problems without the full code.

Thanks,

Mark

[Tim Straub]

Sorry, Mark, here you go.

            return db.CandidateClientVersionLicensingTestXREF.FindAllBy(CandidateId: candidateId)

                .Join(db.ClientVersionLicensingTestXREF).On(db.ClientVersionLicensingTestXREF.ClientVersionLicensingTestXREFId == db.CandidateClientVersionLicensingTestXREF.ClientVersionLicensingTestXREFId)

                .Join(db.LicensingTest).On(db.LicensingTest.LicensingTestId == db.ClientVersionLicensingTestXREF.LicensingTestId)

                .Select(db.CandidateClientVersionLicensingTestXREF.CandidateClientVersionLicensingTestXREFId

                    , db.CandidateClientVersionLicensingTestXREF.ClientVersionLicensingTestXREFId

                    , db.CandidateClientVersionLicensingTestXREF.CompletedPriorToHire

                    , db.CandidateClientVersionLicensingTestXREF.CompletionDate

                    , db.ClientVersionLicensingTestXREF.Order

                    , db.LicensingTest.LicensingTestId

                    , db.LicensingTest.LicensingTestName

                    , db.LicensingTest.ImageLocation)

                //.OrderBy(db.ClientVersionLicensingTestXREF.Order)

               .ToList<CandidateLicensingTest>();

I was able to get around it by doing the following:

(1) Remove the FindAllBy

(2) Add a Where statement

(3) Change the order of the tables

Thanks,

-Tim

[Mark Rendle]

Thanks. I'll look into this, there should be no semantic difference between the FindAllBy and the FindAll().Where(), so that's an interesting bug.

Cheers,

Mark

[Tim Straub]

Thanks Mark.  BTW, here was my solution / work-around.

            return db.ClientVersionLicensingTestXREF

                .Join(db.CandidateClientVersionLicensingTestXREF).On(db.ClientVersionLicensingTestXREF.ClientVersionLicensingTestXREFId == db.CandidateClientVersionLicensingTestXREF.ClientVersionLicensingTestXREFId)

                .Join(db.LicensingTest).On(db.LicensingTest.LicensingTestId == db.ClientVersionLicensingTestXREF.LicensingTestId)

                .Where(db.CandidateClientVersionLicensingTestXREF.CandidateId == candidateId)

                .Select(db.CandidateClientVersionLicensingTestXREF.CandidateClientVersionLicensingTestXREFId

                    , db.CandidateClientVersionLicensingTestXREF.ClientVersionLicensingTestXREFId

                    , db.CandidateClientVersionLicensingTestXREF.CompletedPriorToHire

                    , db.CandidateClientVersionLicensingTestXREF.CompletionDate

                    , db.ClientVersionLicensingTestXREF.Order

                    , db.LicensingTest.LicensingTestId

                    , db.LicensingTest.LicensingTestName

                    , db.LicensingTest.ImageLocation)

                .OrderBy(db.ClientVersionLicensingTestXREF.Order)

                .ToList<CandidateLicensingTest>();