LINQ Subquery help

[Garry]

Guys this question really is just my non-comprehension of LINQ so I apologize, but I really could use some advice here. Basically what I need to do is to combine these two LINQ queries into one. One of them is basically a subquery of the other to filter out latest record.

Basically what I have is this

var result = (from c in scope.Extent<PtAMember>()
                              where (c.LastName.ToLower().Contains(param.ToLower()) || c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param))
                              select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName);

works great no issues except I get a bunch of records I do not need. I am only needing the latest record. I found out you apparently can't call methods from with in a LINQ query which is sad because I think I would have already had this licked if you could. The second query looks like this.
 

public int GetEffectiveCertification(int participantPK)
        {
            try
            {
                scope = InfrastructureScopeProvider.GetNewObjectScope();
                scope.Transaction.Begin();

                var result = (from c in scope.Extent<PtApprovedCertification>()
                              where c.FkosParticipant.Equals(participantPK) && c.EffectiveDate <= DateTime.Now &&
                              (c.FkMLSosCodeActionType != 1057 || c.FkMLSosCodeActionType != 1059 || c.FkMLSosCodeActionType != 1060 || c.FkMLSosCodeActionType != 1062 ||
                              c.FkMLSosCodeActionType != 1063 || c.FkMLSosCodeActionType != 1064)
                              orderby c.EffectiveDate descending, c.Pk descending
                              select c).Single();

                return result.Pk;
            }

            catch (Exception ex)
            {
                return 0;
            }
        }

What I was hoping to do was something like this.

 var result = (from c in scope.Extent<PtAMember>()
                              where (c.LastName.ToLower().Contains(param.ToLower()) || c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param)) &&
                              c.PtApprovedCertification.Pk.Equals(GetEffectiveCertification(c.PtApprovedCertification.OsParticipant.Pk))
                              select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName);

 but as I said above you can't call methods from a query :(. What I have basically hacked it up to is this monstrosity.

public ObservableCollection<PtAMember> SimpleResidentSearch(string param)
        {

            try
            {
                scope = InfrastructureScopeProvider.GetNewObjectScope();
                scope.Transaction.Begin();

                ObservableCollection<PtAMember> list = new ObservableCollection<PtAMember>();

                var result = (from c in scope.Extent<PtAMember>()
                              where (c.LastName.ToLower().Contains(param.ToLower()) || c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param))
                              select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName);

                //stores most recent cert PK
                int certPK = 0;
                //stores current resident pk
                int currentPK = 0;
                //stores previous resident PK
                int oldPK = 0;

                foreach (PtAMember resident in result)
                {
                    currentPK = resident.PtApprovedCertification.OsParticipant.Pk;
                    if (oldPK != currentPK)
                    {
                        //set oldPK to new pk so that we can compare later
                        oldPK = currentPK;
                        certPK = GetEffectiveCertification(resident.PtApprovedCertification.OsParticipant.Pk);
                    }
                    //Only Add residents who represent effective cert
                    if (resident.PtApprovedCertification.Pk == certPK)
                    {
                        list.Add(resident);
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

It works, but it is so so so slow it's not a feasible answer. I did see where people were doing subqueries in LINQ and this is my attempt at combing these two methods into one LINQ query. This does not work.

var result = (from c in scope.Extent<PtAMember>()
                             from p in scope.Extent<PtApprovedCertification>()
                              where c.FkptACertification == p.Pk && p.FkosParticipant.Equals(c.PtApprovedCertification.OsParticipant.Pk) && p.EffectiveDate <= DateTime.Now &&
                              (p.FkMLSosCodeActionType != 1057 || p.FkMLSosCodeActionType != 1059 || p.FkMLSosCodeActionType != 1060 || p.FkMLSosCodeActionType != 1062 ||
                              p.FkMLSosCodeActionType != 1063 || p.FkMLSosCodeActionType != 1064) &&
                              (c.LastName.ToLower().Contains(param.ToLower()) ||
                              c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param))
                              select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName);

Anyhow I am really struggling with this one so if any one has any pointers I would forever be greatful.

Thanks!

[Zoran]

Hi Garry Clark,

 I think that the best way to achieve this would be to just combine the two queries. Take the following approach in mind:

from c in result
where c.PtApprovedCertification.FkosParticipant.Equals(participantPK) && c.PtApprovedCertification.EffectiveDate <= DateTime.Now &&
...
orderby c.PtApprovedCertification.EffectiveDate descending, c.PtApprovedCertification.Pk descending
select c).Single();

After writing the first query, you could just extend it by adding the additional filtering for the PtApprovedCertification reference. Both queries will execute at once even though you have written them in two separate pieces(it is just for having the code more clear).

Sincerely yours,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Garry]

Thanks for the reply Zoran, but I think the issue I run into when I try and combine the 2 queries is that I do not believe I can .Single() to get my result as it will filter out all the other PtAMembers as well will it not?

What I need is all PtAMembers who have a current certification which is represented by the PtApprovedCertification object. What I get now is a PtAMember for every certification they have. So for instance if I had 3 certifications then my PtAMember object would be returned 3 times. I only need the PtAMember object who has the current PtApprovedCertification object returned. I do not care about the objects who have past certifications, but the problem is that I need this for all PtAMembers and not just me.

So to try and simplify it a little more. I need an ObservableCollection returned of all PtAMember objects who has a current PtApprovedCertification object associated with it. We can determine whether it is the current certification or not by the filter expressions impossed on the PtApprovedCertification object in the previous LINQ queries.

Does that make any sense? I will look at the example you have offered and see what I can do with it, but I still think my issue is I can't do a .Single().

Thanks again!

[Garry]

OK Zoran,
I know this looks crazy, but what about something like this. Is this even feasible?

var result = (from c in scope.Extent<PtAMember>()
                              where (c.LastName.ToLower().Contains(param.ToLower()) || c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param)) &&
                              c.FkptACertification == (from p in scope.Extent<PtApprovedCertification>()
                                                       where p.EffectiveDate <= DateTime.Now &&
                             p.FkMLSosCodeActionType != 1057 && p.FkMLSosCodeActionType != 1059 &&
                             p.FkMLSosCodeActionType != 1060 && p.FkMLSosCodeActionType != 1062 &&
                             p.FkMLSosCodeActionType != 1063 && p.FkMLSosCodeActionType != 1064 &&
                             p.FkptApprovedCertificationVoidedBy == null && p.FkosParticipant == c.PtApprovedCertification.OsParticipant.Pk
                                                       orderby p.EffectiveDate descending, p.Pk descending
                                                       select p).Single().Pk
                              select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName);

Looks logical to me, but it throws an error of

An exception occured during the execution of '
Extent<EliteExtender.Infrastructure.Models.Elite.PtAMember>.Where(c => (((c.LastName.ToLower().Contains(value(EliteExtender.Infrastructure.Services.EliteDataService+<>c__DisplayClass0).param.ToLower()) OrElse c.FirstName.ToLower().Contains(value(EliteExtender.Infrastructure.Services.EliteDataService+<>c__DisplayClass0).param.ToLower())) OrElse c.SocialSecurityNumber.Contains(value(EliteExtender.Infrastructure.Services.EliteDataService+<>c__DisplayClass0).param)) AndAlso (c.FkptACertification == value(EliteExtender.Infrastructure.Services.EliteDataService).scope.Extent().Where(p => (((((((((p.EffectiveDate <= Convert(DateTime.Now)) AndAlso (p.FkMLSosCodeActionType != Convert(1057))) AndAlso (p.FkMLSosCodeActionType != Convert(1059))) AndAlso (p.FkMLSosCodeActionType != Convert(1060))) AndAlso (p.FkMLSosCodeActionType != Convert(1062))) AndAlso (p.FkMLSosCodeActionType != Convert(1063))) AndAlso (p.FkMLSosCodeActionType != Convert(1064))) AndAlso (p.FkptApprovedCertificationVoidedBy == null)) AndAlso (p.FkosParticipant == c.PtApprovedCertification.OsParticipant.Pk))).OrderByDescending(p => p.EffectiveDate).ThenByDescending(p => p.Pk).Single().Pk))).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName)'. See InnerException for more details.

Inner Exception Message

Argument expression is not valid

Anyhow that is where I am at now. This LINQ stuff is starting to drive me bonkers!

[Zoran]

Hello Garry Clark,

 I really think that it is not necessary to complicate the query and I would suggest you to split the query in two and use the result from the first one as a parameter to the second. Please consider the following:

int pk = (from p in scope.Extent<PtApprovedCertification>()
      where p.EffectiveDate <= DateTime.Now &&
      p.FkMLSosCodeActionType != 1057 && p.FkMLSosCodeActionType != 1059 &&
      p.FkMLSosCodeActionType != 1060 && p.FkMLSosCodeActionType != 1062 &&
      p.FkMLSosCodeActionType != 1063 && p.FkMLSosCodeActionType != 1064 &&
      p.FkptApprovedCertificationVoidedBy == null && p.FkosParticipant ==             c.PtApprovedCertification.OsParticipant.Pk orderby p.EffectiveDate descending, p.Pk descending select p).Single().Pk

