Print Page | Close Window

Dynamic Query - create PredicateDescription for null values or property to property comparisons

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2775
Printed Date: 15-Oct-2026 at 2:57pm


Topic: Dynamic Query - create PredicateDescription for null values or property to property comparisons
Posted By: pk55
Subject: Dynamic Query - create PredicateDescription for null values or property to property comparisons
Date Posted: 16-Jun-2011 at 4:18pm
Does the 6.1.0 new feature F1672 below support comparing a property to null (not empty string but a NULL value comparison on any nullable type)  or comparing one property to another property?

"Support simple functions with no args in PredicateDescription. [F1672] "

We're still on 6.0.9 and in that release the PredicateDescription only takes an object value to compare to and I haven't been able to find a way to do either of those. 
 
In this example below, _actualInputType is a type passed at runtime to be a data model entity; "left" is a string property name for a db-backed property:
// this doesn't work
var p = new PredicateDescription(_actualInputType, left, FilterOperator.IsEqualTo, Expression.Constant(null));
 
Or this:
// can't find a way to pass a reference to the property
var p = new PredicateDescription(_actualInputType, "ID", FilterOperator.IsEqualTo, ???);
where ??? is another property on the _actualInputType entity (like ParentID).
 
Both types of expressions are obviously simple to build using linq but I'm combining lots of dynamic predicates into composites and can't seem to find a way to create a PredicateDescription that mimics any of these expressions:
Contacts.Where(x => x.ID != x.ParentID)
Contacts.Where(x => x.ID == null)
Contacts.Where(x => String.IsNullOrEmpty(x.ID))


Replies:
Posted By: DenisK
Date Posted: 17-Jun-2011 at 1:21pm
Hi pk55;

The null value comparison should work in 6.0.9 as shown below in my test.

var empType = typeof(Employee);
var mainPredicate = new PredicateDescription(empType, Employee.EntityPropertyNames.Region, 
   FilterOperator.IsEqualTo, null);
var q1 = mgr.Employees.Where(mainPredicate);
var r1 = q1.ToList();

Does it not work for you?

Comparing to another property hasn't been implemented yet. I'll see about adding this to our feature request list and let you know.

Posted By: pk55
Date Posted: 17-Jun-2011 at 3:20pm

I thought I tried using just null but maybe it failed before for some other reason :-) so I tried the Expression.Constant(null) and that didn't work. 

I did test again with null and it works so I can just build a composite predicate up from a null comparison and a string.empty comparison (and NOT them for the opposite case).
 
However, this doesn't work if you try to compare to a numeric column where the column is set to NOT nullable in the db. I get an error just trying to build the predicate description:
 
Exception Type: IdeaBlade.EntityModel.EntityServerException
Message: Unable to convert '' to type 'System.Int16'
RemoteExceptionName: System.InvalidCastException
RemoteExceptionDetails: System.InvalidCastException: Unable to convert '' to type 'System.Int16'
   at IdeaBlade.Linq.PredicateDescription.ValidateValue()
   at IdeaBlade.Linq.PredicateDescription..ctor(Type instanceType, String propertyName, FilterOperator filterOp, Object value, Boolean ignoreCase)
 
For numeric columns that are nullable in the db, it works just fine. 
 
