Linq中的in和not in的使用方法

T-SQL语句:

select * from PayingRecords where ClientID='17787665-1d98-49e6-b254-a6a6553c4b42'
and ID not in
(select PayingRecordID from InvoicingRecordDetails where InvoicingRecordID in
(
select ID from InvoicingRecords where
ClientID='17787665-1d98-49e6-b254-a6a6553c4b42'
)
)

Linq语句:

 var query = from p in db.PayingRecords
where !(from i in db.InvoicingRecordDetails
where (from ii in db.InvoicingRecords
where ii.ClientID.Equals(paraRequest.ClientID)
select ii.ID).Contains(i.InvoicingRecordID)
select i.PayingRecordID
).Contains(p.ID)
//join td1 in db.DataAuthorities on p.DataAuthorityID equals td1.ID into p_td1
//from pp in p_td1.DefaultIfEmpty()
//join c in db.Clients on p.ClientID equals c.ID into c_join
//from tmp in c_join.DefaultIfEmpty()
where p.ClientID.Equals(paraRequest.ClientID)
select new PayingRecordResponse
{
ID = p.ID,
// DataAuthorityValue = pp.Value,
ClusterID = p.ClusterID,
ArrivingAmount = p.ArrivingAmount,
ClientID = p.ClientID,
// ClientName = tmp.Name,
ArrivingTime = p.ArrivingTime,
Description = p.Description,
UpdateTime = p.UpdateTime,
UpdateUser = p.UpdateUser,
CreateTime = p.CreateTime,
CreateUser = p.CreateUser
};

Linq生成的语句:

SELECT
[Extent1].[ClusterID] AS [ClusterID],
[Extent1].[ID] AS [ID],
[Extent1].[ArrivingAmount] AS [ArrivingAmount],
[Extent1].[ClientID] AS [ClientID],
[Extent1].[ArrivingTime] AS [ArrivingTime],
[Extent1].[Description] AS [Description],
[Extent1].[UpdateTime] AS [UpdateTime],
[Extent1].[UpdateUser] AS [UpdateUser],
[Extent1].[CreateTime] AS [CreateTime],
[Extent1].[CreateUser] AS [CreateUser]
FROM [dbo].[PayingRecords] AS [Extent1]
WHERE ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[InvoicingRecordDetails] AS [Extent2]
WHERE ( EXISTS (
SELECT
1 AS [C1]
FROM [dbo].[InvoicingRecords] AS [Extent3]
WHERE ([Extent3].[ClientID] = '17787665-1d98-49e6-b254-a6a6553c4b42')
AND ([Extent3].[ID] = [Extent2].[InvoicingRecordID])
)) AND ([Extent2].[PayingRecordID] = [Extent1].[ID])
)) AND ([Extent1].[ClientID] = '17787665-1d98-49e6-b254-a6a6553c4b42')
上一篇:我这么玩Web Api(二):数据验证,全局数据验证与单元测试


下一篇:[译] 在Web API 2 中实现带JSON的Patch请求