var result = (from c in scope.Extent<PtAMember>()
          where (c.LastName.ToLower().Contains(param.ToLower()) ||  c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param)) && c.FkptACertification == pk
         select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName);

Have in mind that combining the queries into one where the inner one has a Single() call in it  will still execute two queries so you should not have a performance overhead.

Best wishes,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Garry]

Zoran,
I appreciate you continuing to help me on this as I am really no closer than when we began. The problem with your last solution is that now we have gotten into a chicken before the egg conundrum where how can I do this in the first query

p.FkosParticipant == c.PtApprovedCertification.OsParticipant.Pk

if we haven't queried for c yet? Other words I can't filter the first query for just the PtAMember, because I have no clue what PtAMember I'm looking for. I looked into doing two queries like this but I can't quite get it to work thus the reason I combined them into one ugly one. Any other ideas? I can't believe that this simple type of query is that difficult.

Here is the User Defined Function in SQL that usually takes care of returning the effective certification PK. May help in seeing what is needed.

Select top 1
      @ApprovedCertPK = ptApprovedCertification.PK
    From ptApprovedCertification with (nolock)
    Where ptApprovedCertification.fkosParticipant = @ParticipantPK
    And ptApprovedCertification.fkMLSosCodeActionType not in (1057, -- FSS/WtW Addendum Only
                                                              1059, -- Issuance of Voucher
                                                              1060, -- Expiration of Voucher
                                                              1062, -- Annual HQS Inspection Only
                                                              1063, -- Historical Adjustment
                                                              1064) -- Void
    And ptApprovedCertification.EffectiveDate <= @EffectiveDate
    And (IsNull(ptApprovedCertification.IsExcluded, 'Yes') = 'No')
    And (ptApprovedCertification.fkptApprovedCertificationVoidedBy is null)
    Order By ptApprovedCertification.EffectiveDate desc,
             ptApprovedCertification.PK desc

Since OpenAccess already has all the related objects setup as fields in PtAMember it seems to me we should just be able to filter on the PtAMember.PtApprovedCertification field, but I can't figure out how unless I did some really nasty loop with multiple selections which would kill performance.

Is there anything else you can think of that may help shed some light on the solution here? DB Diagram or reversemapping.config perhaps?

I do appreciate all the help and I know it has to be something simple.

[Zoran]

Hi Garry Clark,

 You are right, in my last post I really did make a glitch in my Linq query which resulted in a chicken over egg situation. After multiple tries and combinations with your query, the final result is that this is not a query that can be processed in a single server call using the OpenAccess Linq implementation. The sub-queries are still a problematic part in our Linq and that is the last piece of the puzzle that we are implementing for the next Q2 release. The only way to achieve your goal would be to execute a part of the query on the database server and part of it in memory. You could still achieve it using only one query and it will very much look like the one you had originally tried. The in-memory Linq calls support calling a user defined method so you could try something like this:

var result = (from c in scope.Extent<PtAMember>()
          where c.LastName.ToLower().Contains(param.ToLower()) ||           c.FirstName.ToLower().Contains(param.ToLower()) || c.SocialSecurityNumber.Contains(param)
select c).Distinct().OrderBy(PtAMember => PtAMember.LastName).ThenBy(PtAMember => PtAMember.FirstName).ToList().Where(c=>c.PtApprovedCertification.Pk.Equals(GetEffectiveCertification(c.PtApprovedCertification.OsParticipant.Pk));

I hope you are able to execute this query and get the desired results.

Kind regards,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Garry]

Zoran,
I appreciate all of the effort you put into this. The last solution you offered does work, but it's so clunky that the performance is not acceptable. Takes almost 5 minutes to pull 130 records from the DB. I am thinking at this point that OpenAccess just may not work for this module and I may have to re-engineer the DAL in this one. I will have to be able to query pulling records only on the last certification.

The other problem is that since this is a SQL CE local database cache I am unable to use stored procedures to query against as well. I'm thinking the only option left to me is to use straight ADO.NET and see how that goes. Do you think the Q2 release of OpenAccess will be able to handle this query of mine?

Any guidance would greatly be appreciated.

Thanks!

[Zoran]

Hello Garry Clark,

 There is another possible solution which could improve the performance on this query:

