I just grabbed the latest July 17th build and I am still seeing the following issue.

           int[] ids = new int[] { 1, 2, 3, 4, 5};

            LinqMetaData metaData = new LinqMetaData(DataAdapter);
            var q = from c in metaData.Customers
                    select c;

            var q2 = q.Where(c => c.Position == Array.IndexOf(ids, c.Id));
            

At q2, it seems the Array.IndexOf tries to get translated into SQL as "Customer.Position IS NULL." So no data gets returned.

I change it to a delegate, it does the filtering in memory.

            var q2 = q.Where(delegate(Customer c) {
                return c.Position == Array.IndexOf(ids, c.Id));
            });

Another observation, if I call invoke the query before the filtering, as in call .ToList() before the Where, this forces the lambda to work in memory because the results are fetched already.

What is the proper way to do this? Should I be required to force the query before any in memory type operations? Is there any reason why the Array.IndexOf method isn't be picked up to work in memory and not in sql?

var q2 = q.Where(c => c.Position == Array.IndexOf(ids, c.Id));

Should be:

var q2 = q.ToList().Where(c => c.Position == Array.IndexOf(ids, c.Id));

Otherwise Array.IndexOf() is part of the DB query, because c.Id is an argument of IndexOf, so it requires to be inside the DB.

I did notice this behavior, is there anyway to know what functions can't be mapped into a DB call?

lethologica wrote:

I did notice this behavior, is there anyway to know what functions can't be mapped into a DB call?

In this case, you can't do it, as your array is in memory and you index into that array with a value from the DB. The array isn't passed to the DB so you can't index into it on the DB, you have to do it on the client. The provider doesn't compile it into an in-memory delegate because you refer to a value which is in the db: 'c'. The delegate is seen as a separate object so it's compiled as in-memory code, which makes IndexOf not dependent on any db element (notice that the 'Where' call is simply appended to the database query!) and is thus ran in-memory.