This is a migrated thread and some comments may be shown as answers.

Error in conditional filtering

11 Answers 149 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Adam
Top achievements
Rank 1
Adam asked on 18 Aug 2009, 11:51 PM
            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...


11 Answers, 1 is accepted

Sort by
0
Accepted
PetarP
Telerik team
answered on 19 Aug 2009, 03:25 PM
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.
0
Adam
Top achievements
Rank 1
answered on 20 Aug 2009, 02:55 PM
The first part works, but the second part does not (comparing date to DateTime.Today).


0
PetarP
Telerik team
answered on 21 Aug 2009, 11:58 AM
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.
0
Adam
Top achievements
Rank 1
answered on 21 Aug 2009, 02:56 PM
No, I had caught this, it is still not matching.

query.Where(c =>  c.DateModified.Date == DateTime.Today);
0
Adam
Top achievements
Rank 1
answered on 21 Aug 2009, 03:14 PM
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;


0
Adam
Top achievements
Rank 1
answered on 21 Aug 2009, 03:21 PM
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.
0
PetarP
Telerik team
answered on 24 Aug 2009, 03:09 PM
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.
0
paolo
Top achievements
Rank 1
answered on 06 Feb 2010, 06:59 AM
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!
0
PetarP
Telerik team
answered on 10 Feb 2010, 07:12 PM
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.
0
Greg Lynne
Top achievements
Rank 1
answered on 31 Mar 2010, 03:55 AM
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.
0
PetarP
Telerik team
answered on 07 Apr 2010, 09:18 AM
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.
Tags
LINQ (LINQ specific questions)
Asked by
Adam
Top achievements
Rank 1
Answers by
PetarP
Telerik team
Adam
Top achievements
Rank 1
paolo
Top achievements
Rank 1
Greg Lynne
Top achievements
Rank 1
Share this question
or