在 LINQ 中向实体左联接
本文关键字:实体 LINQ | 更新日期: 2023-09-27 18:35:10
我正在尝试将LINQ用于实体。
我有以下问题:我希望它这样做:
SELECT
T_Benutzer.BE_User
,T_Benutzer_Benutzergruppen.BEBG_BE
FROM T_Benutzer
LEFT JOIN T_Benutzer_Benutzergruppen
ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID
我得出的最接近的事情是:
var lol = (
from u in Repo.T_Benutzer
//where u.BE_ID == 1
from o in Repo.T_Benutzer_Benutzergruppen.DefaultIfEmpty()
// on u.BE_ID equals o.BEBG_BE
where (u.BE_ID == o.BEBG_BE || o.BEBG_BE == null)
//join bg in Repo.T_Benutzergruppen.DefaultIfEmpty()
// on o.BEBG_BG equals bg.ID
//where bg.ID == 899
orderby
u.BE_Name ascending
//, bg.Name descending
//select u
select new
{
u.BE_User
,o.BEBG_BG
//, bg.Name
}
).ToList();
但这会产生与内部连接相同的结果,而不是左连接.
此外,它创建了这个完全疯狂的 SQL:
SELECT
[Extent1].[BE_ID] AS [BE_ID]
,[Extent1].[BE_User] AS [BE_User]
,[Join1].[BEBG_BG] AS [BEBG_BG]
FROM [dbo].[T_Benutzer] AS [Extent1]
CROSS JOIN
(
SELECT
[Extent2].[BEBG_BE] AS [BEBG_BE]
,[Extent2].[BEBG_BG] AS [BEBG_BG]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN [dbo].[T_Benutzer_Benutzergruppen] AS [Extent2]
ON 1 = 1
) AS [Join1]
WHERE [Extent1].[BE_ID] = [Join1].[BEBG_BE]
OR [Join1].[BEBG_BE] IS NULL
ORDER BY [Extent1].[BE_Name] ASC
如何在 LINQ-2 实体中以以下方式进行左联接:另一个人仍然可以理解该代码中正在做什么?
最优选的是生成的 SQL 如下所示的位置:
SELECT
T_Benutzer.BE_User
,T_Benutzer_Benutzergruppen.BEBG_BE
FROM T_Benutzer
LEFT JOIN T_Benutzer_Benutzergruppen
ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID
啊,我自己知道了。
LINQ-2-entities.
的怪癖和夸克这看起来最容易理解:
var query2 = (
from users in Repo.T_Benutzer
from mappings in Repo.T_Benutzer_Benutzergruppen
.Where(mapping => mapping.BEBG_BE == users.BE_ID).DefaultIfEmpty()
from groups in Repo.T_Benutzergruppen
.Where(gruppe => gruppe.ID == mappings.BEBG_BG).DefaultIfEmpty()
//where users.BE_Name.Contains(keyword)
// //|| mappings.BEBG_BE.Equals(666)
//|| mappings.BEBG_BE == 666
//|| groups.Name.Contains(keyword)
select new
{
UserId = users.BE_ID
,UserName = users.BE_User
,UserGroupId = mappings.BEBG_BG
,GroupName = groups.Name
}
);
var xy = (query2).ToList();
删除.DefaultIfEmpty()
,你会得到一个内部连接.
这就是我一直在寻找的。
您可以在此处阅读我为 LINQ 中的联接撰写的文章
var query =
from u in Repo.T_Benutzer
join bg in Repo.T_Benutzer_Benutzergruppen
on u.BE_ID equals bg.BEBG_BE
into temp
from j in temp.DefaultIfEmpty()
select new
{
BE_User = u.BE_User,
BEBG_BG = (int?)j.BEBG_BG// == null ? -1 : j.BEBG_BG
//, bg.Name
}
以下是使用扩展方法的等效项:
var query =
Repo.T_Benutzer
.GroupJoin
(
Repo.T_Benutzer_Benutzergruppen,
x=>x.BE_ID,
x=>x.BEBG_BE,
(o,i)=>new {o,i}
)
.SelectMany
(
x => x.i.DefaultIfEmpty(),
(o,i) => new
{
BE_User = o.o.BE_User,
BEBG_BG = (int?)i.BEBG_BG
}
);
也许我稍后会来回答,但现在我正面临这个问题......如果有帮助,还有另一种解决方案(我解决它的方式)。
var query2 = (
from users in Repo.T_Benutzer
join mappings in Repo.T_Benutzer_Benutzergruppen on mappings.BEBG_BE equals users.BE_ID into tmpMapp
join groups in Repo.T_Benutzergruppen on groups.ID equals mappings.BEBG_BG into tmpGroups
from mappings in tmpMapp.DefaultIfEmpty()
from groups in tmpGroups.DefaultIfEmpty()
select new
{
UserId = users.BE_ID
,UserName = users.BE_User
,UserGroupId = mappings.BEBG_BG
,GroupName = groups.Name
}
);
顺便说一下,我尝试使用Stefan Steiger代码,它也有帮助,但它慢得要慢。
简单的方法是使用let
关键字。这对我有用。
from AItem in Db.A
let BItem = Db.B.Where(x => x.id == AItem.id ).FirstOrDefault()
where SomeCondition
select new YourViewModel
{
X1 = AItem.a,
X2 = AItem.b,
X3 = BItem.c
}
这是左联接的模拟。如果 B 表中的每个项目都与 A 项目不匹配,则 BItem 返回 null
Lambda 语法映射
查询语法解决方案很好,但在某些情况下,lambda 语法解决方案更可取(例如,处理表达式树)。LinqPad 可以方便地将查询语法转换为映射查询的 lambda 语法。通过稍作调整,我们最终得到:
// Left-join in query syntax (as seen in several other answers)
var querySyntax =
from o in dbcontext.Outer
from i in dbcontext.Inner.Where(i => i.ID == o.ID).DefaultIfEmpty()
select new { o.ID, i.InnerField };
// Maps roughly to:
var lambdaSyntax = dbcontext.Outer
.SelectMany(
o => dbcontext.Inner.Where(i => i.ID == o.ID).DefaultIfEmpty(),
(o, i) => new { o.ID, i.InnerField }
);
因此,GroupJoin
在 lambda 语法中实际上是多余的。官方dotnet/ef6
存储库的一个测试用例中也涵盖了 SelectMany
+ DefaultIfEmpty
映射。请参阅SelectMany_with_DefaultIfEmpty_translates_into_left_outer_join
。
SelectMany
和其他JOIN
这里要带走的最重要的事情是,在翻译 SQL JOIN
时,SelectMany
比Join
更通用。
对于内部联接,您甚至可以使用
SelectMany
而不是Join
。只需放下.DefaultIfEmpty()
.如果您还将
Where
放在SelectMany
中,则可以执行CROSS JOIN
。请参阅如何使用 LINQ to SQL 执行交叉联接?。
自定义扩展方法
使用上面的 Lambda 语句,我们可以在 lambda 语法中创建 Join
扩展方法的类似物:
public static class Ext
{
// The extension method
public static IQueryable<TResult> LeftOuterJoin<TOuter, TInner, TKey, TResult>(
this IQueryable<TOuter> outer, IQueryable<TInner> inner,
Expression<Func<TOuter, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TOuter, TInner, TResult>> resultSelector)
{
// Re-context parameter references in key selector lambdas.
// Will give scoping issues otherwise
var oParam = Expression.Parameter(
typeof(TOuter),
outerKeySelector.Parameters[0].Name
);
var iParam = Expression.Parameter(
typeof(TInner),
innerKeySelector.Parameters[0].Name
);
var innerLinqTypeArgs = new Type[]{ typeof(TInner) };
// Maps `inner.Where(i => outerKeySelector body == innerKeySelector body)`
var whereCall = Expression.Call(
typeof(Queryable), nameof(Queryable.Where), innerLinqTypeArgs,
// Capture `inner` arg
Expression.Constant(inner),
(Expression<Func<TInner, bool>>)Expression.Lambda(
SwapParams(
Expression.Equal(innerKeySelector.Body, outerKeySelector.Body),
new[] { iParam, oParam }
),
iParam
)
);
// Maps `(IEnumerable<TRight>)<Where Call>.DefaultIfEmpty()`
// Cast is required to get SelectMany to work
var dieCall = Expression.Convert(
Expression.Call(typeof(Queryable), nameof(Queryable.DefaultIfEmpty), innerLinqTypeArgs, whereCall),
typeof(IEnumerable<TInner>)
);
// Maps `o => <DefaultIfEmpty Call>`
var innerLambda = (Expression<Func<TOuter, IEnumerable<TInner>>>)Expression.Lambda(dieCall, oParam);
return outer.SelectMany(innerLambda, resultSelector);
}
// Core class used by SwapParams
private class ParamSwapper : ExpressionVisitor
{
public ParameterExpression Replacement;
// Replace if names match, otherwise leave alone.
protected override Expression VisitParameter(ParameterExpression node)
=> node.Name == Replacement.Name ? Replacement : node;
}
// Swap out a lambda's parameter references for other parameters
private static Expression SwapParams(Expression tgt, ParameterExpression[] pExps)
{
foreach (var pExp in pExps)
tgt = new ParamSwapper { Replacement = pExp }.Visit(tgt);
return tgt;
}
}
示例用法:
dbcontext.Outer
.LeftOuterJoin(
dbcontext.Inner, o => o.ID, i => i.ID,
(o, i) => new { o.ID, i.InnerField }
);
当然,它不会节省大量的类型,但我认为如果你来自SQL背景,它确实使意图更加清晰。
您不仅可以在实体中使用它,还可以在存储过程或其他数据源中使用它:
var customer = (from cus in _billingCommonservice.BillingUnit.CustomerRepository.GetAll()
join man in _billingCommonservice.BillingUnit.FunctionRepository.ManagersCustomerValue()
on cus.CustomerID equals man.CustomerID
// start left join
into a
from b in a.DefaultIfEmpty(new DJBL_uspGetAllManagerCustomer_Result() )
select new { cus.MobileNo1,b.ActiveStatus });
使用 linq//System.Linq 进行左联接
Test t = new Test();
//t.Employees is employee List
//t.EmployeeDetails is EmployeeDetail List
var result = from emp in t.Employees
join ed in t.EmployeeDetails on emp.Id equals ed.EDId into tmp
from final in tmp.DefaultIfEmpty()
select new { emp.Id, emp.Name, final?.Address };
foreach (var r in result)
{
Console.WriteLine($"Employee Id: {r.Id}, and Name: {r.Name}, and address is: {r.Address}");
}