Creating ArcGIS Service (FeatureAccess) with dynamic datasource?

Question

I am using ArcGIS Desktop and ArcGIS Server version 10.3. I have also created an enterprise geodatabase in Oracle 12C.

In the SDE schema I have a table containing a road network (about 10000 links), for the sake of brevity let's assume that the table has the following structure:

LINKS_TABLE

ID (NUMBER(10,0)), SHAPE (ST_GEOMETRY)

Furthermore, I have a second table which holds link-related attributes (e.g. vehicles per hour). In this second table I store data for different scenarios (e.g. vehicles per hour in weekdays, in weekends, during summer, during winter, etc). The table structure is the following:

SCENARIO_TABLE 

ID (NUMBER(10,0)), LINK_ID (NUMBER(10,0)), SCENARIO_ID(NUMBER(4,0)), SCENARIO_VALUE(NUMBER(10,0)).

I want to display the aforementioned network on a web application using either leaflet (ESRI Leaflet) or OpenLayers. I need to be able to select different scenarios (most likely more than one at the same time) and display them as different layers. This means the datasource for each layer will be something like

SELECT LINKS_TABLE.ID, LINKS_TABLE.SHAPE, SCENARIO_TABLE.SCENARIO_VALUE FROM LINKS_TABLE INNER JOIN SCENARIO_TABLE ON LINKS_TABLE.ID = SCENARIO_TABLE.LINK_ID WHERE SCENARIO_TABLE.LINK_ID = XXX

I want to deliver the output as an ArcGIS FeatureAccess layer.

The SCENARIO_TABLE will constantly be updated by users (or by background processes), so I cannot export the required services beforehand.

How can I create an ArcGIS Service where I can pass the scenario_id as an argument during runtime?

The web application will most probably (99%) be written in ASP.NET using C#.

Answer 1

Create a view with your join query, but without the filter where :

SELECT LINKS_TABLE.ID, LINKS_TABLE.SHAPE, SCENARIO_TABLE.SCENARIO_VALUE FROM LINKS_TABLE INNER JOIN SCENARIO_TABLE ON LINKS_TABLE.ID = SCENARIO_TABLE.LINK_ID

Publish it as a feature layer.

Now in your C# code, you create as much layers as you want, with the same feature layer from above, and apply a queryfeatures to each one, with your filter WHERE LINK_ID = XXX