[Solved] Is it possible to do TOP 1 subquery using LINQ at server side?

[Alex]

I have 2 classes

class Order
{
   public int ID {get;set;}
   public IList<Image> Images {get;set;}
}

class Image
{
  public int ID {get;set;}
  public Order Order {get;set;}
  public int DisplayOrder {get;set;}
}

now I need to build query which will return me list of orders with their first image if it exists, I'm trying to run something like

query.Select(o => new
{
  ID=o.ID,
  DefaultImage = o.Images.SingleOrDefault(i=>i.DisplayOrder==0)
});

but this doesn't worked. It throws exception

System.NotSupportedException : Execution of 'System.Linq.Enumerable:SingleOrDefault(IEnumerable`1,Func`2)' on the database server side currently not implemented.

tried different ways to do this (for example o.Images.OrderBy(i=>i.DisplayOrder).FirstOrDefault()) - but it gave the same error.

Is there any way to accomplish my task by running one query on server? I guess I could use left join - but what will be best way performance wise?

Thanks!

[Alex]

I did this using left join, but it looks like it is processed mostly on client. I looked via SQL Profiler and I can see how ORM first executes SELECT to find all records from my right table... This is not acceptable due performance reasons. Please suggest.

[Jordan]

Hi Alex,

Please, find the answer to your question in your support ticket on the same subject.

Best wishes,
Jordan
the Telerik team