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

Error in conditional filtering

11 Answers 199 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 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

Accepted

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.

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.

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!

Product Bundles

DevCraft

Web

Mobile

Document Management

Desktop

Reporting & Mocking

Automated Testing

CMS

UI/UX Tools

Debugging

Free Tools

Error in conditional filtering

11 Answers, 1 is accepted