-1

I need a LinQ query that does the same thing as below...but I can't seem to figure it out. (I'm using C#)

SELECT * FROM Groups WHERE Id IN(
SELECT DISTINCT [GroupId] FROM [MyTable].[dbo].[Detail])

NOT This (as marked a duplicate question)

SELECT * FROM Users WHERE User_Rights IN ("Admin", "User", "Limited")

If they are the same then so be it...I'll of course take that as the answer.

jpolo
  • 35
  • 7
  • @jeffmercado I know that question "answers" this one -- but honestly the hightest voted one in there is terrible -- I would use .where and Any – Hogan Oct 30 '19 at 21:26
  • It's not a duplicate of the question above...because it's not a CSV list of items in the "IN" clause like this SELECT * FROM Users WHERE User_Rights IN ("Admin", "User", "Limited")...it's a list of items in another query. – jpolo Oct 30 '19 at 21:32
  • @jpolo: `Contains()` directly translates to an `IN` clause, whether it was a static list of values or from a subquery. That's precisely what you asked for. – Jeff Mercado Oct 30 '19 at 21:39
  • @JeffMercado -- contains only works on objects -- we need a custom selector for a property so we need to use any. – Hogan Oct 30 '19 at 21:40
  • @Hogan: it works on scalar values so yes you naturally need to select, which is exactly what the subquery does as well. There's a difference between translating a query and coming up with an equivalent one. `Any()` produces an `EXISTS` clause which isn't exactly what was asked, but still yields a perfectly valid result. Better to share that in the duplicated question. – Jeff Mercado Oct 30 '19 at 21:42

1 Answers1

1

If Groups collection was in groups and detail was in detail then 

 groups.Where(z => detail.Any(x => x.groupid == z.id));

This checks each element of groups to see if the id property is the same as any groupid property of the detail collection.

It is important to note this might not be the best solution for your use case -- if your groups are very large then I would consider making it into a list and using contains -- that will give you hash performance. For smaller size groups the cost of making the list will be bigger than the performance gain.

 var idlist = details.Select(x => x.groupid).ToList();

 groups.Where(z => idlist.Contains(z.id));
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • I'm getting results from "groups" and from "detail" But when I follow the example, it's ok, up until I get to the "detail.Any"..."Any" is not available for the "detail" resultset. – jpolo Oct 30 '19 at 21:47
  • Never mind...I figured out what I was doing wrong. Testing now. – jpolo Oct 30 '19 at 21:48