SelectMulti API - with Alias

ServiceStack.OrmLite
1

Hello,

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.

2

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

3

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);
saleContacts.PrintDump();
4

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

5

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>(
			qry,
			new[] { "o.*", "da.*", "damua.*", "dt.*", "Address.*", "pcmua.*" }
		)
		.Single();
1 Like