CODEWITHSUNDEEP
c#sqllinq
Mr. MonoChrome
Mr. MonoChrome

Reputation: 1383

filtering a table by ids of another table in linq

So lets say i have a table mappedIds of an entity from linq, which has a relationship with another table named finishedDownloads on its column categoryID. How do i make a new table of mappedIds that contain no ids found in finishedDownloads?

I understand the commands like where and except, but im just not sure how to say, look at this id, and compare it to that id.

I'm looking for the equivalent of

SELECT * FROM  mappedIds mIDs WHERE mIDs.CategoryID NOT IN
(SELECT categoryID FROM finishedDownloads)

Edit: Mapped Ids is stored in a Table

Upvotes: 1

Views: 887

Answers (3)

Sachin
Sachin

Reputation: 40970

Try this

var result = mappedIds.Select(m=>m.CategoryId).Except(finishedDownloads
                                                       .Select(f=>f.categoryId));

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

You didn't say how your context is set, but even if it's not exactly as I think it is you can easily see the idea:

var results = _context.MappedIds
                      .Where(x => !_context.FinishedDownloads
                                           .Select(f => f.categoryID)
                                           .Contains(x.CategoryID));

Upvotes: 4

Selman Genç
Selman Genç

Reputation: 101681

var idList = finishedDownloads.Select(f => f.categoryID);

var result = mappedIds.Where(m => !idList.Contains(m.CategoryID)).ToList();

Upvotes: 3

Related Questions