OData use cases

[zewa666]

the app I'm currently working on makes use of OData sent from Slickgrid to a C# backend that converts it to dynamically generated SQL code using https://github.com/DynamicODataToSQL/DynamicODataToSQL that is sent to a DB so that I get filtering/sorting and pagination for free. the app itself goes a bit further in that through a table based config, instances with custom validators, permissions and auditing can be configured directly in DB. in essence a generic way to fight all those excel files flying around ;)

one next potential use case is going to be combining it with Spfx to query data from Sharepoint Onlines OData backend from within a webpart.

I do wonder what others here are using it for. perhaps @jr01 and @ghiscoding could share your stories as well?

[ghiscoding]

Personally, I don't use the OData anymore and most of the enhancements that were added to the OData Service over the years came from @jr01 (thanks to Jurriaan for all of these contributions). On the other hand, it seems that we are back at something similar to what we discussed a while ago in this older discussion OData Metadata Parser 😄 It might not be a parser that you're looking for, but it's still the idea of dynamically querying.

If what you have in mind is expanding the OData Service by a lot, I would suggest to create a separate package that can be downloaded as per users discretion and loaded as an external backendServiceApi. I currently only have 2 Services (OData, GraphQL), but I have always imagined that eventually someone else will create some extra Services for other platforms like Oracle and others by creating new package(s) in here (perhaps I dreamt too big and OData/GraphQL are already conquering the world haha)

EDIT

So after taking a quick look at the link you provided, I definitely think this should be a separate package since it surely won't be used by everyone and making it optional (not part of OData Service) would be ideal. It's an interesting subject, I guess soon in the future you'll be able to send that over to ChatGPT to convert it to SQL 😆 If you can do it naturally with this package, it's a great addition.

[zewa666]

tbh I'm not sure I'd add much more to the service on the frontend. perhaps support for IN statements, cascading filters and minor things. I'm a firm believer that using the BFF pattern, the backend should prepare the data in such a way that the frontend can work easily with it and enhance its posts with more data for the route back (update, insert, delete). so in my case the C# backend
makes sure to translate sql table structures to proper ColumnDefinitions, the frontend has no idea who feeds what or for what case

no I was really more after the business use cases you're fullfilling with OData, or for Graphql as an alternative, not on how the service itself could be enhanced. So I guess your use case @ghiscoding was to fetch SFDC data as an example and show it in the grid within a lightning component.

[ghiscoding]

I built Angular-Slickgrid with OData at the beginning and only used it in the first project, then we switched to GraphQL. The OData was a simple Get all RestFull API with a straight up "here's the data, please display it all" with simple Sort/Filter/Pagination working and that was enough. We were using C# Backend at the time for both OData/GraphQL. Now it's mostly used in Salesforce but it's with simple straight up 1 fetch to get all data sent to a JSON object (no backend services except the fetch) and I'm not even in that team anymore, I keep adding stuff to the project because I love the project. It's mostly all done in my spare time lol. But anyway, I think with the next major, I will have covered everything that I wanted to do and I'll be done with the project in general (in the coming months).

For the IN, it's already supported I think

slickgrid-universal/packages/odata/src/services/grid-odata.service.ts

Lines 420 to 427 in 0462f17

	if (searchTerms && searchTerms.length > 1 && (operator === 'IN' || operator === 'NIN' || operator === 'NOTIN' || operator === 'NOT IN' || operator === 'NOT_IN')) {
	// when having more than 1 search term (then check if we have a "IN" or "NOT IN" filter search)
	const tmpSearchTerms: string[] = [];
	if (operator === 'IN') {
	// example:: (Stage eq "Expired" or Stage eq "Renewal")
	for (let j = 0, lnj = searchTerms.length; j < lnj; j++) {
	tmpSearchTerms.push(`${fieldName} eq ${searchTerms[j]}`);
	}

[zewa666]

yeah right its just you can express

GET /service/Products?$filter=Name eq 'Milk' or Name eq 'Cheese' or Name eq 'Donut'

also with

GET /service/Products?$filter=Name in ('Milk', 'Cheese', 'Donut')

which when turned into SQL can make a difference perf wise. besides the query length is shorter.

---

dont say everything, there might just loom the next cool feature after the corner ;)

[jr01]

Our application is used by accounting offices to check and report on their client's financial bookkeeping. We import bookkeeping data from ERPs. We use Angular Slickgrid in various parts of the application, e.g. to display financial transactions, the user can sort/filter/add columns/export to excel, etc.

On the backend we have c#/ASP.NET Core 8 and use the Microsoft.AspNetCore.OData library to expose (some of our) Entity Framework Core (EF) entities that get the data from Azure/MS SQL. See https://learn.microsoft.com/en-us/odata/webapi-8/overview for details.

About IN: Most relational databases translate IN to OR and the query plan the database generates is the same. If query length is a true concern, I would start to question the problem and come up with a different solution (either functionally, or technically).

[zewa666]

yeah thats true for e.g Oracle and MSSQL but e.g not for Postgres, where the longer the list gets the diff can be quite noticable https://ottertune.com/blog/query-best-practices-when-should-you-use-the-in-instead-of-the-or-operator

the length isnt yet a concern but could become, depending how wild the users go with their queries ;)

[jr01]

Interesting, I didn't know that about Postgres!

In our case EF generates optimal queries. I just happened to be doing some experiments with EF today and just configured my project to use the Postgres provider https://www.npgsql.org/efcore/ and ran a Postgres docker container ( docker run -p:5432:5432 --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres). Then did a simple c# Linq query:

await dbContext.Customers.Where(x => x.Name == "A" || x.Name == "B").ToListAsync();

And that translates to:

SELECT a."Id", a."Name"
FROM "Customers" AS a
WHERE a."Name" IN ('A', 'B')

But, yeah, you would have to be using EF ... or do the optimization in that DynamicODataToSQL library.

Don't get me wrong here though. I look forward to a PR that changes the Slickgrid IN operator to use the oData in keyword as mentioned here :)

[zewa666]

thanks for checking it out. it matches with what I've seen from the query plan. EF is not an option for me sadly due to the dynamic nature (multi tenant, unknown tables but only generated tblvfunctions are exposed).

But so for me it sounds you guys also havent tried to target your frontend directly towards a standard source but always wrap the access with your own backend right?

[jr01]

Yes, the project I mentioned also uses the BFF pattern, it's actually multi-tenant with a fixed database schema and uses the tenant per row approach.