List<PtApprovedCertification> MyFunction(IObjectScope scope, string identifier)
{
 var x = scope.Extent<PtAMember>()
              .Where(m => m.LastName.ToLower().Contains(identifier.ToLower()) ||
            m.FirstName.ToLower().Contains(identifier.ToLower()) ||
      m.SocialSecurityNumber.Contains(identifier))
     .Distinct()
     .OrderBy(m => m.LastName).ThenBy(m => m.FirstName)
     .Select(m => m.ApprovedCertification.OsParticipant);
 var y = x.ToList();

 DateTime effective = DateTime.Now;
 List<int> filter = new List<>(new int[] { 1057,1059,1060,1062,1063,1064 });
 var q = scope.Extent<PtApprovedCertification>()
              .Where(ac => ac.EffectiveDate <= effective &&
            ! filter.Contains(ac.FkMLSosCodeActionType) &&
      ac.IsExcluded == false &&
      ac.FkptApprovedCertificationVoidedBy == null &&
      y.Contains(ac.PkosParticipant));
 return q.ToList();
}

If it still doesn't get you the expected results, you could still use OpenAccess instead of refactoring to plain ADO. You could  directly execute SQL queries against your database using the scope.GetSqlQuery method. There you could write a parametrized SQL query similar to the one you have been using in your stored procedure.

Best wishes,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Garry]

Zoran,
Thank you again for all of your help on this. I think I've decided to use the scope.GetSQLMethod for now. Here is the query that I ended up with that I pass to the scope.

Select * from ptamember
                              Inner Join ptapprovedcertification on ptapprovedcertification.pk =  ptamember.fkptacertification
                              Inner Join osParticipant on osParticipant.PK = ptApprovedCertification.fkosParticipant
                              Where fkptacertification in (Select Top(1) ptApprovedCertification.PK
                              From ptApprovedCertification with (nolock)
                              Where ptApprovedCertification.fkosParticipant = osParticipant.PK
                              And ptApprovedCertification.fkMLSosCodeActionType not in (1057,   -- FSS/WtW Addendum Only
                                                                                        1059, -- Issuance of Voucher
                                                                                        1060, -- Expiration of Voucher
                                                                                        1062, -- Annual HQS Inspection Only
                                                                                        1063, -- Historical Adjustment
                                                                                        1064) -- Void
                              And ptApprovedCertification.EffectiveDate <= GETDATE()
                              And (ptApprovedCertification.fkptApprovedCertificationVoidedBy is null)
                              Order By ptApprovedCertification.EffectiveDate desc,
                              ptApprovedCertification.PK desc) AND
                              LastName Like ('%clark%') OR FirstName Like ('%clark%') OR SocialSecurityNumber Like ('%clark%')
                              Order By LastName, FirstName

This works great on my full blown SQL 2008 server, but again it is dog slow on my local SQL CE DB. I have posted some questions out on MSDN to the SQL CE guys to see what the deal is. I mean this is not that complicated of a query. I am starting to think SQL CE is for the birds. If I didn't already have so much time invested into it I think I would look for another offline DB solution.

Anyhow I will let you know what I hear back from the CE guys as it may help others since subqueries are a fairly common thing.

Thanks,
Garry

[Zoran]

Hi Garry Clark,

 Seems that SQL CE has a somewhat weak support for sub-queries and that might be the reason for the unsatisfactory results that you are getting. You could check-out the following forum where I found an interesting discussion on the matter.

Best wishes,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Garry]

Zoran,
I'm back with this question again as I have never gotten satisfactory results from any of my other attempts. OpenAccess has 2 new versions under its belt since we last looked at this. I was hoping things may have changed because even using the TSQL query against SQL CE causes the query to take a minute or more. If I use LINQ it takes about 7 to 14 seconds, however that's with my query returning a lot more results than it needs to.

Anyhow the LINQ query I've been looking at recently is this.

