CODEWITHSUNDEEP
c#sqllinq
Christopher Beltran
Christopher Beltran

Reputation: 86

Linq AND for each item in list

I have a List of ints. I need to select elements from a data source where a particular field/column matches each int in the list.

Data Source Example

ItemID  ListID
1       1
1       2
2       1

I want to find all Items that match all ListIDs from a List containing List IDs 1 and 2.

Currently I'm using...

List<Item> items = (from element in MyItems where listIDs.Contains(element.ListID) select element).ToList();

However, this produces an OR query and not an AND query across multiple rows for each distinct ItemID.

Upvotes: 0

Views: 490

Answers (5)

terrybozzio
terrybozzio

Reputation: 4542

If i understood you correctly,this will do:

var result = (from item in MyItems
              from i in listIDs
              where i == item.ListId
              select item).ToList();

It will get all Item objects in MyItems list that have ListId present in ListId.

Upvotes: 0

Mike Hixson
Mike Hixson

Reputation: 5189

I'm not sure I understand the question. I think you have a list and you are trying to match rows that have both ListID & ItemID equal to ANY item in the list. If thats what you are sking this this is a way to do it:

List<Item> items = from element in MyItems
where listIDs.Contains(element.ListID) and listIDs.Contains(element.ItemID)

Or perhaps you are trying to match rows that have both ListID & ItemID equal to the SAME item in the list. If thats what you are sking this this is a way to do it:

List<Item> items = from element in MyItems
where listIDs.Contains(element.ListID) and element.ListID == element.ItemID

Upvotes: 0

har07
har07

Reputation: 89285

You can try this way :

List<Item> result = MyItems.GroupBy(o => o.ItemID)
                            //find group that contain all listIDs
                           .Where(o => !listIDs.Except(o.Select(x => x.ListID)).Any())
                           //flatten groups to items
                           .SelectMany(o => o)
                           .ToList();

Related question : Determine if a sequence contains all elements of another sequence using Linq

Upvotes: 1

sameh tohamy
sameh tohamy

Reputation: 31

if i got your question !! then try this

var MyItems=<your data column values>
var ItemsMatch=MyItems.Where(z=>MyIntsList.Contains(int.Pars(z.ToString()))).ToArray();

Upvotes: 0

haku
haku

Reputation: 4505

If you are trying to compare two list for equality, then you could use SequenceEqual method.

Something along the lines 

list<item> results = (from element in MyItems where listIDs.SequenceEqauls(element.ListID) select element).ToList();

I am not entirely sure if I understood your question properly. When you say "particular field/column," is that field/column some kind of collection as well?

Upvotes: 0

Related Questions