请问有没有人能够给我一个使用LINQ/lambda表达式执行左连接操作的示例?
DefaultIfEmpty
。Dim q = From e In db.Employees _
Group Join o In db.Orders On e Equals o.Employee Into ords = Group _
From o In ords.DefaultIfEmpty _
Select New With {e.FirstName, e.LastName, .Order = o}
我尝试复制著名的左连接,其中b键为空,我得到的结果是这个扩展方法(稍加想象,您可以修改它以使其成为左连接):
public static class extends
{
public static IEnumerable<T> LefJoinBNull<T, TKey>(this IEnumerable<T> source, IEnumerable<T> Target, Func<T, TKey> key)
{
if (source == null)
throw new ArgumentException("source is null");
return from s in source
join j in Target on key.Invoke(s) equals key.Invoke(j) into gg
from i in gg.DefaultIfEmpty()
where i == null
select s;
}
}
IQueryable<aspnet_UsersInRole> q = db.aspnet_Roles
.Select(p => p.aspnet_UsersInRoles
.SingleOrDefault(x => x.UserId == iduser));
将为您提供来自asp.net成员资格的角色列表,其中包含与指定用户(iduser键)不匹配的空值
OuterCollection.SelectMany() 与 InnerCollection.DefaultIfEmpty()结合使用。您可以在“C# 语句”模式下使用 LINQPad运行以下内容。
var teams = new[]
{
new { Id = 1, Name = "Tigers" },
new { Id = 2, Name = "Sharks" },
new { Id = 3, Name = "Rangers" },
};
var players = new[]
{
new { Name = "Abe", TeamId = 2},
new { Name = "Beth", TeamId = 4},
new { Name = "Chaz", TeamId = 1},
new { Name = "Dee", TeamId = 2},
};
// SelectMany generally aggregates a collection based upon a selector: from the outer item to
// a collection of the inner item. Adding .DefaultIfEmpty ensures that every outer item
// will map to something, even null. This circumstance makes the query a left outer join.
// Here we use a form of SelectMany with a second selector parameter that performs an
// an additional transformation from the (outer,inner) pair to an arbitrary value (an
// an anonymous type in this case.)
var teamAndPlayer = teams.SelectMany(
team =>
players
.Where(player => player.TeamId == team.Id)
.DefaultIfEmpty(),
(team, player) => new
{
Team = team.Name,
Player = player != null ? player.Name : null
});
teamAndPlayer.Dump();
// teamAndPlayer is:
// {
// {"Tigers", "Chaz"},
// {"Sharks", "Abe"},
// {"Sharks", "Dee"},
// {"Rangers", null}
// }
在尝试这个的时候,我发现有时可以省略匿名类型实例化中对player
的空值检查。我认为这种情况是在使用LINQ-to-SQL访问数据库时出现的(而不是这里使用的数组,我认为这使它成为了LINQ-to-objects或其他什么)。我认为在LINQ-to-SQL中省略空值检查是可行的,因为查询被转换为SQL LEFT OUTER JOIN
,直接跳过将null与外部项连接的步骤。(请注意,匿名对象属性的值必须是可空的;因此,如果您想安全地包含一个int
,您需要像这样写:new { TeamId = (int?)player.TeamId }
。