将左连接SQL转换为Linq(方法语法)

Question

这是我在这里的第一个帖子，所以如果我有任何错误，请告诉我，我会修复它。

我正在努力转换一个简单的SQL语句与左连接，到LINQ语句(方法语法)。我不能使用Linquer，因为这是一个。net Core 5.0 MVC项目。

假设我有两个表:

dbo.OrganisationChannel (Id, OrganisationId, ChannelId)
dbo.Channel (Id, ChannelName, ChannelUrl)

我想显示一个组织目前没有的所有渠道。

这里是正确的SQL查询

SELECT c.Id,  c.ChannelName, c.ChannelUrl
FROM dbo.Channel c
LEFT JOIN dbo.OrganisationChannel oc ON  c.Id = oc.ChannelId
WHERE oc.ChannelId IS NULL OR oc.OrganisationId <> 1

然而，相应的.GroupJoin和.SelectMany让我很困惑。我找不到合适的地方添加WHERE子句:

var groupItems = db.Channel
.GroupJoin(
db.OrganisationChannel,
c => c.Id,
oc => oc.ChannelId,
(c, oc) => new { c, oc })
.SelectMany(
x => x.oc.DefaultIfEmpty(),
(chan, orgChan) => new
{
Id = chan.c.Id,
ChannelName = chan.c.ChannelName,
ChannelUrl = chan.c.ChannelUrl,
IsActive = chan.c.IsActive,
}
);

我很感激任何帮助在这里，谢谢!

如果

Answer 1

使用LEFT JOIN的方法语法是一场噩梦。如果您确实需要方法语法，请安装shaper并单击"转换为方法链"。但是我不建议这样做，否则查询将变得不可维护。

你的查询语法很简单

var query = 
from c in db.Channel
join oc in db.OrganisationChannel on c.Id equals oc.ChannelId into gj
from oc in gj.DefaultIfempty()
where (int?)oc.ChannelId == null || oc.OrganisationId != 1
select new 
{
c.Id,
c.ChannelName, 
c.ChannelUrl
};

Answer 2

您可以使用LeftJoin扩展方法:

public static IQueryable<TResult> LeftJoin<TResult, Ta, Tb, TKey>(this IQueryable<Ta> TableA, IEnumerable<Tb> TableB, Expression<Func<Ta, TKey>> outerKeySelector, Expression<Func<Tb, TKey>> innerKeySelector, Expression<Func<JoinIntermediate<Ta, Tb>, Tb, TResult>> resultSelector)
{
return TableA.GroupJoin(TableB, outerKeySelector, innerKeySelector, (a, b) => new JoinIntermediate<Ta, Tb> { Value = a, ManyB = b }).SelectMany(intermediate => intermediate.ManyB.DefaultIfEmpty(), resultSelector);
}
public class JoinIntermediate<Ta, Tb>
{
public Ta Value { get; set; }
internal IEnumerable<Tb> ManyB { get; set; }
}

它的用法类似于Join扩展方法，但将执行左连接而不是常规连接。然后，您可以在调用LeftJoin之后将您的调用添加到Where方法。

Answer 3

使用以下查询代替lambda表达式

from left in lefts
join right in rights on left equals right.Left into leftRights
from leftRight in leftRights.DefaultIfEmpty()
select new { }

Answer 4

查看此url https://dotnettutorials.net/lesson/left-outer-join-in-linq/

也是我的工作代码示例:

UserApiKeys
.Where(w => w.AppID == AppID && w.IsActive)
.Join(
UserApiApplications,
keys => keys.AppID,
apps => apps.AppID,
(keys, apps) => new { UserApiKeys = keys, UserApiApplications = apps}
)
.OrderByDescending(d => (d.UserApiKeys.ExpirationDate ?? DateTime.MaxValue))
.Select(s => new {
ApiKey = s.UserApiKeys.ApiKey, 
IsActive = s.UserApiKeys.IsActive, 
SystemName = s.UserApiKeys.SystemName,
ExpirationDate = (s.UserApiKeys.ExpirationDate == null)
? "Newer Expires"
: s.UserApiKeys.ExpirationDate.ToString(),
s.UserApiApplications
})
.ToList()

另外，参考@nalka关于扩展方法使用的帖子:

NotificationEvents
.Where(w => w.ID == 123)
.LeftJoin(
Events, 
events => events.EventID, ev => ev.EventID, 
(events, ev) => new { NotificationEvents = events, Events = ev }
);