Skip to main content

Database to EPANET Update Strategy

Date: October 16, 2025 Status: Design Document Purpose: Define the strategy for applying database changes to EPANET's in-memory model before running simulations

Executive Summary​

This document outlines the approach for synchronizing changes made in the database with EPANET's in-memory model structure. Currently, the system loads networks from .inp files, meaning database modifications are not reflected in simulations unless manually exported to an INP file first.

Goal: Enable database changes to flow directly to EPANET before running hydraulic and quality simulations, eliminating the need for manual INP file regeneration.

Current System Analysis​

What EXISTS Today​

1. Database Update Capabilities (API Layer) ✅​

The following controllers successfully update the database:

ControllerUpdatesStatus
PipeController.csDiameter, Length, Roughness, MinorLoss, KWall (LinkProperty)✅ Implemented
PumpController.csAll pump properties via LinkProperty table✅ Implemented
ValveController.csDiameter, ValveType, InitSetting, MinorLoss, InitStatus✅ Implemented
TankController.csElevation, levels, InitQual, BulkCoeff (NodeProperty), MixingModel✅ Implemented
JunctionController.csElevation, BaseDemand, Pattern✅ Implemented
ReservoirController.csInitialHead, Pattern✅ Implemented
PatternController.csPattern multipliers✅ Implemented
DataCurveController.csCurve X,Y points✅ Implemented
OptionsController.csSimulation options (units, headloss, demand model, etc.)✅ Implemented
TimeParametersController.csDuration, timesteps, report timing✅ Implemented
SimpleControlsController.csSimple control rules✅ Implemented
RuleBasedControlsController.csRule-based controls✅ Implemented

Example from PipeController:

[HttpPut("{id}")]
public async Task<ActionResult<PipeResponseDto>> UpdatePipe(string id, UpdatePipeRequest request)
{
  var pipe = await _context.Pipes.FindAsync(id);
  if (request.Diameter.HasValue) pipe.Diameter = request.Diameter.Value;
  if (request.KWall.HasValue) await UpsertLinkProperty(id, 7, request.KWall.Value);  // EN_KWALL
  await _context.SaveChangesAsync();
  return Ok(result);
}

2. EPANET Setter Functions (EpanetWrapper) ✅​

The EpanetWrapper class provides setter methods:

MethodPurposeEPANET Function
EN_setlinkvalue(index, param, val)Set link property✅ Available
EN_setnodevalue(index, param, val)Set node property✅ Available
EN_setdemandmodel(type, pmin, preq, pexp)Set demand model✅ Available
EN_addcontrol(...)Add simple control✅ Available
EN_addrule(ruleText)Add rule-based control✅ Available
EN_setpattern(index, values)Set pattern multipliers⚠️ Needs wrapper
EN_setcurve(index, xvals, yvals)Set curve data⚠️ Needs wrapper
EN_setoption(option, value)Set simulation option⚠️ Needs wrapper
EN_settimeparam(param, value)Set time parameter⚠️ Needs wrapper
SetValveStatus(id, open)Set valve status✅ Available
SetTankDiameter(id, value)Set tank diameter✅ Available
SetLinkLeakArea(index, value)Set leak area✅ Available
SetLinkLeakExpansion(index, value)Set leak expansion✅ Available

Example usage:

// From Program.cs - WriteDemandModel()
epanetWrapper.EN_setdemandmodel(demandModelType, pressureMin, pressureReq, pressureExp);

// From Program.cs - WriteControls()
epanetWrapper.EN_addcontrol(type, linkIndex, setting, nodeIndex, level, ref controlIndex);

3. Partial Database → EPANET Flow ⚠️​

Currently implemented in Program.cs before simulation:

// Line 299-300 in Program.cs
await WriteControls(epanetWrapper, enContext, scenarioId);  // ✅ Writes controls from DB
WriteDemandModel(epanetWrapper, options);                    // ✅ Writes demand model from DB

What's Written:

  • ✅ SimpleControls (from database)
  • ✅ RuleBasedControls (from database)
  • ✅ Demand model settings (from Options)

What's NOT Written:

  • ❌ Pipe properties (diameter, roughness, length, KWall)
  • ❌ Pump properties (curves, patterns, energy cost, status, setting)
  • ❌ Valve properties (diameter, type, setting, status)
  • ❌ Tank properties (levels, diameter, InitQual, BulkCoeff)
  • ❌ Junction properties (elevation, demand, pattern)
  • ❌ Reservoir properties (head, pattern)
  • ❌ Pattern multipliers
  • ❌ Curve data points
  • ❌ Options (units, headloss formula, etc.)
  • ❌ Time parameters (duration, timesteps)

What's MISSING​

Critical Gap: No Pre-Simulation Update Mechanism​

Current Flow:

1. User edits pipe in database (via API) ✅
2. Database updated successfully ✅
3. User runs scenario
4. Scenario loads network from INP file ❌ (ignores database changes!)
5. Simulation runs with OLD data from INP ❌

Desired Flow:

1. User edits pipe in database (via API) ✅
2. Database updated successfully ✅
3. User runs scenario
4. Scenario loads network from INP file
5. **Apply database overrides to EPANET model** ← MISSING!
6. Simulation runs with CURRENT data from DB ✅

Problem Statement:

  • Scenario.InputFile (line 187 in Program.cs) always points to a static .inp file
  • epanetWrapper.Startup() loads this file, creating the initial network
  • Database changes are never applied to the in-memory EPANET model
  • Result: Database edits have no effect on simulations

Design Solution​

Architecture Overview​

We need a Network Synchronization Service that applies database changes to EPANET after loading the INP file but before running the simulation.

┌─────────────────┐
│  Load INP File  │  ← Existing: epanetWrapper.Startup()
└────────┬────────┘
         │
         ▼
┌─────────────────────────────────────┐
│  NEW: NetworkSynchronizationService │  ← Proposed
│                                     │
│  • Queries database for overrides   │
│  • Applies via EN_setlinkvalue()    │
│  • Applies via EN_setnodevalue()    │
│  • Updates patterns, curves, etc.   │
└────────┬────────────────────────────┘
         │
         ▼
┌─────────────────┐
│  Write Controls │  ← Existing: WriteControls()
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Write DemandModel│ ← Existing: WriteDemandModel()
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Run Simulation  │  ← Existing: RunNextHQ()
└─────────────────┘

Implementation Strategy​

Apply database changes every time a scenario runs, just after loading the INP file.

Pros:

  • ✅ Always up-to-date with latest database state
  • ✅ No need to track "dirty" flags
  • ✅ Simple to implement
  • ✅ Works for any scenario execution path

Cons:

  • ⚠️ Slight performance overhead (query database, apply changes)
  • ⚠️ May apply changes even if nothing changed

Implementation Location:

// In Program.cs, after line 232 (after Startup):
bool startupSuccess = epanetWrapper.Startup(...);

// NEW CODE:
await SyncNetworkFromDatabase(epanetWrapper, enContext);

await WriteControls(epanetWrapper, enContext, scenarioId);
WriteDemandModel(epanetWrapper, options);

Detailed Implementation Plan​

Phase 1: Core Infrastructure (Week 1)​

1.1 Create NetworkSynchronizationService​

Location: epanetconsolecore/EpanetConsoleCore/Services/NetworkSynchronizationService.cs

public class NetworkSynchronizationService
{
  private readonly EpanetWrapper _wrapper;
  private readonly EN2PostgresContext _context;
  private readonly HTLogger.HTLogger _logger;

  public NetworkSynchronizationService(
    EpanetWrapper wrapper,
    EN2PostgresContext context,
    HTLogger.HTLogger logger)
  {
    _wrapper = wrapper;
    _context = context;
    _logger = logger;
  }

  /// <summary>
  /// Synchronizes all database changes to the EPANET in-memory model.
  /// Called after loading INP file but before running simulation.
  /// </summary>
  public async Task SyncAllFromDatabaseAsync()
  {
    _logger.LogInformation("🔄 Synchronizing network from database...");

    await SyncNodesAsync();
    await SyncLinksAsync();
    await SyncPatternsAsync();
    await SyncCurvesAsync();
    await SyncOptionsAsync();
    await SyncTimeParametersAsync();

    _logger.LogInformation("✅ Network synchronization complete");
  }

  // Individual sync methods implemented below...
}

1.2 Add Missing EpanetWrapper Methods​

Location: epanetconsolecore/EpanetWrapper/EpanetWrapper/EpanetWrapper.cs

// Pattern methods
public int EN_setpattern(int index, double[] factors, int numFactors)
{
  return EN.EN_setpattern(this.Project, index, factors, numFactors);
}

public int EN_setpatternvalue(int index, int period, double value)
{
  return EN.EN_setpatternvalue(this.Project, index, period, value);
}

// Curve methods
public int EN_setcurve(int index, double[] xValues, double[] yValues, int numPoints)
{
  return EN.EN_setcurve(this.Project, index, xValues, yValues, numPoints);
}

public int EN_setcurvevalue(int curveIndex, int pointIndex, double x, double y)
{
  return EN.EN_setcurvevalue(this.Project, curveIndex, pointIndex, x, y);
}

// Option methods
public int EN_setoption(int option, double value)
{
  return EN.EN_setoption(this.Project, option, value);
}

// Time parameter methods
public int EN_settimeparam(int param, long value)
{
  return EN.EN_settimeparam(this.Project, param, value);
}

Phase 2: Node Synchronization (Week 1-2)​

2.1 Junction Sync​

private async Task SyncJunctionsAsync()
{
  var junctions = await _context.Junctions.ToListAsync();

  foreach (var junction in junctions)
  {
    int index = _wrapper.GetNodeIndex(junction.Id);
    if (index <= 0)
    {
      _logger.LogWarning($"Junction {junction.Id} not found in EPANET model");
      continue;
    }

    // Update elevation
    if (junction.Elevation.HasValue)
    {
      _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_ELEVATION, junction.Elevation.Value);
    }

    // Update base demand
    if (junction.BaseDemand > 0)
    {
      _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_BASEDEMAND, junction.BaseDemand);
    }

    // Update pattern
    if (junction.Pattern > 0)
    {
      _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_PATTERN, junction.Pattern);
    }
  }

  _logger.LogInformation($"Synced {junctions.Count} junctions");
}

2.2 Tank Sync​

private async Task SyncTanksAsync()
{
  var tanks = await _context.Tanks.ToListAsync();

  foreach (var tank in tanks)
  {
    int index = _wrapper.GetNodeIndex(tank.Id);
    if (index <= 0) continue;

    // Core properties
    if (tank.Elevation.HasValue)
      _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_ELEVATION, tank.Elevation.Value);

    _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_TANKDIAM, tank.Diameter);
    _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_MINLEVEL, tank.MinLevel);
    _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_MAXLEVEL, tank.MaxLevel);
    _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_MINVOLUME, tank.MinVolume);

    if (tank.VolCurve > 0)
      _wrapper.EN_setnodevalue(index, (int)EN_NodeProperty.EN_VOLCURVE, tank.VolCurve);
  }

  // Sync NodeProperties for tanks (InitQual, BulkCoeff)
  await SyncNodePropertiesAsync();

  // Sync TankMixing
  await SyncTankMixingAsync();

  _logger.LogInformation($"Synced {tanks.Count} tanks");
}

2.3 NodeProperty Sync (Generic)​

private async Task SyncNodePropertiesAsync()
{
  var nodeProperties = await _context.NodeProperties.ToListAsync();

  foreach (var prop in nodeProperties)
  {
    int index = _wrapper.GetNodeIndex(prop.NodeId);
    if (index <= 0) continue;

    _wrapper.EN_setnodevalue(index, prop.PropertyId, prop.PropertyValue);
  }

  _logger.LogInformation($"Synced {nodeProperties.Count} node properties");
}

3.1 Pipe Sync​

private async Task SyncPipesAsync()
{
  var pipes = await _context.Pipes.ToListAsync();

  foreach (var pipe in pipes)
  {
    int index = _wrapper.GetLinkIndex(pipe.Id);
    if (index <= 0) continue;

    // Core pipe properties from Pipe table
    _wrapper.EN_setlinkvalue(index, (int)EN_LinkProperty.EN_DIAMETER, pipe.Diameter);
    _wrapper.EN_setlinkvalue(index, (int)EN_LinkProperty.EN_LENGTH, pipe.Length);
    _wrapper.EN_setlinkvalue(index, (int)EN_LinkProperty.EN_ROUGHNESS, pipe.Roughness);
    _wrapper.EN_setlinkvalue(index, (int)EN_LinkProperty.EN_MINORLOSS, pipe.MinorLoss);
  }

  _logger.LogInformation($"Synced {pipes.Count} pipes");
}

3.2 Pump Sync​

private async Task SyncPumpsAsync()
{
  // Pumps have NO core properties (after model redesign)
  // All properties are in LinkProperty table
  // Will be handled by SyncLinkPropertiesAsync()

  _logger.LogInformation("Pumps synced via LinkProperties");
}

3.3 Valve Sync​

private async Task SyncValvesAsync()
{
  var valves = await _context.Valves.ToListAsync();

  foreach (var valve in valves)
  {
    int index = _wrapper.GetLinkIndex(valve.Id);
    if (index <= 0) continue;

    // Core valve properties
    _wrapper.EN_setlinkvalue(index, (int)EN_LinkProperty.EN_DIAMETER, valve.Diameter);
    _wrapper.EN_setlinkvalue(index, (int)EN_LinkProperty.EN_VALVE_TYPE, valve.ValveType);
    _wrapper.EN_setlinkvalue(index, (int)EN_LinkProperty.EN_INITSETTING, valve.InitSetting);
    _wrapper.EN_setlinkvalue(index, (int)EN_LinkProperty.EN_MINORLOSS, valve.MinorLoss);
  }

  _logger.LogInformation($"Synced {valves.Count} valves");
}

3.4 LinkProperty Sync (Generic)​

private async Task SyncLinkPropertiesAsync()
{
  var linkProperties = await _context.LinkProperties.ToListAsync();

  foreach (var prop in linkProperties)
  {
    int index = _wrapper.GetLinkIndex(prop.LinkId);
    if (index <= 0) continue;

    _wrapper.EN_setlinkvalue(index, prop.PropertyId, prop.PropertyValue);
  }

  _logger.LogInformation($"Synced {linkProperties.Count} link properties");
}

Phase 4: Pattern & Curve Synchronization (Week 2-3)​

4.1 Pattern Sync​

private async Task SyncPatternsAsync()
{
  var patterns = await _context.Patterns.ToListAsync();

  foreach (var pattern in patterns)
  {
    int index = _wrapper.GetPatternIndex(pattern.Id);
    if (index <= 0)
    {
      // Pattern doesn't exist - add it
      index = await AddPatternAsync(pattern);
    }

    if (index > 0 && pattern.Multipliers != null && pattern.Multipliers.Length > 0)
    {
      // Update pattern values
      int error = _wrapper.EN_setpattern(index, pattern.Multipliers, pattern.Multipliers.Length);
      if (error != 0)
      {
        _logger.LogError($"Failed to set pattern {pattern.Id}: EPANET error {error}");
      }
    }
  }

  _logger.LogInformation($"Synced {patterns.Count} patterns");
}

private async Task<int> AddPatternAsync(Pattern pattern)
{
  // Note: EPANET 2.2 doesn't have EN_addpattern in the basic toolkit
  // May need to use EN_setpattern with a new index
  // Or regenerate INP file for new patterns
  _logger.LogWarning($"Cannot add new pattern {pattern.Id} - not in original INP");
  return -1;
}

4.2 Curve Sync​

private async Task SyncCurvesAsync()
{
  var curves = await _context.DataCurves
    .OrderBy(c => c.Id)
    .ThenBy(c => c.SequenceNumber)
    .ToListAsync();

  var groupedCurves = curves.GroupBy(c => c.Id);

  foreach (var curveGroup in groupedCurves)
  {
    string curveId = curveGroup.Key;
    int index = _wrapper.GetCurveIndex(curveId);

    if (index <= 0)
    {
      _logger.LogWarning($"Curve {curveId} not found in EPANET model");
      continue;
    }

    var points = curveGroup.OrderBy(c => c.SequenceNumber).ToList();
    double[] xValues = points.Select(p => p.XValue).ToArray();
    double[] yValues = points.Select(p => p.YValue).ToArray();

    int error = _wrapper.EN_setcurve(index, xValues, yValues, points.Count);
    if (error != 0)
    {
      _logger.LogError($"Failed to set curve {curveId}: EPANET error {error}");
    }
  }

  _logger.LogInformation($"Synced {groupedCurves.Count()} curves");
}

Phase 5: Options & Time Parameters (Week 3)​

5.1 Options Sync​

private async Task SyncOptionsAsync()
{
  var options = await _context.Options.FirstOrDefaultAsync(o => o.IsDefault);
  if (options == null)
  {
    _logger.LogWarning("No default options found, skipping options sync");
    return;
  }

  // Units (note: units often can't be changed after EN_open)
  if (!string.IsNullOrEmpty(options.Units))
  {
    var unitsEnum = MapUnitsStringToEnum(options.Units);
    // Note: Some options can only be set before EN_open(), not after
    // May need to handle these differently
  }

  // Headloss formula
  if (!string.IsNullOrEmpty(options.Headloss))
  {
    var headlossEnum = MapHeadlossStringToEnum(options.Headloss);
    _wrapper.EN_setoption((int)EN_Option.EN_HEADLOSSFORM, headlossEnum);
  }

  // Hydraulic parameters
  if (options.Trials.HasValue)
    _wrapper.EN_setoption((int)EN_Option.EN_TRIALS, options.Trials.Value);

  if (options.Accuracy.HasValue)
    _wrapper.EN_setoption((int)EN_Option.EN_ACCURACY, options.Accuracy.Value);

  // Quality tolerance
  if (options.QualityTolerance.HasValue)
    _wrapper.EN_setoption((int)EN_Option.EN_TOLERANCE, options.QualityTolerance.Value);

  _logger.LogInformation("Synced simulation options");
}

5.2 Time Parameters Sync​

private async Task SyncTimeParametersAsync()
{
  var timeParams = await _context.TimeParameters.FirstOrDefaultAsync(tp => tp.IsDefault);
  if (timeParams == null)
  {
    _logger.LogWarning("No default time parameters found, skipping time sync");
    return;
  }

  // Duration
  if (timeParams.Duration.HasValue)
    _wrapper.EN_settimeparam((int)EN_TimeParameter.EN_DURATION, timeParams.Duration.Value);

  // Hydraulic timestep
  if (timeParams.HydraulicTimestep.HasValue)
    _wrapper.EN_settimeparam((int)EN_TimeParameter.EN_HYDSTEP, timeParams.HydraulicTimestep.Value);

  // Quality timestep
  if (timeParams.QualityTimestep.HasValue)
    _wrapper.EN_settimeparam((int)EN_TimeParameter.EN_QUALSTEP, timeParams.QualityTimestep.Value);

  // Report timestep
  if (timeParams.ReportTimestep.HasValue)
    _wrapper.EN_settimeparam((int)EN_TimeParameter.EN_REPORTSTEP, timeParams.ReportTimestep.Value);

  // Report start
  if (timeParams.ReportStart.HasValue)
    _wrapper.EN_settimeparam((int)EN_TimeParameter.EN_REPORTSTART, timeParams.ReportStart.Value);

  _logger.LogInformation("Synced time parameters");
}

Phase 6: Integration with Program.cs (Week 3)​

6.1 Modify Program.cs​

Location: Line 232 in epanetconsolecore/EpanetConsoleCore/Program.cs

bool startupSuccess = epanetWrapper.Startup(doQuality, msxFile, out int epanetErrorCode, out string epanetErrorMessage);
if (!startupSuccess)
{
  // ... existing error handling ...
}

// ✨ NEW CODE: Synchronize network from database
scenario.CurrentStep = "Synchronizing network from database";
scenario.ProgressPercentage = 7;
enContext.Entry(scenario).State = EntityState.Modified;
await enContext.SaveChangesAsync();

var syncService = new NetworkSynchronizationService(epanetWrapper, enContext, logger);
await syncService.SyncAllFromDatabaseAsync();

// Continue with existing code
reportTimestep = epanetWrapper.GetReportingTimestep();
// ...

Property-by-Property Mapping​

Node Properties​

Database SourcePropertyIdEN_NodePropertySetter Method
Junction.Elevation-EN_ELEVATION (0)EN_setnodevalue()
Junction.BaseDemand-EN_BASEDEMAND (1)EN_setnodevalue()
Junction.Pattern-EN_PATTERN (2)EN_setnodevalue()
Reservoir.InitialHead-EN_ELEVATION (0)EN_setnodevalue()
Reservoir.Pattern-EN_PATTERN (2)EN_setnodevalue()
Tank.Elevation-EN_ELEVATION (0)EN_setnodevalue()
Tank.MinLevel-EN_MINLEVEL (20)EN_setnodevalue()
Tank.MaxLevel-EN_MAXLEVEL (21)EN_setnodevalue()
Tank.Diameter-EN_TANKDIAM (17)EN_setnodevalue()
Tank.MinVolume-EN_MINVOLUME (18)EN_setnodevalue()
Tank.VolCurve-EN_VOLCURVE (19)EN_setnodevalue()
NodeProperty.PropertyValuePropertyId(varies)EN_setnodevalue()
Emitter (via NodeProperty)3EN_EMITTER (3)EN_setnodevalue()
InitQual (via NodeProperty)4EN_INITQUAL (4)EN_setnodevalue()
TankBulkCoeff (via NodeProperty)23EN_TANK_KBULK (23)EN_setnodevalue()
TankMixing.MixingModel-EN_MIXMODEL (15)EN_setnodevalue()
TankMixing.Fraction-EN_MIXFRACTION (22)EN_setnodevalue()
Database SourcePropertyIdEN_LinkPropertySetter Method
Pipe.Diameter-EN_DIAMETER (0)EN_setlinkvalue()
Pipe.Length-EN_LENGTH (1)EN_setlinkvalue()
Pipe.Roughness-EN_ROUGHNESS (2)EN_setlinkvalue()
Pipe.MinorLoss-EN_MINORLOSS (3)EN_setlinkvalue()
Valve.Diameter-EN_DIAMETER (0)EN_setlinkvalue()
Valve.ValveType-EN_VALVE_TYPE (29)EN_setlinkvalue()
Valve.InitSetting-EN_INITSETTING (5)EN_setlinkvalue()
Valve.MinorLoss-EN_MINORLOSS (3)EN_setlinkvalue()
LinkProperty.PropertyValuePropertyId(varies)EN_setlinkvalue()
KWall (via LinkProperty)7EN_KWALL (7)EN_setlinkvalue()
InitStatus (via LinkProperty)4EN_INITSTATUS (4)EN_setlinkvalue()
InitSetting (via LinkProperty)5EN_INITSETTING (5)EN_setlinkvalue()
Pattern (via LinkProperty)15EN_LINKPATTERN (15)EN_setlinkvalue()
PumpHCurve (via LinkProperty)19EN_PUMP_HCURVE (19)EN_setlinkvalue()
PumpECurve (via LinkProperty)20EN_PUMP_ECURVE (20)EN_setlinkvalue()
PumpECost (via LinkProperty)21EN_PUMP_ECOST (21)EN_setlinkvalue()
PumpEPattern (via LinkProperty)22EN_PUMP_EPAT (22)EN_setlinkvalue()
LeakArea (via LinkProperty)26EN_LEAK_AREA (26)EN_setlinkvalue()
LeakExpansion (via LinkProperty)27EN_LEAK_EXPAN (27)EN_setlinkvalue()

Pattern & Curve Data​

Database SourceEPANET FunctionNotes
Pattern.MultipliersEN_setpattern()Set all multipliers at once
DataCurve.XValue, YValueEN_setcurve()Set all X,Y points at once

Options & Time Parameters​

Database SourceEN_OptionSetter Method
Options.HeadlossEN_HEADLOSSFORMEN_setoption()
Options.TrialsEN_TRIALSEN_setoption()
Options.AccuracyEN_ACCURACYEN_setoption()
Options.QualityToleranceEN_TOLERANCEEN_setoption()
TimeParameters.DurationEN_DURATIONEN_settimeparam()
TimeParameters.HydraulicTimestepEN_HYDSTEPEN_settimeparam()
TimeParameters.QualityTimestepEN_QUALSTEPEN_settimeparam()
TimeParameters.ReportTimestepEN_REPORTSTEPEN_settimeparam()

Error Handling Strategy​

Validation Before Applying​

private bool ValidateNodeProperty(string nodeId, int propertyId, double value)
{
  // Validate ranges
  switch ((EN_NodeProperty)propertyId)
  {
    case EN_NodeProperty.EN_ELEVATION:
      if (value < -1000 || value > 10000)
      {
        _logger.LogWarning($"Node {nodeId}: Elevation {value} out of range");
        return false;
      }
      break;

    case EN_NodeProperty.EN_TANKDIAM:
      if (value <= 0)
      {
        _logger.LogError($"Tank {nodeId}: Diameter must be positive");
        return false;
      }
      break;

    // ... other validations ...
  }

  return true;
}

EPANET Error Code Handling​

private void ApplyLinkProperty(string linkId, int propertyId, double value)
{
  int index = _wrapper.GetLinkIndex(linkId);
  if (index <= 0)
  {
    _logger.LogWarning($"Link {linkId} not found in EPANET model");
    return;
  }

  int error = _wrapper.EN_setlinkvalue(index, propertyId, value);
  if (error != 0)
  {
    string errorMsg = GetEpanetErrorMessage(error);
    _logger.LogError($"Failed to set {linkId} property {propertyId} to {value}: {errorMsg}");

    // Decision: Continue or abort?
    // Recommendation: Log error but continue (partial sync better than no sync)
  }
}

Transactional Behavior​

Question: Should we rollback all changes if one fails?

Recommendation: No

  • EPANET doesn't support transactions
  • Partial sync is better than no sync
  • Log all errors and continue
  • Report summary at end (X properties updated, Y failed)

Performance Considerations​

The Core Trade-Off​

Short-term Strategy (Recommended for MVP): Sync ALL assets every time

  • ✅ Simple implementation
  • ✅ Reliable (always consistent)
  • ✅ No complex change tracking needed
  • ⚠️ May sync thousands of unchanged values
  • ⚠️ Overhead scales linearly with network size

Long-term Strategy (Future Optimization): Only sync what changed

  • ✅ Much faster for large networks
  • ✅ Minimal overhead when few changes
  • ⚠️ Complex implementation (change tracking, dirty flags)
  • ⚠️ Risk of missed updates if tracking fails

Why We Should Start with "Sync Everything"​

1. Correctness Over Performance​

Problem: The system currently has NO sync mechanism. Database changes are completely ignored.

Priority: Getting a working sync mechanism is more valuable than an optimized one.

Analogy: Better to have a slow car than no car at all. We can optimize later.

2. Current Architecture Constraints​

Core Properties Not in Property Tables:

// When user edits pipe diameter via API:
pipe.Diameter = 15.0;  // Updates Pipe table
await _context.SaveChangesAsync();

// LinkProperty table is NOT updated!
// So we MUST sync from Pipe table, not just LinkProperty

Implication: We need to sync BOTH:

  • Core asset tables (Pipe, Tank, Junction, etc.)
  • Property tables (LinkProperty, NodeProperty)

3. Unknown Performance Impact​

We don't yet know if this is actually a problem:

  • How many scenarios run per day?
  • What's the typical network size?
  • What's acceptable overhead (5 seconds? 30 seconds? 2 minutes?)

Recommendation: Implement simple version, measure real-world performance, optimize if needed.

Estimated Performance Impact​

Theoretical Analysis​

Per-Asset Overhead:

  • Database read: ~0.1-0.5 ms
  • EPANET setter call: ~0.05-0.1 ms
  • Total per asset: ~0.15-0.6 ms

Network Size Estimates:

Network SizeNodesLinksTotal AssetsSync Time (Worst Case)
Small100120220~0.13 seconds
Medium1,0001,2002,200~1.3 seconds
Large5,0006,00011,000~6.6 seconds
Very Large10,00012,00022,000~13 seconds
Extreme50,00060,000110,000~66 seconds

Comparison to Simulation Time:

  • Small network hydraulic simulation: 1-5 seconds
  • Large network hydraulic simulation: 30-120 seconds
  • Sync overhead: 5-20% of simulation time

Context: If a simulation takes 60 seconds, adding 6 seconds for sync (10% overhead) is likely acceptable.

Real-World Factors​

Factors that REDUCE overhead:

  • Database query optimization (indexes, prepared statements)
  • Batch reads (SELECT all pipes in one query vs. 10,000 individual queries)
  • EPANET setter efficiency (C code is fast)

Factors that INCREASE overhead:

  • Network latency to database
  • Database load (concurrent users)
  • Complex entity relationships (joins)

Optimization Strategies (Future Enhancements)​

Strategy 1: Change Tracking with Timestamps​

Implementation:

// Add ModifiedAt to ALL tables
public class Pipe : Link
{
  // ... existing properties ...
  public DateTime? ModifiedAt { get; set; }
}

// Track last sync time per scenario
public class Scenario
{
  // ... existing properties ...
  public DateTime? LastSyncAt { get; set; }
}

// Only sync changed entities
private async Task SyncPipesAsync()
{
  var changedPipes = await _context.Pipes
    .Where(p => !scenario.LastSyncAt.HasValue || p.ModifiedAt > scenario.LastSyncAt)
    .ToListAsync();

  // Update LastSyncAt after successful sync
  scenario.LastSyncAt = DateTime.UtcNow;
}

Pros:

  • ✅ Simple to understand
  • ✅ Works with existing architecture
  • ✅ Dramatic performance improvement (only sync 1-10% of entities typically)

Cons:

  • ⚠️ Requires migration to add ModifiedAt columns
  • ⚠️ Must update ModifiedAt in ALL update code paths
  • ⚠️ Risk of missing updates if ModifiedAt not set correctly

Estimated Impact: 90-95% reduction in sync time for typical usage

Strategy 2: Dirty Flag Pattern​

Implementation:

// New table to track changes
public class DirtyEntity
{
  public int Id { get; set; }
  public string EntityType { get; set; }  // "Pipe", "Junction", etc.
  public string EntityId { get; set; }    // "P-123"
  public DateTime DirtiedAt { get; set; }
}

// Mark dirty on update (in API controllers)
[HttpPut("{id}")]
public async Task<ActionResult> UpdatePipe(string id, UpdatePipeRequest request)
{
  var pipe = await _context.Pipes.FindAsync(id);
  pipe.Diameter = request.Diameter.Value;

  // Mark as dirty
  _context.DirtyEntities.Add(new DirtyEntity
  {
    EntityType = "Pipe",
    EntityId = id,
    DirtiedAt = DateTime.UtcNow
  });

  await _context.SaveChangesAsync();
}

// Sync only dirty entities
private async Task SyncPipesAsync()
{
  var dirtyPipeIds = await _context.DirtyEntities
    .Where(de => de.EntityType == "Pipe")
    .Select(de => de.EntityId)
    .ToListAsync();

  var pipes = await _context.Pipes
    .Where(p => dirtyPipeIds.Contains(p.Id))
    .ToListAsync();

  // Clear dirty flags after sync
  await _context.DirtyEntities
    .Where(de => de.EntityType == "Pipe")
    .ExecuteDeleteAsync();
}

Pros:

  • ✅ Very precise (only syncs actually changed entities)
  • ✅ Can track who made change, when, etc.
  • ✅ Easy to audit what changed

Cons:

  • ⚠️ More complex (new table, more code)
  • ⚠️ Must update dirty flags in EVERY update path
  • ⚠️ Orphaned dirty flags if updates fail

Estimated Impact: 95-99% reduction in sync time

Strategy 3: Database-Driven Diff​

Implementation:

// Store snapshot of last synced state
public class ScenarioSnapshot
{
  public int ScenarioId { get; set; }
  public string EntityType { get; set; }
  public string EntityId { get; set; }
  public string PropertyName { get; set; }
  public string PropertyValue { get; set; }  // JSON serialized
  public DateTime SnapshotAt { get; set; }
}

// Compute diff
private async Task<List<Pipe>> GetChangedPipesAsync()
{
  var currentPipes = await _context.Pipes.ToListAsync();
  var snapshot = await _context.ScenarioSnapshots
    .Where(s => s.ScenarioId == scenarioId && s.EntityType == "Pipe")
    .ToListAsync();

  // Compare and find differences
  var changedPipes = currentPipes.Where(p =>
  {
    var snapshotValues = snapshot.FirstOrDefault(s => s.EntityId == p.Id);
    return snapshotValues == null || HasChanged(p, snapshotValues);
  }).ToList();

  return changedPipes;
}

Pros:

  • ✅ Works even if ModifiedAt/dirty flags fail
  • ✅ Can detect changes from external sources (direct DB edits)
  • ✅ Provides audit trail

Cons:

  • ⚠️ Very complex
  • ⚠️ High storage overhead (duplicate of all data)
  • ⚠️ Slower (must compute diff every time)

Not Recommended - too complex for marginal benefit

Strategy 4: Query Optimization (No Architecture Change)​

Implementation:

// Instead of loading all entities and filtering in memory
var allPipes = await _context.Pipes.ToListAsync();  // BAD
foreach (var pipe in allPipes)
{
  // Process...
}

// Use efficient query projections
var pipeData = await _context.Pipes
  .Select(p => new { p.Id, p.Diameter, p.Length, p.Roughness })  // GOOD
  .ToListAsync();

// Or use AsNoTracking for read-only
var pipes = await _context.Pipes
  .AsNoTracking()  // Don't track changes
  .ToListAsync();

Benefits:

  • ✅ Easy to implement (just add .AsNoTracking())
  • ✅ 20-30% performance improvement
  • ✅ No architecture changes

Recommendation: Do this FIRST, before more complex optimizations

Phase 1: Baseline Implementation (MVP - Now)​

// Sync everything, no optimizations
public async Task SyncAllFromDatabaseAsync()
{
  await SyncNodesAsync();  // All junctions, tanks, reservoirs
  await SyncLinksAsync();  // All pipes, pumps, valves
  // ...
}

When to use: Initial implementation, networks < 5,000 assets

Advantages:

  • Simple, reliable, maintainable
  • Fast enough for most networks (< 10 second overhead)
  • Provides baseline for performance metrics

Phase 2: Basic Optimization (3-6 months later)​

// Add AsNoTracking, use efficient queries
var pipes = await _context.Pipes
  .AsNoTracking()
  .Select(p => new { p.Id, p.Diameter, p.Length, p.Roughness, p.MinorLoss })
  .ToListAsync();

Trigger: When overhead > 20% of simulation time OR user complaints

Effort: 1-2 days

Benefit: 20-30% reduction

Phase 3: Change Tracking (6-12 months later)​

// Add ModifiedAt timestamps
var changedPipes = await _context.Pipes
  .AsNoTracking()
  .Where(p => !scenario.LastSyncAt.HasValue || p.ModifiedAt > scenario.LastSyncAt)
  .ToListAsync();

Trigger: Networks > 10,000 assets OR overhead > 30 seconds

Effort: 1-2 weeks (migration, update all update paths)

Benefit: 90-95% reduction in typical scenarios

Phase 4: Advanced Optimization (12+ months later)​

Only if needed for extreme networks (50,000+ assets):

  • Dirty flag pattern
  • Parallel processing
  • Cached snapshots

Performance Monitoring​

Metrics to Track:

public class SyncMetrics
{
  public int NodesProcessed { get; set; }
  public int LinksProcessed { get; set; }
  public int PatternsProcessed { get; set; }
  public int CurvesProcessed { get; set; }
  public TimeSpan TotalSyncTime { get; set; }
  public TimeSpan DatabaseQueryTime { get; set; }
  public TimeSpan EpanetUpdateTime { get; set; }
  public int ErrorCount { get; set; }
}

// Log metrics after each sync
_logger.LogInformation($"Sync completed: {metrics.NodesProcessed + metrics.LinksProcessed} assets in {metrics.TotalSyncTime.TotalSeconds:F2}s");

Alert Thresholds:

  • ⚠️ Sync time > 30 seconds
  • 🔴 Sync time > 60 seconds
  • 🔴 Sync time > 20% of simulation time

Dashboard Metrics:

  • Average sync time per scenario
  • 95th percentile sync time
  • Sync time vs. network size correlation
  • Error rate

Decision Matrix: When to Optimize​

Network SizeAsset CountCurrent OverheadAction
Small< 500< 1 second✅ No optimization needed
Medium500-5,0001-10 seconds✅ Monitor, optimize if complaints
Large5,000-20,00010-30 seconds⚠️ Consider Phase 2 optimization
Very Large20,000-50,00030-60 seconds🔴 Implement Phase 3 (change tracking)
Extreme> 50,000> 60 seconds🔴 Full optimization (Phase 3+4)

Alternative: Scenario-Specific INP Generation​

Instead of syncing in-memory, generate custom INP file per scenario:

// Generate INP from database
var inpGenerator = new InpFileGenerator(_context);
string customInp = await inpGenerator.GenerateForScenarioAsync(scenarioId);

// Load custom INP
epanetWrapper = new EpanetWrapper(customInp, rpt, out);

Pros:

  • ✅ No sync overhead (standard EPANET workflow)
  • ✅ INP file represents exact database state
  • ✅ Can version control generated files

Cons:

  • ⚠️ Requires complete INP writer (complex)
  • ⚠️ File I/O overhead
  • ⚠️ Temporary file management

When to consider: If sync overhead becomes unacceptable even with optimization

Conclusion​

Recommended Strategy:

  1. Start simple - Sync everything (MVP implementation)
  2. Measure performance - Track metrics for 3-6 months
  3. Optimize if needed - Based on real-world data, not speculation
  4. Use phased approach - Easy optimizations first (AsNoTracking), complex ones only if necessary

Key Insight: Most networks are small-to-medium (<5,000 assets). For these, sync overhead is negligible (< 5 seconds). Only optimize when data shows it's actually a problem.

Performance is not the bottleneck today - lack of functionality is. Get the feature working, then optimize based on real usage patterns.

Testing Strategy​

Unit Tests​

[Test]
public async Task SyncPipes_UpdatesDiameter_Success()
{
  // Arrange
  var pipe = new Pipe { Id = "P1", Diameter = 12.5 };
  _context.Pipes.Add(pipe);
  await _context.SaveChangesAsync();

  // Act
  var service = new NetworkSynchronizationService(_wrapper, _context, _logger);
  await service.SyncPipesAsync();

  // Assert
  double diameter = _wrapper.GetLinkProperty(EN_LinkProperty.EN_DIAMETER, _wrapper.GetLinkIndex("P1"));
  Assert.AreEqual(12.5, diameter, 0.01);
}

Integration Tests​

[Test]
public async Task FullSync_AllProperties_Success()
{
  // Arrange: Load test INP file
  _wrapper.Startup(...);

  // Modify database
  var pipe = await _context.Pipes.FindAsync("P1");
  pipe.Diameter = 15.0;
  await _context.SaveChangesAsync();

  // Act: Full sync
  var service = new NetworkSynchronizationService(_wrapper, _context, _logger);
  await service.SyncAllFromDatabaseAsync();

  // Assert: Verify change applied
  double diameter = _wrapper.GetLinkProperty(EN_LinkProperty.EN_DIAMETER, _wrapper.GetLinkIndex("P1"));
  Assert.AreEqual(15.0, diameter, 0.01);
}

Validation Tests​

Test that simulations produce different results after database changes:

[Test]
public async Task Simulation_UsesUpdatedDiameter_DifferentResults()
{
  // Run 1: Original diameter
  var results1 = await RunSimulation();

  // Modify diameter in database
  var pipe = await _context.Pipes.FindAsync("P1");
  pipe.Diameter = 8.0;  // Smaller diameter
  await _context.SaveChangesAsync();

  // Run 2: With sync
  await SyncNetworkFromDatabase();
  var results2 = await RunSimulation();

  // Assert: Velocities should be different (smaller pipe = higher velocity)
  Assert.AreNotEqual(results1.PipeVelocity, results2.PipeVelocity);
  Assert.Greater(results2.PipeVelocity, results1.PipeVelocity);
}

Known Limitations​

1. Immutable Properties​

Some EPANET properties cannot be changed after EN_open():

  • ❌ Flow units (must regenerate INP)
  • ❌ Node/Link topology (adding/removing elements)
  • ❌ Node/Link IDs (renaming)

Workaround: These require full INP regeneration.

2. Adding New Elements​

EPANET 2.2 has limited support for adding nodes/links after opening:

  • ⚠️ EN_addnode() - Available in EPANET 2.2
  • ⚠️ EN_addlink() - Available in EPANET 2.2
  • ⚠️ EN_addpattern() - NOT available in EPANET 2.2
  • ⚠️ EN_addcurve() - NOT available in EPANET 2.2

Workaround: Only sync existing elements; new elements require INP regeneration.

3. Control/Rule Ordering​

Controls and rules are ORDER-DEPENDENT. If database changes their order, results may differ.

Mitigation: Store Priority or Sequence field to maintain order.

Future Enhancements​

1. INP File Generation Service​

Purpose: Generate complete .inp file from database

Use Cases:

  • Adding new nodes/links
  • Changing flow units
  • Export/backup scenarios

Implementation: Separate service (not part of sync service)

2. Change Tracking​

Purpose: Only sync changed properties

Implementation:

  • Add ModifiedAt timestamp to all tables
  • Track LastSyncAt per scenario
  • Only sync entities where ModifiedAt > LastSyncAt

Benefits:

  • Faster sync for large networks
  • Clear audit trail of changes

3. Dry-Run Mode​

Purpose: Preview what would be changed without applying

Implementation:

public async Task<SyncReport> PreviewSyncAsync()
{
  // Return list of changes without applying
  // Useful for validation before running expensive simulations
}

4. Selective Sync​

Purpose: Only sync specific entity types

Implementation:

public async Task SyncAsync(SyncOptions options)
{
  if (options.SyncNodes) await SyncNodesAsync();
  if (options.SyncLinks) await SyncLinksAsync();
  if (options.SyncPatterns) await SyncPatternsAsync();
  // ...
}

Migration Plan​

Phase 1: Prototype (Week 1)​

  • ✅ Create NetworkSynchronizationService skeleton
  • ✅ Implement pipe sync (simplest case)
  • ✅ Add wrapper methods for EN_setpattern, EN_setcurve
  • ✅ Test with small network

Phase 2: Core Entities (Week 2)​

  • ✅ Implement junction, tank, reservoir sync
  • ✅ Implement valve, pump sync
  • ✅ Implement LinkProperty/NodeProperty sync
  • ✅ Integration testing

Phase 3: Advanced Features (Week 3)​

  • ✅ Implement pattern sync
  • ✅ Implement curve sync
  • ✅ Implement options sync
  • ✅ Implement time parameters sync
  • ✅ Error handling and logging

Phase 4: Integration & Testing (Week 4)​

  • ✅ Integrate with Program.cs
  • ✅ End-to-end testing
  • ✅ Performance testing
  • ✅ Documentation

Phase 5: Production Deployment (Week 5)​

  • ✅ Code review
  • ✅ Deploy to staging
  • ✅ User acceptance testing
  • ✅ Deploy to production

Success Criteria​

Functional Requirements​

  • ✅ User edits pipe diameter in database via API
  • ✅ User runs scenario
  • ✅ Simulation reflects new diameter (different velocities/headloss)
  • ✅ Results saved to database show updated behavior

Non-Functional Requirements​

  • ✅ Sync completes in < 5% of simulation time
  • ✅ All EPANET errors logged and handled gracefully
  • ✅ Partial sync failures don't crash simulation
  • ✅ Clear logging of what was synced

Validation​

  • ✅ Unit tests: 90%+ code coverage
  • ✅ Integration tests: All entity types
  • ✅ E2E tests: Full scenario with database changes
  • ✅ Performance tests: Large networks (10K+ elements)

Conclusion​

This design provides a comprehensive strategy for synchronizing database changes to EPANET's in-memory model before simulations. The approach:

  1. Builds on existing infrastructure (API controllers, EpanetWrapper, Program.cs)
  2. Follows the model redesign (LinkProperty/NodeProperty tables)
  3. Handles all entity types (nodes, links, patterns, curves, options, time parameters)
  4. Provides clear migration path (incremental implementation)
  5. Includes proper error handling (validation, logging, graceful degradation)

Next Steps:

  1. Review and approve this design
  2. Begin Phase 1 implementation (prototype)
  3. Iterate based on testing feedback

Estimated Total Effort: 4-5 weeks for full implementation and testing

Last updated on by Jake, Cursor