SelectMulti API - with Alias


I am just wondering if it is possible to use same table in a query multiple times and, get records respectively?

For example :

var q = db.From<Employee>()
    .Join<Employee, Employee>();

var results = db.SelectMulti<Employee, Employee>(q);

foreach (var tuple in results)
    Employee emp = tuple.Item1;
    Employee empResponsible = tuple.Item2;

Note: When I try the above, it gives same first record deserialized for both.

No OrmLite typed SqlExpression doesn’t support multiple self table joins.

Whilst SqlExpression doesn’t include Typed APIs to allow multiple self joins with custom aliases, you can use CustomJoin to specify multiple self-joins instead.

We use an empty EOT column to specify the table delimiter, so you can still get SelectMulti like functionality by selecting it into a Tuple<T1,T2> with:

var q = db.From<Sale>()
    .CustomJoin("LEFT JOIN Contact seller on (Sale.SellerId = seller.Id)")
    .CustomJoin("LEFT JOIN Contact buyer on (Sale.BuyerId = buyer.Id)"
    .Where(x => x.TenantId == tenantId);
    .Select("seller.*, 0 EOT, buyer.*");

var saleContacts = db.Select<Tuple<Contact,Contact>>(q);

It’s nice to know, there is a workaround for this functionality.

But if it will be available to query Typed expression with table aliases, and get deserialized back, it will be perfect way.

Thank you

For anyone else who stumbles across this, an almost-completely-strongly-typed solution is possible, using a pattern like what I apply in the sample below, by using the TableAlias feature.

var qry = Db.From<OfferingTbl>(Db.TableAlias("o"))
				.Join<OfferingTbl, DealAgentTbl>((o, da) => Sql.TableAlias(o.DealAgentId, "o") == da.MyCustomUserAuthId, Db.TableAlias("da"))
				.Join<DealAgentTbl, MyCustomerUserAuthTbl>((da, damua) => Sql.TableAlias(da.MyCustomUserAuthId, "da") == damua.Id, Db.TableAlias("damua"))
				.Join<DealAgentTbl, DealTeamTbl>((da, dt) => Sql.TableAlias(da.DealTeamId, "da") == dt.Id, Db.TableAlias("dt"))
				.Join<OfferingTbl, AddressTbl>((o, a) => Sql.TableAlias(o.PrimaryAddressId, "o") == a.Id)
				.LeftJoin<OfferingTbl, MyCustomerUserAuthTbl>((o, pcmua) => Sql.TableAlias(o.PrimaryContactId, "o") == pcmua.Id, Db.TableAlias("pcmua"))
				.Where(o => o.Uid == request.OfferingUid)
		var tuple = Db.SelectMulti<OfferingTbl, DealAgentTbl, MyCustomerUserAuthTbl, DealTeamTbl, AddressTbl, MyCustomerUserAuthTbl>(
			new[] { "o.*", "da.*", "damua.*", "dt.*", "Address.*", "pcmua.*" }
1 Like