Databases
StackOverflow2013
Postsdbo
POExtension on IQueryable has no translation to SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExtension on IQueryable has no translation to SQL
    primarykey
    data
    text
    <p>I've been fiddling for two full days with a simple problem now.</p> <p>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. </p> <p>An example:</p> <pre><code>var storehousesWithBorrowedItems = productService.GetAllProducts() .Select(p =&gt; p.ProductLocationHistories .SingleOrDefault(plh =&gt; plh.DateCreation == p.ProductLocationHistories.Max(grp =&gt; grp.DateCreation))) .Select(plh =&gt; plh.Storehouse) .Distinct(); </code></pre> <p>These are all storehouses which currently have a product.</p> <p>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:</p> <pre><code>Product.ProductLocationHistories.Current(); </code></pre> <p>So I tried:</p> <pre><code>public static ProductLocationHistory Current(this EntitySet&lt;ProductLocationHistory&gt; plhs) { return plhs.SingleOrDefault(plh =&gt; plh.DateCreation == plhs.Max(grp =&gt; grp.DateCreation)); } </code></pre> <p>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.</p> <p>I have gone through possibilities like Func, ProductLocationHistory>> and also with Expression&lt;...> 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:</p> <pre><code>Current&lt;T&gt; (IQueryable&lt;T&gt; collection, string field) { return entity with max field of collection } </code></pre> <p>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.</p> <p><strong>Update</strong></p> <p>Currently, the following works: </p> <pre><code> Expression&lt;Func&lt;Product, ProductLocationHistory&gt;&gt; expression = IQueryable.Current(null); var ken = productService.GetAllProducts() .Where(p =&gt; p.OnLoan) .Select(expression) .Where(plh =&gt; plh.Storehouse != null) .Select(plh =&gt; plh.Storehouse) .Distinct(); public static Expression&lt;Func&lt;Product, ProductLocationHistory&gt;&gt; Current(this EntitySet&lt;ProductLocationHistory&gt; productLocationHistories) { Expression&lt;Func&lt;Product, ProductLocationHistory&gt;&gt; expression = p =&gt; p.ProductLocationHistories .SingleOrDefault(plh =&gt; plh.DateCreation == p.ProductLocationHistories.Max(plhs =&gt; plhs.DateCreation)); return expression; } </code></pre> <p>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. </p> <p>Thanks already Kirill! This is the first time I saw C# code translated to SQL! A nice step in the right direction!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPaul1977
    1. USPaul1977
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExtension on IQueryable has no translation to SQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWith 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`.
      singulars
      1. POExtension on IQueryable has no translation to SQL
      1. USjessehouwing
    2. COI 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.
      singulars
      1. POExtension on IQueryable has no translation to SQL
      1. USPaul1977
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload