Subquery Single support

[DuelingCats]

In the following LINQ statement, you can see I am attempting to use the .Single() method in a sub query. The exception I am getting indicates that this isn't supported. Are there any plans in the future to add this feature/capability? 

var sauce = from system in context.MonitorSystems
            select new
            {
                Id = system.Id,
                Name = system.Name,
                Average = system.Records.Average(x => x.Measurement),
                LatestMeasurement = context.Records.Single(y => y.Id == (system.Records.Max(x => x.Id)))
            };

Execution of 'System.Linq.Queryable:Single(IQueryable`1,Expression`1)' on the database server side currently not implemented. This occured from EXTENT1-Record.Single(y => (y.Id == system.Records.Max(x => x.Id))).


I tried using this as a work around, but it throws an exception as well.

var sauce = from system in context.MonitorSystems
            select new
            {
                Id = system.Id,
                Name = system.Name,
                Average = system.Records.Average(x => x.Measurement),
                LatestMeasurement = context.Records.Where(y => y.Id == (system.Records.Max(x => x.Id))).ElementAt(0)
            };

[Garrett]

Hi DuelingCats,

Try this instead:

var sauce = from system in context.MonitorSystems
            select new
            {
                Id = system.Id,
                Name = system.Name,
                Average = system.Records.Average(x => x.Measurement),
                LatestMeasurement = context.Records.Where(y => y.Id == (system.Records.Max(x => x.Id))).FirstOrDefault().Measurement
            };

I took a guess at the Measurement column.

Let me know if that works.

[Alexander]

Hello,

Indeed some more-complex queries are not fully supported by OpenAccess and we are constantly working on Linq improvements. However, in almost every case there is more than one possible solution, so the same result could be achieved by using a slightly different query.

In your case you want to get the Record object with the maximal Id for each MonitorSystem instance. You could simplify the query a bit by sorting the Records collection of the system object, so the record you want to obtain is always first:

var sauce = from system in context.MonitorSystems
            select new
            {
                Id = system.Id,
                Name = system.Name,
                Average = system.Records.Average(x => x.Measurement),
                LatestMeasurement = system.Records.OrderByDescending(x => x.Id).FirstOrDefault()
            };

Hope that helps.

Kind regards,
Alexander
the Telerik team

[DuelingCats]

Will do, thanks. Do these difficulties arise due to database abstraction so that it will work with multiple databases?

[Alexander]

Hi,

At the moment we have limited support for subselects, so I believe you have hit one of the not supported cases.
When we add new functionality not only in the Linq implementation but in OpenAccess as a whole, we always aim at supporting all backends that OpenAccess works with. So this could explain why sometimes it takes more time to introduce new features in the product.

All the best,
Alexander
the Telerik team

[Fawad]

Hi DuelingCats, I had the similar problem with this, where I was selecting .First() from a subquery:

(from cls in Database.Clients
  join crs in Database.Cores on cls.ClientID equals crs.ClientID
  where (cls.Type == ClientType.Client
        || cls.Type == ClientType.Lead
        || cls.Type == ClientType.HotLead)
      && crs.StaffID.Equals(gStaffId)
      && (// Current or outdated.
           (crs.ActionDate >= dtThresholdDate)
           || (cls.OutdatedReason != OutdatedReason.Undefined
               && cls.OutdatedReason != OutdatedReason.NotOutdated)
          )
  select new
  {
      CoreType = (from cr in Database.Cores
                  where cr.ClientID.Equals(cls.ClientID)
                  && cr.StaffID.Equals(gStaffId)
                  orderby cr.ActionDate descending
                  select
                cr.Type
                  ).First(),
  }).Distinct().ToList();

And I solved it by declaring subselect as an Anonymous type:

(from cls in Database.Clients
      join crs in Database.Cores on cls.ClientID equals crs.ClientID
      where (cls.Type == ClientType.Client
            || cls.Type == ClientType.Lead
            || cls.Type == ClientType.HotLead)
          && crs.StaffID.Equals(gStaffId)
          && (// Current or outdated.
               (crs.ActionDate >= dtThresholdDate)
               || (cls.OutdatedReason != OutdatedReason.Undefined
                   && cls.OutdatedReason != OutdatedReason.NotOutdated)
              )
      select new
      {
          CoreType = (from cr in Database.Cores
                      where cr.ClientID.Equals(cls.ClientID)
                      && cr.StaffID.Equals(gStaffId)
                      orderby cr.ActionDate descending
                      select new
                      {
                          Type = cr.CoreType
                      }).First().Type
      }).Distinct().ToList();