IQueryable<PtAMember> result = (from p in context.PtAMembers
                                                where (p.FkptACertification == (from c in context.PtApprovedCertifications
                                                                             where c.FkosParticipant == p.PtApprovedCertification.OsParticipant.PK &&
                                                                            (!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
                                                                            c.EffectiveDate <= DateTime.Now &&
                                                                            c.FkptApprovedCertificationVoidedBy == null
                                                                             orderby c.EffectiveDate descending, c.PK descending
                                                                             select c.PK).FirstOrDefault()) &&
                                                                                   (p.LastName.ToLower().Contains(param.ToLower()) ||
                                                                                   p.FirstName.ToLower().Contains(param.ToLower()) ||
                                                                                   p.SocialSecurityNumber.Contains(param))
                                                select p).Distinct().OrderBy(PtAMembers => PtAMembers.LastName).ThenBy(PtAMember => PtAMember.FirstName);

 
but OpenAccess throws this error.

Error Message

An exception occured during the execution of '
Extent<EliteExtender.EliteDataService.Models.PtAMember>.Where(p => ((p.FkptACertification == value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass2).context.PtApprovedCertifications.Where(c => ((((c.FkosParticipant == p.PtApprovedCertification.OsParticipant.PK) AndAlso Not(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass2).excludedActionTypes.Contains(c.FkMLSosCodeActionType))) AndAlso (c.EffectiveDate <= Convert(DateTime.Now))) AndAlso (c.FkptApprovedCertificationVoidedBy == null))).OrderByDescending(c => c.EffectiveDate).ThenByDescending(c => c.PK).Select(c => c.PK).FirstOrDefault()) AndAlso ((p.LastName.ToLower().Contains(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass0).param.ToLower()) OrElse p.FirstName.ToLower().Contains(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass0).param.ToLower())) OrElse p.SocialSecurityNumber.Contains(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass0).param)))).Distinct().OrderBy(PtAMembers => PtAMembers.LastName).ThenBy(PtAMember => PtAMember.FirstName)'. See InnerException for more details.

Inner Exception

Argument expression is not valid

Stack Trace

at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type type, Int32 elementAt, Object[] groupResolutionParamValues)
at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues)

 
Maybe these will shed some light. Any ideas you have would greatly be appreciated.

Thanks!

[Garry]

Zoran,
I got a response on StackOverflow lastnight and below is the suggested code I am trying. It is very similar to the one where we tried to use a method to filter in the .WHERE clause, but instead of running slow this one throws an error. The Stackoverflow thread is http://stackoverflow.com/questions/3571135/linq-subquery-where-clause

int?[] excludedActionTypes = { 1057, 1059, 1060, 1062, 1063, 1064 };

                Func<int, int> pkLookup = n => (from c in context.PtApprovedCertifications
                                                where c.FkosParticipant == n &&
                                                (!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
                                                c.EffectiveDate <= DateTime.Now &&
                                                c.FkptApprovedCertificationVoidedBy == null
                                                orderby c.EffectiveDate descending, c.PK descending
                                                select c.PK).FirstOrDefault();

                IQueryable<PtAMember> result = (from p in context.PtAMembers
                                                where p.FkptACertification == pkLookup(p.PtApprovedCertification.OsParticipant.PK) &&
                                                                                   (p.LastName.ToLower().Contains(param.ToLower()) ||
                                                                                   p.FirstName.ToLower().Contains(param.ToLower()) ||
                                                                                   p.SocialSecurityNumber.Contains(param))
                                                select p).Distinct().OrderBy(PtAMembers => PtAMembers.LastName).ThenBy(PtAMember => PtAMember.FirstName);

Message

An exception occured during the execution of '
Extent<EliteExtender.EliteDataService.Models.PtAMember>.Where(p => ((p.FkptACertification == Invoke(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass3).pkLookup, p.PtApprovedCertification.OsParticipant.PK)) AndAlso ((p.LastName.ToLower().Contains(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass1).param.ToLower()) OrElse p.FirstName.ToLower().Contains(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass1).param.ToLower())) OrElse p.SocialSecurityNumber.Contains(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass1).param)))).Distinct().OrderBy(PtAMembers => PtAMembers.LastName).ThenBy(PtAMember => PtAMember.FirstName)'. See InnerException for more details.

Inner Exception

Invocation argument not a parameter expression
Parameter name: invocationExpression
Actual value was Invoke(value(EliteExtender.EliteDataService.Domain.Residents+<>c__DisplayClass3).pkLookup, p.PtApprovedCertification.OsParticipant.PK).

StackTrace

at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type type, Int32 elementAt, Object[] groupResolutionParamValues)
at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues)

[Zoran]

Hello Garry Clark,

 Some of the more complex sub-query expression especially those that include delegate invocation are not supported in the OpenAccess ORM Linq implementation. For this situation I really think that you should go with the direct SQL as you mentioned you were doing. There is really no way that you can top the performance of that. If direct SQL is executed slow than this is due to the SQL CE database server, but using Linq can not help you having a better performance for this type of query. 

Greetings,
Zoran
the Telerik team