This is a migrated thread and some comments may be shown as answers.
Subquery Single support
9 Answers 101 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
DuelingCats
Top achievements
Rank 2
DuelingCats asked on 24 Sep 2012, 12:09 PM
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)
            };


9 Answers, 1 is accepted

Sort by
0
Garrett
Top achievements
Rank 1
answered on 24 Sep 2012, 08:12 PM
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.
0
Alexander
Telerik team
answered on 27 Sep 2012, 08:31 AM
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!
0
DuelingCats
Top achievements
Rank 2
answered on 01 Oct 2012, 04:23 PM
Will do, thanks. Do these difficulties arise due to database abstraction so that it will work with multiple databases?
0
Alexander
Telerik team
answered on 04 Oct 2012, 12:53 PM
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!
0
Fawad
Top achievements
Rank 1
answered on 14 Oct 2012, 03:05 PM
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,
0
DuelingCats
Top achievements
Rank 2
answered on 17 Oct 2012, 05:05 PM
Nice work around. Thanks for the tip.
0
Nils C.
Top achievements
Rank 1
answered on 18 Oct 2012, 09:23 AM
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
0
Garrett
Top achievements
Rank 1
answered on 18 Oct 2012, 01:36 PM
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
0
Alexander
Telerik team
answered on 22 Oct 2012, 08:16 AM
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.
Tags
LINQ (LINQ specific questions)
Asked by
DuelingCats
Top achievements
Rank 2
Answers by
Garrett
Top achievements
Rank 1
Alexander
Telerik team
DuelingCats
Top achievements
Rank 2
Fawad
Top achievements
Rank 1
Nils C.
Top achievements
Rank 1
Share this question
or