gpt4 book ai didi

servicestack - 如何使用 ServiceStack OrmLite 两次 LeftJoin 到同一个表?

转载 作者:行者123 更新时间:2023-12-05 01:34:26 26 4
gpt4 key购买 nike

我有如下所示的表结构:

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:
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过滤结果(在数据库端)我的代码如下所示
public List<TOut> Exec<TIn, TOut>(SqlExpression<TIn> exp) where TIn : IHaveTenantId
{
exp.Where(x => x.TenantId == _tenantId);
return _conn.Select<TOut>(exp);
}

Sale 的 poco 如下所示:
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 语法:
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));

我不知道如何多次加入同一个表,并且每次加入都有一个别名(例如,将加入联系人作为“卖家”,因此我可以选择卖家。名字,买家。名字),我不想使用参数化的原始 sql。

OrmLite 可以做到这一点吗?

最佳答案

支持 typed JOIN aliases were added in v4.0.62 ,例如:

var q = db.From<Sale>()
.LeftJoin<ContactIssue>((s,c) => s.SellerId == c.Id, db.JoinAlias("seller"))
.LeftJoin<ContactIssue>((s,c) => s.BuyerId == c.Id, db.JoinAlias("buyer"))
.Select<Sale, ContactIssue>((s,c) => new {
s,
BuyerFirstName = Sql.JoinAlias(c.FirstName, "buyer"),
BuyerLastName = Sql.JoinAlias(c.LastName, "buyer"),
SellerFirstName = Sql.JoinAlias(c.FirstName, "seller"),
SellerLastName = Sql.JoinAlias(c.LastName, "seller"),
});

在 v4.0.62 之前,您可以继续使用 Typed SqlExpression为此使用自定义 SQL,例如:
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");

这样做的好处是它仍然保留了一个 Typed API,您可以在其中添加额外的过滤器,例如全局 TenantId 过滤器,例如:
q.Where(x => x.TenantId == tenantId);

然后将其投影到您的自定义 POCO 中:
var sales = db.Select<SaleView>(q);

新款 CustomJoin API 可从 获得v4.0.37+ 那是 now available on MyGet .

关于servicestack - 如何使用 ServiceStack OrmLite 两次 LeftJoin 到同一个表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28019508/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com