How can I use With and LeftJoin in the same Simple.Data Query?

Question

I am using Simple.Data to try and build a query from our Database. Basically the results need to include certain columns from a Users table, as well as multiple rows from an Addresses table, as well as one specific column from an Entity Table.

Database.Users.FindAll(query)
              .With(Database.Users.Addresses.As("Address"))
              .LeftJoin(Database.Entities).On(Database.Users.ParentEntityId == Database.Entities.EntityID)
              .Select(Database.Users.UserID,
                     Database.Users.FirstName,
                     Database.Users.LastName,
                     Database.Entities.Name.As("ParentEntityName"));

The odd thing is if I don't have the With statement in my query this works fine (though I would be missing the Address), but with it I end up getting "The given key was not present in the dictionary".

Also should note that including the With and excluding the Database.Entities.Name.As("ParentEntityName") works as well, but I'm missing that value.

Any Ideas?

Answer 1

Turns out LeftJoin and With don't play nice together when you specify what you want in the Select statement. I ended up getting around this issue using a WithOne statement with a LeftJoin to get the entire row from the table, then just got the value from there - not the most elegant fix but it works.

dynamic entity;
Database.Users.FindAll(query)
              .With(Database.Users.Addresses.As("Address"))
              .LeftJoin(Entities.As("Entity"), out entity).On(Users.ParentEntityId == entity.EntityID)
              .WithOne(entity)
              .Select(Database.Users.UserID,
                      Database.Users.FirstName,
                      Database.Users.LastName);

Then when I turned the results to a list I got the value from "Entity" in the SimpleQuery as a SimpleRecord, which contained name which was the value I wanted.