How to translate to Linq Expression

Question

I have the following sql command which i cant translate into linq

select Distinct(fp.Parks_Id)
from ParkFeaturePark fp
Inner Join Parkfeatures feat on fp.ParkFeatures_Id = feat.Id
Inner Join Parks p On fp.Parks_Id = p.Id
where  p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =1 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =2 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =31     )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =42 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =106 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =118 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =4 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =6 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =10 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =18 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =22 )
    And p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =46 )

The twist here is that.. i have to use the combination seleted by the user .. sample is

    p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =1 )
    AND p.Id In (Select Parks_Id from ParkFeaturePark where ParkFeatures_Id =4 )

Or any other combination choose by the user..

Thanks for the reply

score 2 · Answer 1 · answered Jun 10 '13 at 06:17

2

This is not the answer, but the good style coding -

SELECT DISTINCT(fp.Parks_Id)
FROM dbo.ParkFeaturePark fp
JOIN dbo.Parkfeatures feat ON fp.ParkFeatures_Id = feat.Id
JOIN dbo.Parks p ON fp.Parks_Id = p.Id
WHERE p.Id IN (
        SELECT Parks_Id 
        FROM dbo.ParkFeaturePark 
        WHERE ParkFeatures_Id IN (1, 2, 31, 42, 106, 118, 4, 6, 10, 18, 22, 46)
    )

answered Jun 10 '13 at 06:17

Devart

119,203
23
166
186

Thanks devart, that somehow give me an idea – gerald Jun 10 '13 at 06:22
Im thinking about making the "where" to be dynamic using the Predicate Builder.. but can it be done without using it? – gerald Jun 10 '13 at 06:31

Gert Arnold · Accepted Answer · 2013-06-10T08:17:37.307

2

Let's suppose you've got navigation properties Park.ParkFeatureParks and ParkFeaturePark.Parkfeatures. (Or else will be able to create them). Then you can do:

int[] featureIds = new { 1, 2, 31, 42, 106, 118, .. };

var query = from p in context.Parks
            where p.ParkFeatureParks
                   .SelectMany(pfp => pfp.Parkfeatures)
                   .All(feature => featureIds
                                   .Contains(id => feature.ParkFeatures_Id))
            select p.Parks_Id;

edited Jun 10 '13 at 08:17

answered Jun 10 '13 at 07:37

Gert Arnold

105,341
31
202
291

thanks Gert, but my problem is, i want to get those Park_id that have all those featureId passed to it, like ... I want those Id that has 1, 2, 10 featureId. And not those park which only have 1 and 2 featureId.. sorry for my bad english.. hope you get what i mean. – gerald Jun 10 '13 at 07:54

How to translate to Linq Expression

2 Answers2