子查询top 1且大于等于使用Linq

Question

我的sql查询如下。

select st.kProductVersionPlatformFrom, st.kProductVersionPlatformTo, pv.kProduct, pv.nId, * 
from vSysTable st
left join vProductVersion pv on st.kProductVersionPlatformFrom = pv.nId  
where 
st.kProductVersionPlatformTo >= (select top 1 nid from vProductVersion where kProduct = 2
                                 and kProductVersionPlatform = 87 order by nId desc)

我试图将其转换为Linq，但无法找到使用大于等于

的方法

var systemTableList = (from sysTableEntity in _sysTableEntityRepository.AsQueryable()
                       join versionEntity in _productVersionRepository.AsQueryable() on
                           sysTableEntity.ProductVersionPlatformFrom equals versionEntity.Id
                           into leftjoined 
                           from joinedsysVersionTable in leftjoined.DefaultIfEmpty()
                           where (from versionEntity in _productVersionRepository.AsQueryable() 
                                  where versionEntity.ProductEntityId.Equals(2) && 
                                 versionEntity.PlatformProductVersionEntityId.Equals(87) 
                                  orderby versionEntity.Id descending 
                                  select versionEntity.Id).Take(1).Contains(sysTableEntity.ProductVersionPlatformFrom >= 87)
                       select new { sysTableEntity});

但是它给了我包含关键字附近的错误，"类型参数不能从用法推断"

这里用什么代替contains?

Answer 1

因为子查询

select top 1 nid
from vProductVersion
where kProduct = 2
and kProductVersionPlatform = 87
order by nId desc

不相关，您可以单独使用它，然后在主查询中使用它的值。另外，您确定查询总是返回值(不是null)吗?

var nid = from versionEntity in _productVersionRepository.AsQueryable()
where versionEntity.ProductEntityId.Equals(2) && 
versionEntity.PlatformProductVersionEntityId.Equals(87)
orderby versionEntity.Id descending
select versionEntity.Id).First();

然后使用:

var systemTableList =
     from sysTableEntity in _sysTableEntityRepository.AsQueryable()
       join versionEntity in _productVersionRepository.AsQueryable()
     on sysTableEntity.ProductVersionPlatformFrom equals versionEntity.Id
           into leftjoined
     from joinedsysVersionTable in leftjoined.DefaultIfEmpty()
     where ProductVersionPlatformTo >= nid;