How to use Any between two ienumerables

Question

I have the following code inside my asp.net MVC web application:

var currentport = tms.TMSSwitchPorts
                     .Where(a => a.SwitchID == fromID)
                     .Select(a2 => a2.PortNumber)
                     .ToList();
            if (currentport.Any(tms.TMSSwitchPorts
                                   .Where(a => a.SwitchID == toID)
                                   .Select(a => a.PortNumber)
                                   .ToList()
                               )
               )
            {
              // do something
            }

but i am unable to use the .Any() in this way, although i am selecting the PortNumber field in both lists?

Can anyone advise?

Answer 1

You can to this efficiently and entirely in the database using a simple Join:

var match = tms.TMSSwitchPorts.Where(a => a.SwitchID == fromID)
                              .Join(tms.TMSSwitchPorts.Where(a => a.SwitchID == toID),
                                    (a) => a.PortNumber,
                                    (b) => b.PortNumber,
                                    (a, b) => true).Any();

if (match) { ... }

---

The generated SQL should look like

SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [TMSSwitchPorts] AS [t0]
            INNER JOIN [TMSSwitchPorts] AS [t1] ON [t0].[PortNumber] = [t1].[PortNumber]
            WHERE ([t0].[SwitchID] = @p0) AND ([t1].[SwitchID] = @p1)
            ) THEN 1
        ELSE 0
     END) AS [value]

so you don't have any data expect a single 1 or 0 moving over the wire and you don't have to clutter up your application memory.

---

The code

if (currentport.Any(tms.TMSSwitchPorts
                               .Where(a => a.SwitchID == toID)
                               .Select(a => a.PortNumber)
                               .ToList()
                           )

will not work because Any expects a predicate in the form of Func<T, bool>, but you pass it a List<Int> (assuming PortNumber is an int).

Answer 2

Take the analogous list for SwitchID == toID.

var alreadyUsed = tms.TMSSwitchPorts
                     .Where(a => a.SwitchID == toID)
                     .Select(a2 => a2.PortNumber)
                     .ToList();

Then just check that nothing appears in both two lists.

if (currentPort.Intersect(alreadyUsed).Any())
{ // do something }

Explanation:

Any() doesn't work the way you think it does. By itself as used above, it checks for any elements in the container. As shown in @BenAaronson's answer (which is slightly better than this answer), it checks if an IEnumerable contains any element which for which the function argument returns true.

list1.Any(HasSomeProperty)

where HasSomeProperty is a function which takes an element of list1 and returns a bool; or more usually with a lambda:

list1.Any(x => SomePropertyHoldsFor(x))

Edit:

I said @BenAaronson's answer was better because it allows some 'short-circuiting' optimizations that I didn't think my solution had. These are mentioned in the comments to his answer. However, with some trial and error I found that Intersect does automatically make the same optimizations - specifically it caches the 'output' of one IEnumerable for comparison with each element of the other, rather than traverse it each time. So my solution is better, as Intersect does automatically what you have to think about a little bit for Ben's method ;) @sloth's answer involves a different automatic optimization, and is much is better for the database query using Queryable that the asker had in mind.

This answer explains how Intersect works - it is not really 'caching', but it only accesses each IEnumerable once.

Answer 3

Based on jwg's comment, you can check to see if there is a match between the first set of ports (From) and the second set (to), using a Contains match:

var fromPorts = tms.TMSSwitchPorts
                 .Where(a => a.SwitchID == fromID)
                 .Select(a2 => a2.PortNumber);

if (tms.TMSSwitchPorts
       .Any(a => a.SwitchID == toID && 
                 fromPorts.Contains(a.PortNumber)))

Answer 4

If your aim is as jwg described in his comment, you could do:

var currentportFrom = tms.TMSSwitchPorts
                 .Where(a => a.SwitchID == fromID)
                 .Select(a2 => a2.PortNumber)
                 .ToList();
var currentportTo = tms.TMSSwitchPorts
                 .Where(a => a.SwitchID == fromID)
                 .Select(a2 => a2.PortNumber)
                 .ToList();

if(currentportFrom.Any(cp => currentportTo.Contains(cp))
{
    //do something
}