Extension on IQueryable has no translation to SQL

Question

I've been fiddling for two full days with a simple problem now.

I have a database with Product entities and ProductLocationHistory entities. ProductLocationHistory has a reference to either a Storehouse, a Contact or a Relation. Every time a product is moved it gets a new entry so the past of a product can be traced. The current location, therefore, is the last entry as determined by the DateCreation field of ProductLocationHistory.

An example:

var storehousesWithBorrowedItems = productService.GetAllProducts()
    .Select(p => p.ProductLocationHistories
        .SingleOrDefault(plh => plh.DateCreation == p.ProductLocationHistories.Max(grp => grp.DateCreation)))
    .Select(plh => plh.Storehouse)
    .Distinct();

These are all storehouses which currently have a product.

Of course it's highly inconvenient to write this out in code all the time I need to determine the current location of a product. A reference to the current ProductLocationHistory in Product is totally undesired in my opinion due to possible consistency problems. I'd prefer something like:

Product.ProductLocationHistories.Current();

So I tried:

public static ProductLocationHistory Current(this EntitySet<ProductLocationHistory> plhs)
    {
        return plhs.SingleOrDefault(plh => plh.DateCreation == plhs.Max(grp => grp.DateCreation));
    }

This does not work on queryable as I get an 'Current has no supported translation to sql' and since the combination of Product and ProductLocationHistory is often the 'start' of a query I want to stay IQueryable instead of immediately to IEnumerable and have a query for each product to determine the current location! Let alone what else follows afterwards... The current log entry for any entity is often used and it doesn't matter that much how complex the .Current() function is as long as it works and stays queryable. I had hoped my .Current(...) function would work since the underlying code is queryable but I still get an exception. I don't get the exception when the code is inline as in the first example.

I have gone through possibilities like Func, ProductLocationHistory>> and also with Expression<...> around it but I couldn't find an example of what I'm looking for. A solution of the kind Product.CurrentProductLocationHistory() might be even better. The absolute best solution would be even more generic and of the form:

Current<T> (IQueryable<T> collection, string field) { return entity with max field of collection }

Help would be greatly appreciated, I have been trying this for a long time and I'm certain it must be possible since the internal functions of LINQ itself - Any, First, Count, Max - also stay queryable if needed.

Update

Currently, the following works:

 Expression<Func<Product, ProductLocationHistory>> expression = IQueryable.Current(null);
        var ken = productService.GetAllProducts()
            .Where(p => p.OnLoan)
            .Select(expression)
            .Where(plh => plh.Storehouse != null)
            .Select(plh => plh.Storehouse)
            .Distinct();

public static Expression<Func<Product, ProductLocationHistory>> Current(this EntitySet<ProductLocationHistory> productLocationHistories)
    {
        Expression<Func<Product, ProductLocationHistory>> expression = p => p.ProductLocationHistories
                                         .SingleOrDefault(plh => plh.DateCreation == p.ProductLocationHistories.Max(plhs => plhs.DateCreation));
        return expression;
    }

A step in the right direction, but I'm not yet fully satisfied. I want to be able to use p.ProductLocationHistories().Current() so my quest continues.

Thanks already Kirill! This is the first time I saw C# code translated to SQL! A nice step in the right direction!

With this extension method in place, you *can* call .Current() if I'm not mistaking. Can you show a sample of the calling convention you'd like to use? Also, it's probably a lot faster to use `OrderByDescending(pld => pld.DateCreation).FirstOrDefault()` than querying max then using `SingleOrDefault`. — jessehouwing, Nov 19 '12 at 12:03
I will try the solution [link](http://stackoverflow.com/questions/10826275/iqueryable-extension-method-for-linq2entities) as soon as I can upgrade to ASP.NET 4.5. Currently we are acquiring Visual Studio 2012. I may update this question afterwards. — Paul1977, Jan 16 '13 at 13:06

score 1 · Answer 1 · answered Nov 17 '12 at 08:08

1

You can set field with expression:

Expression<Func<ProductLocationHistory, bool>> currentSelector = plh => plh.DateCreation == p.ProductLocationHistories.Max(grp => grp.DateCreation)

and use it anywhere:

var storehousesWithBorrowedItems = productService.GetAllProducts()
    .Select(p => p.ProductLocationHistories
        .SingleOrDefault(currentSelector ))
    .Select(plh => plh.Storehouse)
    .Distinct();

answered Nov 17 '12 at 08:08

Kirill Bestemyanov

11,946
2
24
38

As far as I remember this functions, but is not as usable as I want it to be. The .Current() is very often used on logs and I am willing to go as far as necessary to fix this. Shame on Microsoft for not including the possibility to write your own queryable functions. I think it's quite an omission. For the rest, LINQ makes my life much, much easier than before with dynamic SQL text strings *br*. – Paul1977 Jan 16 '13 at 13:08

Extension on IQueryable has no translation to SQL

1 Answers1