How to select existing objects in LINQ query

Question

I have a table Companies

CompanyId
CountryId
and so on...

And then have a list of objects with following structure (let's name this list dataset)

Id
CountryId
CurrencyId
TotalCost
and so on...

and what I'm trying to do is to get all companies which exists in above dataset, so I'm trying to write query like:

IEnumerable<Company> companies = db.Companies.Where(a=a.CompanyId == dataset.Select(a=>a.CompanyId).ToList()

but it gives me an error,

Operator '==' cannot be applied to operands of typ int and List

How to get rid of this error?

You can't use this: `Where(a=a.CompanyId == dataset.Select(a=>a.CompanyId)`. But you can use `Where(a => a.CompanyId == companyId` where `companyId = dataset.Select(a=>a.CompanyId).FirstOrDefault()`. — Tetsuya Yamamoto, Aug 06 '18 at 09:43
You need to convert dataset.Select(a=>a.CompanyId).ToList() to int and then compare — LifeOfPi, Aug 06 '18 at 09:44
db.Companies.Where(a=>dataset.Select(a=>a.CompanyId).Contains(a.CompanyId)).ToList(); //Assuming dataset companyid is an int — Kamal, Aug 06 '18 at 09:47
As a complement, note that `myVar = someInt` returns an int, this is why you get this exception: it tries to compare `int` and `List`. Find more here: https://stackoverflow.com/a/3807583/9757968 — Maxime Recuerda, Aug 06 '18 at 09:48

score 1 · Answer 1 · answered Aug 06 '18 at 09:48

1

IEnumerable<Company> companies = db.Companies.Where(a=> dataset.Select(b=>b.CompanyId).Contains(a.CompanyId)).ToList()

Try this , you are trying to use list of int == on single int variable .. use contains to do that

answered Aug 06 '18 at 09:48

Sarav

139
1
13

score 1 · Answer 2 · answered Aug 06 '18 at 09:49

List<int> companyIds = dataset.Select(a=>a.CompanyId).ToList();
IEnumerable<Company> companies = db.Companies.Where(a=> companyIds.Contains(a.CompanyId));

You can pre-calculate the company ids to be searched and then use Contains method of List<T> to match the company ids.

Antoine V · Answer 3 · 2018-08-06T09:53:18.673

0

dataset.Select(a=>a.CompanyId) return a List, so you can't compare your simple CompanyId type int with this List

You must check if an object with his CompanyId being in this list allowed or not by the method Contains

var companies = db.Companies.Where(a=> dataset.Select(a=>a.CompanyId).Contains(a.CompanyId))

edited Aug 06 '18 at 09:53

answered Aug 06 '18 at 09:48

Antoine V

6,998
2
11
34

score 0 · Answer 4 · answered Aug 06 '18 at 09:52

You could use Contains() like this:

var companies = new List<(int companyid, int countryid, string companyName)>()
{
    (1, 2, "One Incorporated"),
    (2, 5, "Two-two Ltd"),
    (3, 24, "Mr-Three Ltd")
};

var other = new List<(int companyid, string stuff)>()
{
    (1, "asdad"),
    (2, "asdadv")
};

var inOther = companies.Where(x => other.Select(y => y.companyid).Contains(x.companyid));

This would output "One Incorporated" and "Two-two Ltd".

other.Select(y => y.companyid) gets your list of ID's then Contains() checks to see if x's (x being the company in the where statement) ID exists.

How to select existing objects in LINQ query

4 Answers4