Joining a table cause "Column 'Id' in where clause is ambiguous"

1

Hi.
Im trying to add simple row level security and join user table the following way:

var query = 
                db.From<ResearchEntity>().Where(x => x.Id == id);
                query.Join<AppUserAuth>((x, y) => x.CreatedBy.Id == int.Parse(session.UserAuthId));
                var data = await db.LoadSelectAsync(query);

I am getting from MySQL: “Column ‘Id’ in where clause is ambiguous”
I have tried to add table options with alias with no success…
What am I missing?
Thanks

2

You can’t query on nested columns, e.g x.CreatedBy.Id i.e. you can only query the columns of the table you’re joining as you would in SQL.

3

I have on ResearchEntity the fields CreatedBy and LastUpdatedBy
I want to join the CreatedBy and do where clause on the CreatedBy.Id.
What are my options?
Thanks

4

With OrmLite you’d structure your query how you would in SQL, than Type it. There’s no magic query conversions, it’s essentially just a Typed SQL query with any custom Select projections at the end. Querying a field on a joined table is the same as you would do it in SQL.