Does LightSpeed.Linq support subqueries?

Hi Mindscape!

I have got problem with Linq subquery:

For example I have two entities Seller and Order, Seller has Orders.

When I run query:

            using (var dc =marketContext.CreateUnitOfWork())
            {

                var idler = dc.Sellers.FirstOrDefault(s => s.Orders.Count(o=> o.CreateDateTime > DateTime.Now.AddDays(-1)) == 0);

                if(idler != null)
                    Console.WriteLine(idler.Name);
                
            }

then a get exception: This overload of method 'Count' has no supported translation in LightSpeed.

When I rewrite query so:

            using (var dc =marketContext.CreateUnitOfWork())
            {

                var idler = dc.Sellers.FirstOrDefault(s => s.Orders.FirstOrDefault(o=> o.CreateDateTime > DateTime.Now.AddDays(-1)) == null);

                if(idler != null)
                    Console.WriteLine(idler.Name);
               
            }

then I get exception: The method 'FirstOrDefault' is not supported.

Hi Sergei,

We have limited support for sub-queries, for example where you have a nested Where or Select inside a Where clause or you have a sub-expression inside a projection. Unfortunately as you have seen the translations for the queries you are trying to perform are not currently supported.

Ill pop an entry on our backlog to have a look into expanding our support for this :)

Thanks!

Jeremy

Hi Jeremy!

Thanks for your answer!
It is very important for us that LigntSpeed supports sub-queries. We have many projects they use any ORM like LinqToSql and Entity Framework.  LinqToSql and Entity Framework support sub-queries, so we can't port our previous projects to LightSpeed.

In additional when i made sample, i called entity as Order, so table in database got name Order. When i tried run sample, i got SqlException:
Incorrect syntax near the keyword 'Order'. I think it's happened because Order is keyword.

Best regards,
Sergei

Hi Sergei,

Set quoteIdentifiers="true" (in config, or LightSpeedContext.QuoteIdentifiers = true in code) to turn on escaping -- this will sort out your SQL exception.

Unfortunately resource constraints mean we probably won't ever have support for arbitrary subqueries.  We try to support them well enough for most scenarios, and we are improving this support as customers identify limitations.  As Jeremy said we have created a backlog item for your particular example and we will try to help with others, but our resources are finite!

One option that may help is to use the IUnitOfWork.FindBySql method -- this allows you to write your own SQL queries.  It is not ideal of course but it does at least offer an escape hatch when LightSpeed can't generate the query for you!