Simple.Data - how to bi-drectionally join on the same table twice

Question

I know I'm close with this one...

My structure is:

I have some companies Each company has a primary user (account) And a list of all users (accounts).

I've implemented this in the db as a companies table that has "Primary" as a foreign key to the Accounts table, and the Accounts table has a CompanyId which is a foreign key to the Companies table.

So there's only ever one primary user, and each user is associated with one company.

I want to retrieve a list of all companies, and plonk them in a c# object. What I have so far is:

public IEnumerable<Company> GetAllCompaniesList()
    {
        var allData = database.Companies.All()
            .Join(database.Accounts).On(database.Accounts.Id == database.Companies.Primary)
            .Join(database.Accounts).On(database.Accounts.CompanyId == database.Companies.Id)
            .ToList<Company>();
        return allData;
    }

and it works in my tests using an in-memory adapter with the relevant keys set up, but not in the real version, crashing out with

The objects "dbo.Accounts" and "dbo.Accounts" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

I think this means I need to name each join (e.g. to make sql like "join a on a = b as c"), but I can't find the syntax to do that. Anyone know?

Answer 1

You can alias table names using the .As method. In addition to that, the Join method has an optional second out parameter which puts the reference to the aliased table in a dynamic variable; it just makes referring to the table easier.

So try this:

public IEnumerable<Company> GetAllCompaniesList()
{
    dynamic primary;
    var allData = database.Companies.All()
        .Join(database.Accounts.As("PrimaryAccounts"), out primary)
            .On(primary.Id == database.Companies.Primary)
        .Join(database.Accounts)
            .On(database.Accounts.CompanyId == database.Companies.Id)
        .ToList<Company>();
    return allData;
}

That should work fine.