强制linq执行内连接

I'm trying to force Linq to preform an inner join between two tables. I'll give an example.

我试图迫使Linq在两个表之间执行内连接。我举个例子。

CREATE TABLE [dbo].[People] (
   [PersonId] [int] NOT NULL,
   [Name] [nvarchar](MAX) NOT NULL,
   [UpdatedDate] [smalldatetime] NOT NULL
   ... Other fields ...
)

CREATE TABLE [dbo].[CompanyPositions] (
   [CompanyPositionId] [int] NOT NULL,
   [CompanyId] [int] NOT NULL,
   [PersonId] [int] NOT NULL,
   ... Other fields ...
)

Now I'm working with unusual database as there's a reason beyond my control for people to be missing from the People table but have a record in CompanyPositions. I want to filter out CompanyPositions with missing People by joining the tables.

现在我正在使用不寻常的数据库，因为我无法控制人们在People表中丢失但在CompanyPositions中有记录的原因。我想通过加入表来过滤掉缺少人员的CompanyPositions。

return (from pos in CompanyPositions
        join p in People on pos.PersonId equals p.PersonId
        select pos).ToList();

Linq sees this join as redundant and removes it from the SQL it generates.

Linq认为此连接是冗余的，并将其从它生成的SQL中删除。

SELECT 
[Extent1].[CompanyPositionId] AS [CompanyPositionId], 
[Extent1].[CompanyId] AS [CompanyId], 
.... 
FROM  [dbo].[CompanyPositions] AS [Extent1]

However it's not redundant in my case. I can fix it like this

然而，在我的情况下，这并不是多余的。我可以像这样解决它

// The min date check will always be true, here to force linq to perform the inner join
var minDate = DateTimeExtensions.SqlMinSmallDate;

return (from pos in CompanyPositions
        join p in People on pos.PersonId equals p.PersonId
        where p.UpdatedDate >= minDate
        select pos).ToList();

However this now creates a needless where clause in my SQL. As a purest I'd like to remove this. Any idea's or does the current database design tie my hands?

但是现在这在我的SQL中创建了一个不必要的where子句。作为一个最纯粹的我想删除它。任何想法或当前的数据库设计是否与我联系？

4 个解决方案

#1

Since PersonId is declared NOT NULL (and I assume it is declared as an FK to People) then I'm not sure how you could have a CompanyPosition with a person that is not assigned; and Linq can't see how you can eiter, which is why as you have observed Linq considers the join redundant.

由于PersonId被声明为NOT NULL（并且我假设它被声明为人们的FK），所以我不确定如何将一个CompanyPosition与未分配的人一起;而Linq看不出你怎么能这样做，这就是为什么你观察到Linq认为连接是多余的。

#2

If you're using LinqToSql, you can use LoadWith similar to this:

如果您使用的是LinqToSql，则可以使用与此类似的LoadWith：

var context = new MyDataContext();
var options = new DataLoadOptions();
options.LoadWith<People>(x => x.CompanyPositions);
context.LoadOptions = options;

#3

I don't know how to force linq to use a join. But the following statment should give you the required result.

我不知道如何强制linq使用连接。但是以下陈述应该给你所需的结果。

return (from pos in CompanyPositions
        where (p in People select p.PersonId).Contains(pos.PersonId)
        select pos).ToList();

#4

ClientSide transformation:

ClientSide转换：

(
from pos in CompanyPositions
join p in People on pos.PersonId equals p.PersonId
select new {pos, p}
).ToList().Select(x => x.pos);

More direct filtering:

更直接的过滤：

from pos in CompanyPositions
where pos.People.Any()
select pos

#1

#2