EF Linq查询与条件包括 [英] EF Linq query with conditional include
问题描述
所以我有以下的Linq查询:
So I have the following Linq query:
var member = (from mem in
context.Members.Include(m =>
m.MemberProjects.Select(mp => mp.Project))
where mem.MemberId == memberId
select mem).FirstOrDefault();
这将返回一个成员实体,一组具有Project子项的MemberProjects。我想限制MemberProjects只有那些Project项目的子项有一个属性
ProjectIdParent == null
。
This returns a Member entity, with a set of MemberProjects that have a Project child. I would like to limit the MemberProjects to only those for which the Project child has a property
ProjectIdParent == null
.
我的一个失败的尝试可能会使意图更清楚:
One of my failed attempts might make the intent clearer:
var member = (from mem in context.Members
.Include(m => m.MemberProjects
.Where(mp =>
mp.Project.ProjectIdParent == null)
.Select(proj => proj.Project))
where mem.MemberId == memberId
select mem).FirstOrDefault();
这当然是因为Where子句而引起无效的包含表达式。
This of course complains of an invalid Include expression because of the Where clause.
任何关于如何做到这一点的想法将是很棒的:)
Any thoughts on how to do this would be great :)
推荐答案
免责声明:我没有测试过这个。这只是一个想法。如果你让我知道结果,我会相应地更新。 (跳到更新部分获取测试解决方案)
DISCLAIMER: I havent tested this. This is just an idea. If you let me know the results, I will update this accordingly. (Skip to the update part for the tested solutions)
var member = (from mps in context.MemberProjects
.Include(m => m.Members)
.Include(m => m.Projects)
where mps.Project.ProjectIdParent == null
select mps)
.FirstOrDefault(mprojs => mprojs.Member.MemberId == memberId);
我还会使用类似 EFProfiler ,以确保生成的查询不会离开理智的境界。
I'd also analyze the queries using something like EFProfiler to make sure the generated queries dont leave the realm of sanity.
你可以还要看看这篇文章由Jimmy Bogard发表了许多与ORM的关系。
You can also take a look at this post by Jimmy Bogard on Many to Many relationships with ORMs.
更新
我为EF 6.1提出了多项测试解决方案0.3。我的Edmx如下所示:
I came up with multiple tested solutions for this with EF 6.1.3. My Edmx looked like below:
设置数据如下所示:
我能够运行下面的代码来正确获取MemberFive
I was able to run code below to get the MemberFive correctly
var member = context.Members.FirstOrDefault
(m => m.MemberId == memberId
&& m.Projects.Any(p => p.ProjectParentId == null));
生成的SQL如下所示:
The generated SQL looked like this:
SELECT TOP (1) [Extent1].[MemberId] AS [MemberId],
[Extent1].[MemberName] AS [MemberName]
FROM [dbo].[Members] AS [Extent1]
WHERE ([Extent1].[MemberId] = 1)
AND (EXISTS (SELECT 1 AS [C1]
FROM (SELECT [MemberProjects].[MemberId] AS [MemberId],
[MemberProjects].[ProjectId] AS [ProjectId]
FROM [dbo].[MemberProjects] AS [MemberProjects])
AS [Extent2]
INNER JOIN [dbo].[Projects] AS [Extent3]
ON [Extent3].[ProjectId] = [Extent2].[ProjectId]
WHERE ([Extent1].[MemberId] = [Extent2].[MemberId])
AND ([Extent3].[ProjectParentId] IS NULL)))
如果你不喜欢生成的查询,你可以使用这个:
If you dont like the generated query you can use this:
var memberQuery = @"Select M.* from Members M
inner join MemberProjects MP on M.MemberId = Mp.ProjectId
inner join Projects P on MP.ProjectId = P.ProjectId
where M.MemberId = @MemberId and P.ProjectParentId is NULL";
var memberParams = new[]
{
new SqlParameter("@MemberId", 1)
};
var member3 = context.Members.SqlQuery(memberQuery, memberParams)
.FirstOrDefault();
后来一直在20ms内返回,另一个在60ms左右徘徊(如果对您很重要)。
The later consistently returned under 20ms vs the other one hovered around 60ms (if that matters to you).
我希望这有助于。
这篇关于EF Linq查询与条件包括的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!