Search code examples
joinsimple.data

Predefining joins in Simple.Data

I have a table Users with a foreign key to another table Department. The User object has a POCO for the department. I can select a user with the department-poco being populated with the following Simple.Data by-id query:

db.Users.With(db.Users.Departments.As("Department")).FindByUserID(userId);

Now I not only load users by id but also by name which results in the following query:

db.Users.With(db.Users.Departments.As("Department")).FindByUserName(userName);

To make it DRY I thought I might refactor the join into a variable and reuse it:

var userJoinedWithDepartment = db.Users.With(db.Users.Departments.As("Department"));
userJoinedWithDepartment.FindByUserID(userId);

However that causes trouble. At some other point I get an error about a already open transaction which leads me to believe that by defining that variable that already executes something in the background. I can't see a SQL statement being executed.

Is there a way to get around that and reuse the definitions for joins?

Solution

  • The solution is not to make it a var but a dynamic.

    dynamic userJoinedWithDepartment = db.Users.With(db.Users.Departments.As("Department"));
userJoinedWithDepartment.FindByUserID(userId);