I am developing an application using LinqToSQL. As part of this I create a list of integers, which represent keys I want to filter. Every time in the past that I've done this and tried to join my list and the data table I get the following error:

Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator

Now this is fine because, as I understand it, it is a limitaiton/feature of LinqToSQl. I've been using the Contains operator for my queries as shown:

List<CargoProduct> cargoProducts = context.CargoProducts
                                   .Where(cp => cargos.Contains(cp.CargoID))
                                    .ToList();

Recently I've come across the 2100 item limitation in Contains, so was looking for other ways to do it, eventually coming up with the following:

List<CargoProduct> cargoProducts = context.CargoProducts.AsEnumerable()
                                   .Join(cargos, cp => cp.CargoID, c => c, (cp, c) => cp)
                                   .ToList();

Now, that works fine so I was putting together a knowledge sharing email for the other developers in case they came across this limitation. I was trying to get the error message so put together another query than I'd expect to fail:

List<CargoProduct> results = (from c in cargos
                              join cp in context.CargoProducts on c equals cp.CargoID
                              select cp).ToList();

Much to my surprise, not only did this not throw an error but it returned exactly the same results as the previous query. So, what am I missing here? I'm sure it's something obvious!

For reference context is my LinqToSQl connection and cargos is instantiated as:

List<int> cargos = context.Cargos.Select(c => c.CargoID).ToList();

Update

As mentioned in the reply it would indeed appear to be the order in which I am joining stuff, as if I use the following then I get the expected error message:

List<CargoProduct> test3 = (from cp in context.CargoProducts
             join c in cargos on cp.CargoID equals c
             select cp).ToList();

It's interesting functionality and I think I understand why it is doing what it does. Could be a good workaround instead of using Contains for smaller datasets.

In this query

List<CargoProduct> results = (from c in cargos
                         join cp in context.CargoProducts on c equals cp.CargoID
                         select cp).ToList();

the left operand in the join statement is of type IEnumerable, then the Enumerable.Join extension method is being chosen on method overload resolution. This means that the whole CargoProducts table is being loaded in memory and and filtered via Linq To Objects. It is similar to do context.CargoProducts.AsEnumerable().