如何使用ServiceStack OrmLite两次两次将LeftJoin加入同一张表? [英] How to LeftJoin to the same table twice using ServiceStack OrmLite?
问题描述
我的表格结构如下:
table Tenant: Id[PK], etc
table Contact: Id[PK], FirstName, LastName etc
table Sale: Id[PK], TenantId[FK], SellerId[FK], BuyerId[FK], etc
SellerId is a FK to Contact.Id
BuyerId is a FK to Contact.Id
TenantId is a FK to Tenant.Id
我想使用OrmLite生成类似于以下内容的SQL:
I want to use OrmLite to generate SQL similar to below:
select sale.*
, buyer.FirstName 'BuyerFirstName'
, buyer.LastName 'BuyerLastName'
, seller.FirstName 'SellerFirstName'
, seller.LastName 'SellerLastName'
from sale
left join
contact seller
on sale.SellerId = seller.Id
left join
contact buyer
on sale.BuyerId = buyer.Id
where tenantId = 'guid' -- this is being filtered at a global level
因为我想要一个强类型的全局过滤器,以按tenantId(在数据库方面)过滤出结果,所以我的代码如下所示
Because I want to have a strongly typed global filter to filter out result by tenantId (on database side) I have code looks like below
public List<TOut> Exec<TIn, TOut>(SqlExpression<TIn> exp) where TIn : IHaveTenantId
{
exp.Where(x => x.TenantId == _tenantId);
return _conn.Select<TOut>(exp);
}
销售的主题如下:
public class Sale : IHaveTenantId
{
public Guid Id { get; set; }
[ForeignKey(typeof(Contact), OnDelete = "CASCADE")]
public Guid BuyerId { get; set; }
[ForeignKey(typeof(Contact), OnDelete = "CASCADE")]
public Guid SellerId { get; set; }
//etc
}
我正尝试使用如下所示的强类型LeftJoin语法:
And I'm trying to use strongly typed LeftJoin syntax like below:
public class SaleView
{
public Guid Id { get; set; }
public string BuyerFirstName { get; set; }
public string SellerLastName { get; set; }
//etc
}
var result = Exec<SaleView, Sale>(_conn
.From<Sale>()
.LeftJoin<Contact>((sale, seller) => sale.SellerId == seller.Id)
.LeftJoin<Contact>((sale, buyer) => sale.BuyerId == buyer.Id));
我无法弄清楚如何多次联接同一张表,并且每次联接都具有别名(例如,左联接联系人为卖方",因此我可以选择"seller.FirstName","buyer.FirstName"),但我却没有想要使用参数化的原始sql.
I couldn't figure out how to join the same table multiple times and have an alias per join (e.g. left join contact as 'seller', hence I can select seller.FirstName, buyer.FirstName) and I don't want to use parameterised raw sql.
OrmLite完全可以吗?
Is this possible at all with OrmLite?
推荐答案
对
在v4.0.62之前,您可以继续使用带有自定义SQL的Typed Prior to v4.0.62 you can continue to use a Typed 这样做的好处是它仍然保留Typed API,您可以在其中添加其他过滤器,例如全局TenantId过滤器,例如: The benefit of which is that it still leaves a Typed API where you can add additional filters like a global TenantId filter, e.g: 然后使用以下命令将其投影到您的自定义POCO中: And then project it into your Custom POCO with: v4.0.37 + 中提供了新的 The new 这篇关于如何使用ServiceStack OrmLite两次两次将LeftJoin加入同一张表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!SqlExpression
,例如:SqlExpression
with custom SQL for this, e.g: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)")
.Select(@"Sale.*
, buyer.FirstName AS BuyerFirstName
, buyer.LastName AS BuyerLastName
, seller.FirstName AS SellerFirstName
, seller.LastName AS SellerLastName");
q.Where(x => x.TenantId == tenantId);
var sales = db.Select<SaleView>(q);
CustomJoin
API,该API 现在在MyGet上可用.CustomJoin
API is available from v4.0.37+ that's now available on MyGet.