How to ignore case using breeze FilterQueryOp

Question

I am using breeze to query data from the server and seem to be running into problems. Is there a way to filter this data and ignore cases or making the value from the field a lower case? Example:

var term = "john"; 
query = query.where("Name", "contains", Term);

The problem I am having is if the 'Name' field contains John with capital 'J', It return false but returns true if I change term to 'John'. I know this is case issue but how can I make breeze ignore the casing? without using jquery.each.

Thanks. Any help will be greatly appreciated.

Renatarenate

Answer 1

Ok, there are two parts to this. Breeze supports a LocalQueryComparisonOptions object that is used for all localQueries.

    var lqco = new breeze.LocalQueryComparisonOptions({
        name: "custom comparison options",
        isCaseSensitive: false,
        usesSql92CompliantStringComparison: true
    });
    // either apply it globally
    lqco.setAsDefault();
    // or to a specific MetadataStore
    var ms = new breeze.MetadataStore({ localQueryComparisonOptions: lqco });
    var em = new breeze.EntityManager( { metadataStore: ms });

You should set this once at the beginning of your application. In this example, all localQueries performed after this point will be case insensitive.

The problem is that unless your database is ALSO set to "match" these settings ( performing this differs by database vendor), then remote queries against the server will return different results then the same query applied locally.

Basically, Breeze cannot set the "server" side implementation, so the recommendation is usually to create a localQueryComparisons object that matches your server side database settings.

Hope this makes sense.

Cyd

Either I am missing something or my breeze.js is not up to date but seem to be getting this error. 'Failed to load composed module (viewmodels/shell). Details: LocalQueryComparisonOptions is not defined' I am using it in durandal by the way. Thanks for your super quick response. – Renatarenate

Sorry, it should be breeze.LocalQueryComparisionOptions. I updated the post. – Cyd

Works!!! Didn't work at first but like you said, match database settings. So I had to change from the database. I am using Oracle DB by the way. Awesome! thanks a lot mate! – Renatarenate

If anyone run into this problem on an Oracle DB, I added the code above from Jay then modified a logon trigger to alter session variables for DB users. – Renatarenate

Answer 2

In my opinion there is a simpler approach to this.

By default OData is case sensitive, but nonetheless provides functions to transform a string to lower or upper case. So to fire a case-insensitive query to the server simply modify your code as follows:

var term = "john"; 
query = query.where("tolower(Name)", breeze.FilterQueryOp.Contains, term.toLowerCase());

Thus OData is told to transform the subject to lower case before comparing it to your search string, which has been converted to lower case before sending it to the server.

Fruitless

Answer 3

If anyone run into this problem on an Oracle DB, I added the code above from Jay Traband then modified a logon trigger to alter session variables for DB users. Set the following values:

ALTER SESSION SET nls_comp = linguistic; ALTER SESSION SET nls_sort = binary_ci

Hope this helps someone out. I love Breeze!!!

Renatarenate