LINQ query IN for array

Question

I have the following model:

public class Device
{
    //....
    private ICollection<TagDevice> _tagDevices;
    public virtual ICollection<TagDevice> TagDevices { get => _tagDevices ?? (_tagDevices = new List<TagDevice>()); protected set => _tagDevices = value; }
}

public class TagDevice
{
    //....
    public int TagId { get; set; }
}

I need to select all Devices, which has ALL TagIds from int array.

For example:

device1 has tags [1, 2, 3]
device2 has tags [2, 3, 4]
device3 has tags [3, 4, 5]

tagsApplied is [2,3]

result: returned device1 and device2

I try

        query = query.Where(p => tagsApplied.Contains( )

but this method allows only one element, not element list

How to do it?

Are you using EF / EF Core 2.0 / 2.1 / 3.x or LINQ to SQL or Objects? — NetMage
– NetMage, Commented Mar 5, 2020 at 21:12

Mohammed Sajid · Accepted Answer · 2020-03-05 21:28:16Z

1

If i suppose the query is list of Device, change the contains function by Excepting List a from List b, like the following code :

IEnumerable<string> result = query
    .Where(device => !tagApplied.Except(device.TagDevices.Select(t=>t.TagId)).Any())
    .Select(x => x.Name);

Doc of Except

i hope that will help you fix the issue

edited Mar 5, 2020 at 21:28

answered Mar 5, 2020 at 21:10

Mohammed Sajid

4,9132 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NetMage Over a year ago

Rather than a double negative (!...Except) why not use All? tagsApplied.All(ta => device.TagDevices.Select(t=>t.TagId).Contains(ta))

NetMage · Accepted Answer · 2020-03-05 22:41:29Z

Using LINQKit, you can create an extension method that creates an or expression testing against each member of an array:

public static class LinqKitExt { // using LINQKit
    public static IQueryable<T> WhereAllIsContained<T,TKey>(this IQueryable<T> dbq, Expression<Func<T,IEnumerable<TKey>>> keyFne, IEnumerable<TKey> searchTerms) {
        Expression<Func<T,bool>> pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.And(a => keyFne.Invoke(a).Contains(s));

        return dbq.Where(pred.Expand());
    }
}

With this extension available, you can do

query = query.WhereAllIsContained(d => d.TagDevices.Select(td => td.TagId), tagsApplied);

I believe this should translate to SQL, but I don't have an EF Core model with a collection property to test.

Collectives™ on Stack Overflow

LINQ query IN for array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related