I'm quite new with OData, EF and BreezeJS concepts. I'm now in charge of a project that was already developed with the help of Entity Framework/BreezeJS/Angular libraries. I need to improve it.
There's something I really don't understand in the OData calls generated by BreezeJS.
The thing that I'm trying to do is to list all Interests linked into a Campaign by getting the Interests Code and Labels in French language ('FR'). Basically this means writting this query in raw SQL:
select
CAMPAIGN_INTEREST.CODE,
LK_CAMPAIGN_INTEREST.VALUE
from CAMPAIGN_INTEREST
inner join LK_CAMPAIGN_INTEREST_CODE on CAMPAIGN_INTEREST.CODE = LK_CAMPAIGN_INTEREST_CODE.CODE
inner join LK_CAMPAIGN_INTEREST on LK_CAMPAIGN_INTEREST_CODE.CODE = LK_CAMPAIGN_INTEREST.CODE
inner join LK_APP_LANGUAGE on LK_CAMPAIGN_INTEREST.LANG = LK_APP_LANGUAGE.LANG
where LK_APP_LANGUAGE.LANG = 'FR';
Yes, I know that query could be more simple, the reason I written this in that way is, I put several primary/foreign keys constraints to help Entity Framework to 'discover' the model.
First thing, this is the part of the data model concerned by my problem:
The main table here is the CAMPAIGN_INTEREST one. Its contents is the following:
FK_CAMPAIGN POSITION CODE
CAMP01 01 01
CAMP01 02 04
CAMP01 05 03
CAMP02 01 04
CAMP02 02 02
The table LK_CAMPAIGN_INTEREST_CODE contains:
CODE
01
02
03
04
The table LK_CAMPAIGN_INTEREST contains the following:
CODE LANG VALUE
01 EN "Office"
01 FR "Office"
02 EN "Industry"
02 FR "Industrie"
03 EN "Innenraumleuchten"
03 FR "Interior Lighting"
04 EN "Special Lights"
04 FR "Feux spéciaux"
And the latest one, LK_APP_LANGUAGE, contains:
LANG
EN
FR
Now, I'm trying to debug my BreezeJS query by directly sending the OData URL.
The following query returns only the "Office" value, in both 'EN' and 'FR' language, for the 'CAMP01' campaign (note: this URL was generated by BreezeJS library):
http://localhost:50487/breeze/Breeze/CAMPAIGN_INTEREST?$filter=(FK_CAMPAIGN eq 'CAMP01') and (LK_CAMPAIGN_INTEREST_CODE/LK_CAMPAIGN_INTEREST/any(x2: x2/VALUE eq 'Office'))&$expand=LK_CAMPAIGN_INTEREST_CODE/LK_CAMPAIGN_INTEREST&
Now, I want to filter on the application language, so I wrote the following:
http://localhost:50487/breeze/Breeze/CAMPAIGN_INTEREST?$filter=(FK_CAMPAIGN eq 'CAMP02') and (LK_CAMPAIGN_INTEREST_CODE/LK_CAMPAIGN_INTEREST/any(x2: x2/LANG eq 'FR'))&$expand=LK_CAMPAIGN_INTEREST_CODE/LK_CAMPAIGN_INTEREST&
This returns me ALL the languages, not only the 'FR' one.
When I try to change the 'any' keyword into 'all' in the URL above, it returns nothing.
The thing that I don't understand is, when I try to filter on the VALUE column (first URL shown here) it works. If I try to filter on the CODE column it works too. The only columns that doesn't work is the LANG one.
The thing you can see on that partial diagram is, the LK_APP_LANGUAGE is used in several LK_* tables (that's why I put another LK that I'm not using here). In fact, in the application, every LK_ were translated, that why I'm trying to filter on the LANG column.
How can I achieve this by using OData URL filter conditions?
I'm not putting C# and JS code here, because I don't know if it's relevant for now. If you need that, just put a comment and I'll edit the post.
Any help will be very much appreciated! I lost one day by dealing with this. Thank you very much!