Try it in your NorthwindIB on Employee.RowVersion (it's an int not null):
 
var empType = typeof(Employee);
 
// should throw an error here
var mainPredicate = new PredicateDescription(empType, Employee.EntityPropertyNames.RowVersion, 
   FilterOperator.IsEqualTo, null);
 
var q1 = mgr.Employees.Where(mainPredicate);
var r1 = q1.ToList();
 
 
One other request/question: for some of our data, the actual column datatype in the db is string but it's logically typecast as various datatypes (numerics, dates, our own internal types,etc.).  I need to be able to wrap a Cast around the string column so I can use the various numeric/date comparisons.  Any idea how that could be done?
 
 

Posted By: pk55
Date Posted: 17-Jun-2011 at 5:07pm
This also breaks comparing datetimes that aren't nullable in the db with the same kind of error:
 
Exception Type: IdeaBlade.EntityModel.EntityServerException
Message: Unable to convert '' to type 'System.DateTime'
RemoteExceptionName: System.InvalidCastException
RemoteExceptionDetails: System.InvalidCastException: Unable to convert '' to type 'System.DateTime'
   at IdeaBlade.Linq.PredicateDescription.ValidateValue()
   at IdeaBlade.Linq.PredicateDescription..ctor(Type instanceType, String propertyName, FilterOperator filterOp, Object value, Boolean ignoreCase)
We have a generic interface to allow users to filter data and if we have to disable certain filter operators based on the data model not allowing nulls, it will be a real pain.

Posted By: DenisK
Date Posted: 20-Jun-2011 at 11:45am

Hi pk55; 

Hmm, you’re right. This seems to be a bug since the following expression works. 

var query1 = mgr.Employees.Where(e => e.RowVersion != null); 
//or
Expression<Func<Employee, bool>> expr = e => e.RowVersion != null;
var query2 = mgr.Employees.Where(expr);

I’ll confirm with a senior engineer and get back to you. 

Regarding your other question about wrapping a cast, do you mean you want to do something like this?

var pd = new PredicateDescription(typeof(Employee), “SomeStringProperty”, FilterOperator.IsNotEqualTo, someIntValue); 
//Where “SomeStringProperty” will be casted to an Integer?

Posted By: pk55
Date Posted: 20-Jun-2011 at 12:31pm

Take for example, trying to represent this (obviously somewhat made up) SQL query against NorthwindIB.Order using the ShipPostalCode, which is a string field, but treat it as an int.  It will return 3 rows (at least in my copy of the db):

Select * from [Order] where ShipCountry = 'USA' and

(convert(int, ShipPostalCode) > 50000 and convert(int, ShipPostalCode) < 81000)
If I try to represent this:
 
// running sync here
var someAreas = _entityManager.Orders.Where(x => x.ShipCountry == "USA" &&
   (Convert.ToInt32(x.ShipPostalCode) > 50000 & Convert.ToInt32(x.ShipPostalCode) < 81000)).Execute().ToList();
 
EF has no idea how to convert the ToInt32 to a store-backed function so it will complaiin.  I can however, do this if I first treat it as AsEnumerable() so Linq to Objects can do the casting:
 
var someAreas = _entityManager.Orders.AsEnumerable().Where(x => x.ShipCountry == "USA" &&
   (Convert.ToInt32(x.ShipPostalCode) > 50000 & Convert.ToInt32(x.ShipPostalCode) < 81000)).Execute().ToList();
 
This will have the unfortunate side-effect of not applying any where clause on the SQL server (it will bring all Orders to the BOS) and then the filtering happens there.  So I can break that up into two parts to first limit the data on the SQL server and then only do the filtering of the reduced set on the BOS:
 
var someAreas = _entityManager.Orders.Where(x => x.ShipCountry == "USA").AsEnumerable().
  Where(x => 
       (Convert.ToInt32(x.ShipPostalCode) > 50000 & Convert.ToInt32(x.ShipPostalCode) < 81000))
 .Execute().ToList();
 
So finally back to the question of what kind of predicate description can I create that will represent this dynamically at runtime?  Obviously, at least 3 of them; one for the ShipCountry == "USA" and two other that has the convert with the appropriate convert type (which I know the typecasting to do) all composited together.  The big issue is how to specify the conversion to do and the property name in only one string? 
 
So would something like this be possible?
 
var pd1 = new PredicateDescription(typeof(Order), “ShipCountry”, FilterOperator.IsEqualTo, "USA"); 
 
var pd2 = new PredicateDescription(typeof(Order), “Convert.ToInt32(ShipPostalCode)”, FilterOperator.IsGreaterThan, 50000); 
 
var pd3 = new PredicateDescription(typeof(Order), “Convert.ToInt32(ShipPostalCode)”, FilterOperator.IsLessThan, 81000); 
 
 

Posted By: DenisK
Date Posted: 21-Jun-2011 at 10:15am
pk55;

Thanks for the clarification and example. I've submitted this request to a senior engineer and will let you know as soon as I hear back.

I've also submitted the "null" bug and the "property to property comparison" in a PredicateDescription feature request. We may be able to give you an early patch for this bug fix if you're interested. Just let me know. We are also prioritizing feature implementations for 6.1.2 release so I will update you if this feature request will make it in 6.1.2.

Posted By: pk55
Date Posted: 21-Jun-2011 at 10:19am

Thanks Denis.  We would definitely like this fixed in 6.1.2 and would take an early patch when available.  There is also a forum post about the Distinct iterator not working in 6.1.1 (I kind of hijacked that post as it was related to my issue with Distinct).  Can you look into that and see if a fix for that can be included?

 
http://www.ideablade.com/forum/forum_posts.asp?TID=2679&title=async-projections-navigation-properties-and-fake-entity-managers - http://www.ideablade.com/forum/forum_posts.asp?TID=2679&title=async-projections-navigation-properties-and-fake-entity-managers

Posted By: DenisK
Date Posted: 21-Jun-2011 at 10:40am
No problem. I will send you a link to the early patch when it becomes available.

Yes, I'm looking into that issue right now.

Posted By: DenisK
Date Posted: 27-Jun-2011 at 4:33pm
Hi pk55;

var pd1 = new PredicateDescription(typeof(Order), “ShipCountry”, FilterOperator.IsEqualTo, "USA"); 
 
var pd2 = new PredicateDescription(typeof(Order), “Convert.ToInt32(ShipPostalCode)”, FilterOperator.IsGreaterThan, 50000); 
 
var pd3 = new PredicateDescription(typeof(Order), “Convert.ToInt32(ShipPostalCode)”, FilterOperator.IsLessThan, 81000); 

Regarding the above feature request, it looks like we're not going to implement this. As you've already discovered, "EF has no idea how to convert the ToInt32 to a store-backed function so it will complain". You had to call AsEnumerable() to bring it down to cache first, and then apply the conversion. We will likely encounter the same issue if we try to implement this with PredicateDescription.

Fortunately, there's a workaround and it only works with CacheOnly query as is the case using normal LINQ query against EF. The workaround is to implement a custom property that provides the conversion.

public partial class OrderSummary {

    #region EntityPropertyNames
    public partial class EntityPropertyNames {
      public const String ShipPostalCodeAsNumber = "ShipPostalCodeAsNumber";
    }
    #endregion EntityPropertyNames

    public int ShipPostalCodeAsNumber {
      get {
        return Convert.ToInt32(this.ShipPostalCode);
      }
    }
}

var pd0 = new PredicateDescription(typeof(OrderSummary), OrderSummary.EntityPropertyNames.ShipPostalCodeAsNumber, 
        FilterOperator.IsGreaterThan, 87000);

var pd1 = new PredicateDescription(typeof(OrderSummary), OrderSummary.EntityPropertyNames.ShipPostalCodeAsNumber,
        FilterOperator.IsLessThan, 88000);

var combinedPd = pd0.And(pd1);

var r0 = baseQuery.With(QueryStrategy.CacheOnly)
        .Where(combinedPd)
        .ToList();

Posted By: pk55
Date Posted: 27-Jun-2011 at 11:21pm
Unfortunately, we need server-side filtering; we already do pretty much exactly this same kind of thing on the SL client (it does the dynamic conversion of a string property to whatever datatype the user has decided it represents).  That works fine when using it in Linq on the SL client for cacheonly queries but I'm trying to filter data before it hits the entity server.  Bringing data back to the entity server to filter on potentially hundreds of thousands of rows isn't going to be pretty.
 
Look at this post about the exact same issue:
http://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-meth - http://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-meth
 
The solution adds an EDM function that tells EF how to compose the query so that it runs on the Db server to convert strings to numeric data. Of course, this solution only works on the entity server since there isn't a Silverlight version of System.Data.Objects.DataClasses but for me, getting an entity server edm function is exactly what I need. 
 
Since we have an EF data model project to use just for running LinqPad queries against our domain data model, I was able to test this out with a LinqPad query and verified that the SQL generated hits the DB (using SQL profiler) with the CAST in the where clause. EXACTLY what I need.
 
So I added the same thing to our DevForce partial class (the EF project just links to the EDMX in the normal DevForce project) with the appropriate compiler directive to only add that method and the using clause in if it's not Silverlight.
 
Unfortunately, I can't figure out a way to build a predicate description that takes the static method that represents the EDM function since it takes method arguments!  At runtime, I get an error saying it can't find that "property" on the class. Any ideas?  All the examples I see of simple methods like Count() in the predicate description are operating on a navigation set and don't take any arguments.

Posted By: DenisK
Date Posted: 28-Jun-2011 at 4:05pm
pk55;

Nice job finding that post. I've tried it as well and can confirm that it works just as you described it.

I've re-opened the feature request and I'll update you as soon as I hear back from the development team.

Posted By: pk55
Date Posted: 28-Jun-2011 at 4:35pm
Yes that post opens up all sorts of cans of worms, I mean, possibilities :-)
 
I just tried this in our normal DevForce server project and can use it just fine in normal Linq to Entities queries so if I could just find a way to use it in a generic predicate it would be great.
 
Thanks for looking into this again.

Posted By: DenisK
Date Posted: 06-Jul-2011 at 7:59pm
pk55;

I've received an update. Support for model defined function has been on our feature request list. 

Unfortunately, the workaround that you found requires an EdmFunctionAttribute to be applied. We can't directly support this in our PredicateDescription as it will cause DevForce client assemblies to depend on Entity Framework assemblies. Our rule is that it's okay for DF server assemblies to depend on EF but not on the client.

I don't currently have a timeline on when model defined function will be supported. Is this a high priority for you? Do you already have a workaround in place?

My apologies for the inconvenience.

Posted By: pk55
Date Posted: 06-Jul-2011 at 8:23pm
We currently do not have a workaround and it is a relatively high priority.   Long term, these would be very useful for all sorts of things and personally I think there should be an exception to allow only server-side access rather than a somewhat dogmatic approach to "you-get-it-all-or-you-get-nothing".  Maybe my thinking is a bit selfish since I only want to execute these server-side (we already have a separate client-side approach that works fine).
 
I'll look into a hack to work around this. I'll let you know.
 
Thanks


Print Page | Close Window