CODEWITHSUNDEEP
c#linq
DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

subquery with top 1 and more than equal to using Linq

My sql query is as below. 

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)

I am trying to convert it to Linq but cant find way to use greater than equal to

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});

But it gives me error near the Contains keyword that "The type argument cannot be inferred from usage"

What to use here instead of contains? I also tried using Where but still doesn't work.

Upvotes: 0

Views: 679

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Because subquery 

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

is not correlated, you can use it separately, and then its value use in your main query. Also, are you sure, the query always will return value (not 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();

Then use this:

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;

Upvotes: 1

Related Questions