CODEWITHSUNDEEP
databaselinqlinq-to-entities
user3410950
user3410950

Reputation: 135

Linq Query Condition for multiple row

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?

Upvotes: 1

Views: 1353

Answers (2)

nlips
nlips

Reputation: 1288

IQueryable<int> resourceIds = table
     // groups items by ResourceId
     .GroupBy(item => item.ResourceId)
     // consider only group where: an item has 2 as MappingId value
     .Where(group => group.Select(item => item.MappingId).Contains(2))
     // AND where: an item has 4 as MappingId value
     .Where(group => group.Select(item => item.MappingId).Contains(4))
     // select Key (= ResourceId) of filtered groups
     .Select(group => group.Key);

Upvotes: 1

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236298

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.

Upvotes: 2

Related Questions