CODEWITHSUNDEEP
sqlsql-serverlinqentity-frameworkstored-procedures
John John
John John

Reputation: 1

How I can write something similar to Linq .Any() when writing sql server queries

I have the following linq query inside my asp.net mvc web application:-

Public IQuerable<Technologies> Search (int? isManaged , bool PrimaryCustomerOnly, int?customerID  )
{

var technology2 = ITSys.Technologies

.Where(a => (

( !isManaged.HasValue||a.IsManaged == isManaged)

&& 
(!PrimaryCustomerOnly && a.ITSysFirewall.FirewallCustomers.Any(a2 => customerID.HasValue && a2.CustomerName.ToLower() == stringcustomerid && relatedresourcesID2.Contains(a.IT360ID.Value)))

));
//code goes here.
}

Currently I want to execute the same query , inside an sql server 2008 stored procedure . but I am unable to write something similar to .Any (). So can anyone advice how I can write an sql equivalent to the .Any()? Thanks

Upvotes: 2

Views: 7134

Answers (2)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

many guessings there (keys for relationship, which table has really IT360ID field, but I would imagine something like that.

select t.*
from Technologies t
where IsManaged = @IsManaged 
and @PrimaryCustomerOnly = 0 
and t.IT360ID in (<some elements corresponding to relatedresourcesID2>)-- like ('1', '2', 'adsb')
and exists (select null
        from FirewallCustomers fc
        where t.Id = fc.PrimaryCustomerOnly_Id 
        and fc.customerId is not null 
        and lower(fc.CustomerName) = @stringcustomerid)

Upvotes: 2

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61401

EXISTS would be the closest that you can get to .Any()

Example 

SELECT * FROM [TABLE]
WHERE EXISTS (SELECT 1 FROM [TABLE] WHERE NAME IS NOT NULL)

would be equivalent to 

db.Table.Any(o => o.Name != null);

This specific query would start like this, and I am not sure how to finish it but it should give you indication in what direction you should go.

SELECT * FROM Technologies 
WHERE EXISTS (SELECT 1 FROM Technologies WHERE (isManaged IS NOT NULL AND isManaged = @MANAGED) AND (PrimaryCustomerOnly == FALSE AND 
// DO THE JOINS a.ITSysFirewall.FirewallCustomers.Any(a2 => customerID.HasValue && a2.CustomerName.ToLower() == stringcustomerid && relatedresourcesID2.Contains(a.IT360ID.Value)))
)

Upvotes: 2

Related Questions