Subquery Single support

10 posts, 0 answers
  1. DuelingCats
    DuelingCats avatar
    49 posts
    Member since:
    Jul 2012

    Posted 24 Sep 2012 Link to this post

    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)
                };


  2. Garrett
    Garrett avatar
    23 posts
    Member since:
    Sep 2012

    Posted 24 Sep 2012 Link to this post

    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.
  3. DevCraft banner
  4. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 27 Sep 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  5. DuelingCats
    DuelingCats avatar
    49 posts
    Member since:
    Jul 2012

    Posted 01 Oct 2012 Link to this post

    Will do, thanks. Do these difficulties arise due to database abstraction so that it will work with multiple databases?
  6. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 04 Oct 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  7. Fawad
    Fawad avatar
    38 posts
    Member since:
    May 2009

    Posted 14 Oct 2012 Link to this post

    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,
  8. DuelingCats
    DuelingCats avatar
    49 posts
    Member since:
    Jul 2012

    Posted 17 Oct 2012 Link to this post

    Nice work around. Thanks for the tip.
  9. Nils C.
    Nils C. avatar
    41 posts
    Member since:
    Oct 2009

    Posted 18 Oct 2012 Link to this post

    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
  10. Garrett
    Garrett avatar
    23 posts
    Member since:
    Sep 2012

    Posted 18 Oct 2012 Link to this post

    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
  11. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 22 Oct 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
Back to Top
DevCraft banner