SQL-290: Retrieve RctServicePlanCode via ProcessAllServicePlans¶
This NHibernate LINQ query counts rows in LC_SERVICE_PLAN_CODE matching a given service plan code. RctServicePlanService.ProcessAllServicePlans uses the count to validate the incoming service plan code before applying it, unless the code is the special "removed" sentinel checked in memory.
Classification¶
| Field | Value |
|---|---|
| SQL ID | SQL-290 |
| Operation type | Read |
| Execution method | ORM (NHibernate LINQ) |
| Database | Oracle |
| Schema | [unqualified]** |
| Command type | N/A (ORM-generated) |
| Business object | RctServicePlanService |
| Criteria property | N/A — ORM |
| Lifecycle method | ProcessAllServicePlans |
Parameters¶
| Parameter | Direction | Type | Max length | Description |
|---|---|---|---|---|
:ServicePlanCode |
Input | string | N/A | servicePlanChange.ServicePlanCode |
Tables¶
| Table | Access | Columns used |
|---|---|---|
| LC_SERVICE_PLAN_CODE | SELECT | CODE |
LINQ statement¶
Source: Services/SAIF.Services/Rct/RctServicePlanService.cs:74
// Services/SAIF.Services/Rct/RctServicePlanService.cs:74
var validServiceLevelCodes = _unitOfWork.GetRepository<Model.Rct.RctServicePlanCode>().Query();
// Services/SAIF.Services/Rct/RctServicePlanService.cs:93-95
var servicePlanCodeIsValid =
validServiceLevelCodes.Count(x => x.Code == servicePlanChange.ServicePlanCode) > 0
|| servicePlanChange.ServicePlanCode.ToLower() == ServicePlanRemovedCode;
Sample SQL¶
Inferred SQL: This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.
-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
SELECT COUNT(*)
FROM LC_SERVICE_PLAN_CODE LSPC
WHERE LSPC.CODE = :ServicePlanCode
Translation notes:
- L74 only builds the IQueryable; the statement executes at Count(...) (L94), once per service-plan change whose policy is valid.
- > 0 and the || ... == ServicePlanRemovedCode alternative (L95) are evaluated in memory.
Operations and processes¶
- RctServicePlanService.ProcessAllServicePlans — see Operations and processes
Business rules¶
- SVC-BR-POL-100 Service plan policy must exist - 'Error - The PolicyNumber '{n}' is not valid'
- SVC-BR-POL-101
- SVC-BR-POL-102 A per-row processing failure rolls back the whole batch
Sources¶
Services/SAIF.Services/Rct/RctServicePlanService.cs (L74)