Linq Query Condition for multiple row

Question

I tried to solve one query from last 2 days but didn't. It looks easy to understand but can't made.

There are two column in Table for example:

ResourceId  ||   MappingId

      1            2
      1            3
      2            2
      2            4
      3            2
      3            4
      4            2
      4            5
      5            2
      5            4

This is one table which have two fields ResourceId and MappingId. Now I want resourceId which have Mappingid {2,4}

Means answer must be ResourceId {2,3,5}

How can I get this answer in Linq Query?

Answer 1

Use Contains of collection. This method can be translated by Entity Framework into SQL IN operator:

int[] mappingIds = { 2, 4 };
var resources = from t in table
                where mappingIds.Contains(t.MappingId)
                select t.ResourceId;

Lambda syntax:

var  resources = table.Where(t => mappingIds.Contains(t.MappingId))
                      .Select(t => t.ResourceId);

Generated SQL will look like:

SELECT [Extent1].[ResourceId]
FROM [dbo].[TableName] AS [Extent1]
WHERE [Extent1].[MappingId] IN (2,4)

---

UPDATE: If you want to get resources which have ALL provided mapping ids, then

var resources = from t in table
                group t by t.ResourceId into g
                where mappingIds.All(id => g.Any(t => t.Id == id))
                select g.Key;

Entity Framework is able to translate this query into SQL, but it will not be that beautiful as query above.