I guess the same can be done for .Single() as well. Might be a performance hit though, Telerik team can tell you better about that.

Regards,

[DuelingCats]

Nice work around. Thanks for the tip.

[Nils C.]

Hi,

i have a Problem with Subquerys too. 

Dim aufgaben_heute_datensatz = (From it In _db.Task Where aufgaben_heute.Contains(it.Id) _
                                           Select it.Id, Aufgabe = it.Name, it.Feedback_Datei_UIndex, Projekt_ID = it.Projektid, Deadline_Soll = it.EndDate, Phasename = _phq_context.Aufgaben.gib_phase_von_task(it).Name, _
                                           it.EndDate, it.Beschreibung, _
                                           Projektname = (From pt In _db.Projekt Where pt.Id = it.Projektid Select a = pt.Projekt_Name + " (Nr. " + pt.Projektnummer + ")").FirstOrDefault).ToList

Always get :

"An exception occured during the execution of 'Extent<pms_neu.Task>().Where(it => value(pms_neu.mod_mitarbeiter_meineaufgaben+_Closure$__1).$VB$Local_aufgaben_heute.Contains(it.Id)).Select(it => new VB$AnonymousType_0`9(Id = it.Id, Aufgabe = it.Name, Feedback_Datei_UIndex = it.Feedback_Datei_UIndex, Projekt_ID = it.Projektid, Deadline_Soll = it.EndDate, Phasename = value(ASP.mitarbeiter_module_mod_mitarbeiter_meineaufgaben_ascx)._phq_context.Aufgaben.gib_phase_von_task(it).Name, EndDate = it.EndDate, Beschreibung = it.Beschreibung, Projektname = value(ASP.mitarbeiter_module_mod_mitarbeiter_meineaufgaben_ascx)._db.Projekt.Where(pt => ((ConvertChecked(pt.Id) == it.Projektid) ?? False)).Select(pt => Concat(pt.Projekt_Name, " (Nr. ", pt.Projektnummer, ")")).FirstOrDefault()))'. Failure: Execution of 'System.Linq.Queryable:FirstOrDefault(IQueryable`1)' on the database server side currently not implemented. 

Any advice what i have to change?

Thanks in advance

[Garrett]

Hi Nils,

That error i telling you that OpenAccess does not understand how to convert "FirstOrDefault" to SQL.

Since you have a Where clause on your subquery, it should only return one record anyway.

Try adding .ToList ahead of .FirstOrDefault and it should work.

When you add ToList before other methods, you are forcing the SQL query to run and return the record set AND THEN you can use FirstOrDefault to grab the first record.

You have to be careful though because if you don't have a Where clause (which you do), you could wind up returning far more records then you needed for that subquery, making it inefficient.

Dim aufgaben_heute_datensatz = (From it In _db.Task Where aufgaben_heute.Contains(it.Id) _
                                           Select it.Id, Aufgabe = it.Name, it.Feedback_Datei_UIndex, Projekt_ID = it.Projektid, Deadline_Soll = it.EndDate, Phasename = _phq_context.Aufgaben.gib_phase_von_task(it).Name, _
                                           it.EndDate, it.Beschreibung, _
                                           Projektname = (From pt In _db.Projekt Where pt.Id = it.Projektid Select a = pt.Projekt_Name + " (Nr. " + pt.Projektnummer + ")").ToList.FirstOrDefault).ToList

Thanks,

Garrett

[Alexander]

Hello Nils,

As I see you need the subselect just to find the related Projekt record for the current Task. As this seems like a simple one-to-many relation, you should already have a navigational property in the Task class that you can use to access the referenced Projekt instance directly. It this case the subselect could be replaced with a simpler clause like this:

Projektname = it.Projekt.Projekt_Name + " (Nr. " + it.Projekt.Projektnummer + ")"

Here I assume that the navigational property is named Projekt.
Hope that helps.

Regards,
Alexander
the Telerik team