Filter decimal and date columns by string keyword

[ozzioma]

Hi, I have a need to run a free style search on multiple columns, like this

 Query2.Filter(r =>
                        r.BankAccountKey_AccountNo.ToLower().Contains(key)
                        || r.BankAccountKey_BVN.ToLower().Contains(key)
                        || r.BankAccountKey_AccountName.ToLower().Contains(key)
                        || r.BankKey_Name.ToLower().Contains(key)
                        || r.CategoryKey_Name.ToLower().Contains(key)
                        || r.GroupKey_Name.ToLower().Contains(key)
                        || r.Amount.ToString().Contains(key)
                        || r.TransactionDate.ToString("dd MMMM yyyy").ToLower().Contains(key)
                        );

 var items = await Query2.Skip(CurrentPage).Top(PageSize)
                   .OrderByDescending(x => x.TransactionDate)
                   .FindEntriesAsync(annotations);

Amount is a decimal column and TransactionDate is a DateTimeOffset column. The app I'm building has a search bar where users can type in keywords and the app returns rows matching those keywords. Like typing in "Jun Fargo" returns all rows with BankName~"Fargo" and TransactionData~"Jun". Also typing in Jun Fargo 345" should return similar rows with Amount starts with or contains ~"345".

The above filter bombed with a "Bad Request" exception. Previously I was able to manually construct the OData query and call the endpoint using Refit like this

ViewModel.QueryParams.Filter = $"CustomerId_Id eq {MobileSettings.CustomerId} and ClassifiedFlag eq true and ( contains(tolower(Description),'{keywords}') or contains(tolower(BankAccountId_AccountName),'{keywords}') or contains(tolower(BankAccountId_AccountNo),'{keywords}') or contains(tolower(CategoryId_Name),'{keywords}') or contains(tolower(GlobalId_Name),'{keywords}') or contains(tolower(BankId_Name),'{keywords}') or (startswith(cast(Amount, 'Edm.String'),'{keywords}')) )";

How do you construct such a filter/query using this library Like these

Query2.Filter(r => r.Amount.ToString().StartsWith(key));
Query2.Filter(r => r.Amount.ToString().Contains(key));
Query2.Filter(r =>r.TransactionDate.ToString("dd MMMM yyyy").ToLower().Contains(key));

Thanks in advance.

[object]

@ozzioma I suspect that the error might be due to the last clause in your statement: r.TransactionDate.ToString("dd MMMM yyyy").ToLower().Contains(key)

You should bear in mind that not every expression you write can be automatically transformed into a corresponding OData URL. For example, Simple.OData.Client doesn't support ToString format specifications, so ToString("dd MMMM yyyy") won't work.

[object]

But you can try to simplify the query, you can still use a wide range of expressions with the library.