LINQ Subquery help

14 posts, 0 answers
  1. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 19 May 2010 Link to this post

    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!
  2. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 25 May 2010 Link to this post

    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.
  3. DevCraft banner
  4. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 25 May 2010 Link to this post

    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!
  5. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 25 May 2010 Link to this post

    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!
  6. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 27 May 2010 Link to this post

    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.
  7. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 27 May 2010 Link to this post

    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.
  8. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 01 Jun 2010 Link to this post

    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.
  9. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 07 Jun 2010 Link to this post

    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!
  10. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 08 Jun 2010 Link to this post

    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.
  11. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 08 Jun 2010 Link to this post

    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 descAND 
                                  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
  12. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 10 Jun 2010 Link to this post

    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.
  13. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 25 Aug 2010 Link to this post

    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!
  14. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 26 Aug 2010 Link to this post

    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)
  15. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 27 Aug 2010 Link to this post

    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
    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
Back to Top
DevCraft banner