Error in conditional filtering

12 posts, 1 answers
  1. Adam
    Adam avatar
    39 posts
    Member since:
    Jun 2009

    Posted 18 Aug 2009 Link to this post

                var query = from p in scope.Extent<Person>() 
                            where ((p.PersonType.Id == 2) &&  
                                   (activeOnly ? p.Active : true) &&  
                                   (todaysOnly ? SqlMethods.DateDiffDay(p.DateModified, DateTime.Now) == 0 : true)) 
                            select p; 
     

    PersonType Table
    ID    Name    Description    Active    DateModified
    1    Employee    NULL          1    2009-08-10 17:52:01.740
    2    Customer    NULL          1    2009-08-10 17:52:01.740
    3    Vendor      NULL          1    2009-08-10 17:52:01.740
    4    Affiliate   NULL          0    2009-08-10 17:52:01.740

    Problem #1:
    base {System.Exception} = {"Execution of 'System.Data.Linq.SqlClient.SqlMethods:DateDiffDay(DateTime,DateTime)' on the database server side currently not implemented."}

       var query = from p in scope.Extent<Person>() 
                            where ((p.PersonType.Id == 2) &&  
                                   (activeOnly ? p.Active : true)) 

    Problem #2: base {System.Exception} = {"Please reformulate the query so that the parameter appears on the right side."}
    So, how do I filter the set based on the variables without
    CASE a & b, a only, b only, not a or b, etc.

    It was working in Linq to SQL

    I also tried
      var query = from p in scope.Extent<Person>() 
                            where ((p.PersonType.Id == 2) 
                            && (!todaysOnly ? true : DateTime.Now.Date.Equals(p.DateModified.Date)) 
                            select p; 

    base {Telerik.OpenAccess.OpenAccessException} = {"Error executing query: Telerik.OpenAccess.RT.sql.SQLException: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.\r\nStatement(s) could not be prepared.\r\n   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.P...


  2. Answer
    PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 19 Aug 2009 Link to this post

    Hello Adam,

    What you are facing are several problems with our LINQ implementation. Note that we are aware of those problems and we are working on solving them.
    The first problem comes from that we are currently not supporting execution on custom functions on the server side. This would mean that you currently cannot execute the SqlMethods.DateDiffDay() as our LINQ implementation will try to execute it on the server and that is not yet supported.
    The other problem comes from that we are not yet able to correctly translate the conditional statement a?b:c. The most elegant way to workaround this would be to use dynamic LINQ. Here is an example:
    var result = from c in scope.Extent<Product>() 
                             where c.Supplier.SupplierID == 1  
                             select c; 
                if (discontinuedOnly) 
                { 
                    result.Where(c => c.Discontinued == true); 
                } 
                if (todayOnly) 
                { 
                    result.Where(c => c.DateDelivered = DateTime.Today); 
                } 
    Using pattern similar to the code above you can dynamically add additional filtering criteria that will be taken into account once the query is executed.

    All the best,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. DevCraft banner
  4. Adam
    Adam avatar
    39 posts
    Member since:
    Jun 2009

    Posted 20 Aug 2009 Link to this post

    The first part works, but the second part does not (comparing date to DateTime.Today).


  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 21 Aug 2009 Link to this post

    Hello Adam,

    Have you tried it this way:
      result.Where(c => c.DateDelivered == DateTime.Today);  
    Somehow one of the "=" has been committed the first time we send you the code. Excuse us for any inconvenience caused.

    Best wishes,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  6. Adam
    Adam avatar
    39 posts
    Member since:
    Jun 2009

    Posted 21 Aug 2009 Link to this post

    No, I had caught this, it is still not matching.

    query.Where(c =>  c.DateModified.Date == DateTime.Today);
  7. Adam
    Adam avatar
    39 posts
    Member since:
    Jun 2009

    Posted 21 Aug 2009 Link to this post

    The issue is that the date is stored as a DateTime (2009-08-21 10:53:04.673) and the DateTime.Today is 12:00:00, that is why I was doing the DateDiffDay(c.DateModified, DateTimeToday) == 0;


  8. Adam
    Adam avatar
    39 posts
    Member since:
    Jun 2009

    Posted 21 Aug 2009 Link to this post

    I just did it this way:

      query.Where(c =>   
                        c.DateModified.Date.Day == DateTime.Today.Day && 
                        c.DateModified.Date.Month == DateTime.Today.Month && 
                        c.DateModified.Date.Year == DateTime.Today.Year); 

    A little bit longer, but it still works.

    Basically, once I have the query.Where(c => <condition>) ;

    I am able to do what I need.
  9. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 24 Aug 2009 Link to this post

    Hi Adam,

    Yes. This solution is indeed somehow long and not really nice for which we are really sorry. However we are actively working on implementing better and much richer support for LINQ. We hope that with our next release we will be much closer to fully supporting most of the LINQ function so you (as our customer) should be able to freely do your coding without the need of workarounds. Once again sorry for any inconvenience caused.

    Regards,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  10. paolo
    paolo avatar
    9 posts
    Member since:
    Jun 2009

    Posted 06 Feb 2010 Link to this post

    i tried the work around looking like:

    var registry = (from p in scope.Extent<TrackerRegistration>() select p);
                    registry.Where(p => p.AffiliateID == (AffiliateID ?? p.AffiliateID)
                            && p.AffiliateID == (AffiliateID ?? p.AffiliateID)
                            && p.Country == (Country ?? p.Country)
                            && p.CreateDate == (CreateDate ?? p.CreateDate)
                            && p.Email == (Email ?? p.Email)
                            && p.FirstName == (FirstName ?? p.FirstName)
                            && p.IPAddress == (IPAddress ?? p.IPAddress)
                            && p.IsConfirmed == (IsConfirmed ?? p.IsConfirmed)
                            && p.LastName == (LastName ?? p.LastName)
                            && p.MacAddress == (MacAddress ?? p.MacAddress)
                            && p.MainGoal == (MainGoal ?? p.MainGoal)
                            && p.ModifiedDate == (ModifiedDate ?? p.ModifiedDate)
                            && p.UserID == (UserID ?? p.UserID)
                            && p.UserName == (UserName ?? p.UserName));

    return registry.ToList()

    but i still get the "Please reformulate the query so that the parameter appears on the right side." error. Any workarounds?

    Thanks!
  11. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 10 Feb 2010 Link to this post

    Hi paolo,

    The exception you are seeing is caused by an incorrect parameter calculation on our side. We are currently fixing that and the fix will be available in our next build.

    Kind regards,
    Petar
    the Telerik team

    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
    Follow the status of features or bugs in PITS and vote for them to affect their priority.
  12. Greg Lynne
    Greg Lynne avatar
    31 posts
    Member since:
    Mar 2010

    Posted 30 Mar 2010 Link to this post

    Was this fixed in your relase Telerik_OpenAccess_ORM_2010_1_312 using the OpenAccess.35 Extensions, as I still get the error when attempting a simlar query.
  13. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 07 Apr 2010 Link to this post

    Hi Andrew Brown,

    It seems that we have not covered all the possible scenarios and in your case the LINQ query is not translated correctly. We will try to fix that for our next service pack.
    Please find your Telerik points updated for bringing this issue to our attention.

    Kind regards,
    Petar
    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