Ormlite 2 references to the same table. Join issue

I have address1 and address2, I want to be able to join and bring the entire relation of each address.
Sometimes I need one address but sometimes I need both.
For some reason, I’m not getting any data in the address object.

  1. why is it empty?
  2. how can I distinguish between the joins?


var frm = db.From<User>().Join<Address>();
var rowsC = db.Select<User>(frm);

public class User
    public long Id { get; set; }
    public string Name { get; set; }
    public DateTime CreatedDate { get; set; }
    public long? Address1Id {get;set;}
    public Address Address1 {get;set;}
    public long? Address2Id {get;set;}
    public Address Address2 {get;set;}
    public override string ToString() => Name;

Please refer to the example in the docs, you always need to use LoadSelect to load references, which have an optional include parameter to specify which references to load.

LoadSelect will bring both. Can select only address1?

If you don’t want the predefined behavior you’d need to select the main table without references in addition to a custom select to select the reference you want.

  1. If LoadSelete brings all the relations, what’s the point in using join? anyway, it will bring all.

  2. Can you please explain what are you referring to? the example that I posted is the same as the docs.

  1. Maybe some more context will help.
    I have a page and I want to present 100 users in a list, the User entity has 15 references. the list should present only 8 references.
    I want to do it in one query, so I thought use Join to do that, but when I use LoadSelect, either way, it will load all.
    How can I run one query with left joins and only 8 specific references?


LoadSelect loads an entity with references, that’s specifically what the API does, the entity references on your model is so it can be used by Load* APIs, the APIs do let you specify which references to include, but it’s not done with a single query, it requires an additional query per reference type, but if you don’t want the Load* APIs behavior you wouldn’t use it.

If you wanted to use a single custom query with joins instead you can use TableAlias to reference the same table multiple times but you’d then need to use a custom query like SelectMulti to capture the custom resultset of your query.

I think selectmulti is what I’m after.
Just to be sure. the following query return 11 rows, it is all done in one query?

  var from = db.From<ResearchEntity>().LeftJoin<OrgEntity>();
  var data = db.SelectMulti<ResearchEntity, OrgEntity>(from);


Yes you can view the generated SQL using a BeforeExecFilter, e.g: