Database → EPANET Update: Implementation Plan

Date: October 16, 2025 Status: Implementation Guide Related: database_to_epanet_update_strategy.md (full design document)

---

Quick Summary

Problem: Database changes via API don't affect simulations (scenarios always load from static INP files).

Solution: Create NetworkSynchronizationService to apply database overrides after loading INP but before running simulation.

Location in Code: Insert sync call after line 232 in epanetconsolecore/EpanetConsoleCore/Program.cs

---

Implementation Checklist

✅ Phase 1: Infrastructure (Week 1)

1.1 Create NetworkSynchronizationService

• Create file: epanetconsolecore/EpanetConsoleCore/Services/NetworkSynchronizationService.cs
• Add constructor taking EpanetWrapper, EN2PostgresContext, HTLogger
• Add method: Task SyncAllFromDatabaseAsync()

1.2 Add Missing EpanetWrapper Methods

Edit: epanetconsolecore/EpanetWrapper/EpanetWrapper/EpanetWrapper.cs

// Add these methods:
public int EN_setpattern(int index, double[] factors, int numFactors)
public int EN_setpatternvalue(int index, int period, double value)
public int EN_setcurve(int index, double[] xValues, double[] yValues, int numPoints)
public int EN_setoption(int option, double value)
public int EN_settimeparam(int param, long value)

✅ Phase 2: Node Sync (Week 1-2)

2.1 Junction Sync

private async Task SyncJunctionsAsync()
{
  var junctions = await _context.Junctions.ToListAsync();
  foreach (var j in junctions)
  {
    int idx = _wrapper.GetNodeIndex(j.Id);
    if (idx > 0)
    {
      if (j.Elevation.HasValue) _wrapper.EN_setnodevalue(idx, 0, j.Elevation.Value); // EN_ELEVATION
      _wrapper.EN_setnodevalue(idx, 1, j.BaseDemand);  // EN_BASEDEMAND
      if (j.Pattern > 0) _wrapper.EN_setnodevalue(idx, 2, j.Pattern);  // EN_PATTERN
    }
  }
}

2.2 Tank Sync

private async Task SyncTanksAsync()
{
  var tanks = await _context.Tanks.ToListAsync();
  foreach (var t in tanks)
  {
    int idx = _wrapper.GetNodeIndex(t.Id);
    if (idx > 0)
    {
      if (t.Elevation.HasValue) _wrapper.EN_setnodevalue(idx, 0, t.Elevation.Value);
      _wrapper.EN_setnodevalue(idx, 17, t.Diameter);  // EN_TANKDIAM
      _wrapper.EN_setnodevalue(idx, 20, t.MinLevel);  // EN_MINLEVEL
      _wrapper.EN_setnodevalue(idx, 21, t.MaxLevel);  // EN_MAXLEVEL
      _wrapper.EN_setnodevalue(idx, 18, t.MinVolume); // EN_MINVOLUME
      if (t.VolCurve > 0) _wrapper.EN_setnodevalue(idx, 19, t.VolCurve);  // EN_VOLCURVE
    }
  }
}

2.3 Reservoir Sync

private async Task SyncReservoirsAsync()
{
  var reservoirs = await _context.Reservoirs.ToListAsync();
  foreach (var r in reservoirs)
  {
    int idx = _wrapper.GetNodeIndex(r.Id);
    if (idx > 0)
    {
      _wrapper.EN_setnodevalue(idx, 0, r.InitialHead);  // EN_ELEVATION (for reservoir = head)
      if (r.Pattern > 0) _wrapper.EN_setnodevalue(idx, 2, r.Pattern);  // EN_PATTERN
    }
  }
}

2.4 NodeProperty Sync (Generic)

private async Task SyncNodePropertiesAsync()
{
  var props = await _context.NodeProperties.ToListAsync();
  foreach (var p in props)
  {
    int idx = _wrapper.GetNodeIndex(p.NodeId);
    if (idx > 0)
    {
      _wrapper.EN_setnodevalue(idx, p.PropertyId, p.PropertyValue);
    }
  }
}

2.5 TankMixing Sync

private async Task SyncTankMixingAsync()
{
  var mixings = await _context.TankMixing.ToListAsync();
  foreach (var m in mixings)
  {
    int idx = _wrapper.GetNodeIndex(m.TankId);
    if (idx > 0)
    {
      _wrapper.EN_setnodevalue(idx, 15, (int)m.MixingModel);  // EN_MIXMODEL
      if (m.Fraction.HasValue)
        _wrapper.EN_setnodevalue(idx, 22, m.Fraction.Value);  // EN_MIXFRACTION
    }
  }
}

✅ Phase 3: Link Sync (Week 2)

3.1 Pipe Sync

private async Task SyncPipesAsync()
{
  var pipes = await _context.Pipes.ToListAsync();
  foreach (var p in pipes)
  {
    int idx = _wrapper.GetLinkIndex(p.Id);
    if (idx > 0)
    {
      _wrapper.EN_setlinkvalue(idx, 0, p.Diameter);     // EN_DIAMETER
      _wrapper.EN_setlinkvalue(idx, 1, p.Length);       // EN_LENGTH
      _wrapper.EN_setlinkvalue(idx, 2, p.Roughness);    // EN_ROUGHNESS
      _wrapper.EN_setlinkvalue(idx, 3, p.MinorLoss);    // EN_MINORLOSS
    }
  }
}

3.2 Valve Sync

private async Task SyncValvesAsync()
{
  var valves = await _context.Valves.ToListAsync();
  foreach (var v in valves)
  {
    int idx = _wrapper.GetLinkIndex(v.Id);
    if (idx > 0)
    {
      _wrapper.EN_setlinkvalue(idx, 0, v.Diameter);      // EN_DIAMETER
      _wrapper.EN_setlinkvalue(idx, 29, v.ValveType);    // EN_VALVE_TYPE
      _wrapper.EN_setlinkvalue(idx, 5, v.InitSetting);   // EN_INITSETTING
      _wrapper.EN_setlinkvalue(idx, 3, v.MinorLoss);     // EN_MINORLOSS
    }
  }
}

3.3 LinkProperty Sync (Generic - handles ALL pump properties)

private async Task SyncLinkPropertiesAsync()
{
  var props = await _context.LinkProperties.ToListAsync();
  foreach (var p in props)
  {
    int idx = _wrapper.GetLinkIndex(p.LinkId);
    if (idx > 0)
    {
      _wrapper.EN_setlinkvalue(idx, p.PropertyId, p.PropertyValue);
    }
  }
}

✅ Phase 4: Pattern & Curve Sync (Week 2-3)

4.1 Pattern Sync

private async Task SyncPatternsAsync()
{
  var patterns = await _context.Patterns.ToListAsync();
  foreach (var pat in patterns)
  {
    int idx = _wrapper.GetPatternIndex(pat.Id);
    if (idx > 0 && pat.Multipliers != null)
    {
      int error = _wrapper.EN_setpattern(idx, pat.Multipliers, pat.Multipliers.Length);
      if (error != 0)
        _logger.LogError($"Failed to set pattern {pat.Id}: error {error}");
    }
  }
}

4.2 Curve Sync

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

  var grouped = curves.GroupBy(c => c.Id);
  foreach (var group in grouped)
  {
    int idx = _wrapper.GetCurveIndex(group.Key);
    if (idx > 0)
    {
      var points = group.OrderBy(c => c.SequenceNumber).ToList();
      double[] xVals = points.Select(p => p.XValue).ToArray();
      double[] yVals = points.Select(p => p.YValue).ToArray();

      int error = _wrapper.EN_setcurve(idx, xVals, yVals, points.Count);
      if (error != 0)
        _logger.LogError($"Failed to set curve {group.Key}: error {error}");
    }
  }
}

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

5.1 Options Sync (Simplified)

private async Task SyncOptionsAsync()
{
  var opts = await _context.Options.FirstOrDefaultAsync(o => o.IsDefault);
  if (opts == null) return;

  // Only sync options that can be changed after EN_open()
  if (opts.Trials.HasValue)
    _wrapper.EN_setoption(0, opts.Trials.Value);  // EN_TRIALS

  if (opts.Accuracy.HasValue)
    _wrapper.EN_setoption(1, opts.Accuracy.Value);  // EN_ACCURACY

  if (opts.QualityTolerance.HasValue)
    _wrapper.EN_setoption(3, opts.QualityTolerance.Value);  // EN_TOLERANCE

  // Note: Units, Headloss cannot be changed after EN_open()
}

5.2 Time Parameters Sync

private async Task SyncTimeParametersAsync()
{
  var tp = await _context.TimeParameters.FirstOrDefaultAsync(t => t.IsDefault);
  if (tp == null) return;

  if (tp.Duration.HasValue)
    _wrapper.EN_settimeparam(0, tp.Duration.Value);  // EN_DURATION

  if (tp.HydraulicTimestep.HasValue)
    _wrapper.EN_settimeparam(1, tp.HydraulicTimestep.Value);  // EN_HYDSTEP

  if (tp.QualityTimestep.HasValue)
    _wrapper.EN_settimeparam(2, tp.QualityTimestep.Value);  // EN_QUALSTEP

  if (tp.ReportTimestep.HasValue)
    _wrapper.EN_settimeparam(4, tp.ReportTimestep.Value);  // EN_REPORTSTEP

  if (tp.ReportStart.HasValue)
    _wrapper.EN_settimeparam(3, tp.ReportStart.Value);  // EN_REPORTSTART
}

✅ Phase 6: Integration (Week 3)

6.1 Call from Main Sync Method

public async Task SyncAllFromDatabaseAsync()
{
  _logger.LogInformation("🔄 Synchronizing network from database...");

  // Order matters! Nodes before links, patterns/curves after nodes/links
  await SyncNodesAsync();
  await SyncLinksAsync();
  await SyncPatternsAsync();
  await SyncCurvesAsync();
  await SyncOptionsAsync();
  await SyncTimeParametersAsync();

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

private async Task SyncNodesAsync()
{
  await SyncJunctionsAsync();
  await SyncTanksAsync();
  await SyncReservoirsAsync();
  await SyncNodePropertiesAsync();
  await SyncTankMixingAsync();
}

private async Task SyncLinksAsync()
{
  await SyncPipesAsync();
  await SyncValvesAsync();
  // Pumps have no core properties - all via LinkProperty
  await SyncLinkPropertiesAsync();  // Handles pumps + other link properties
}

6.2 Modify Program.cs

File: epanetconsolecore/EpanetConsoleCore/Program.cs

Insert after line 232 (after epanetWrapper.Startup()):

bool startupSuccess = epanetWrapper.Startup(doQuality, msxFile, out int epanetErrorCode, out string epanetErrorMessage);
if (!startupSuccess)
{
  // ... existing error handling ...
  throw new Exception($"EPANET Startup failed with error code {epanetErrorCode}: {epanetErrorMessage}");
}

// ✨ NEW CODE: Synchronize network from database
logger.LogInformation("Synchronizing 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);
try
{
  await syncService.SyncAllFromDatabaseAsync();
}
catch (Exception syncEx)
{
  logger.LogError($"Network synchronization failed: {syncEx.Message}");
  // Decision: Continue with INP values or abort?
  // Recommendation: Continue (log warning, partial sync better than crash)
}

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

---

Testing Plan

Unit Tests (Week 4)

Create: epanetconsolecore/EpanetConsoleCore.Tests/NetworkSynchronizationServiceTests.cs

[TestFixture]
public class NetworkSynchronizationServiceTests
{
  [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
    int idx = _wrapper.GetLinkIndex("P1");
    double diameter = _wrapper.GetLinkProperty(EN_LinkProperty.EN_DIAMETER, idx);
    Assert.AreEqual(12.5, diameter, 0.01);
  }

  [Test]
  public async Task SyncPatterns_UpdatesMultipliers_Success()
  {
    // Arrange
    var pattern = new Pattern
    {
      Id = "PAT1",
      Multipliers = new double[] { 1.0, 1.2, 0.8, 0.6 }
    };
    _context.Patterns.Add(pattern);
    await _context.SaveChangesAsync();

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

    // Assert
    int idx = _wrapper.GetPatternIndex("PAT1");
    double val = _wrapper.GetPatternValue(idx, 2); // Index 2 = 0.8
    Assert.AreEqual(0.8, val, 0.01);
  }
}

Integration Test (Week 4)

Create: epanetconsolecore/EpanetConsoleCore.Tests/EndToEndSyncTests.cs

[Test]
public async Task FullScenario_WithDatabaseChanges_ProducesDifferentResults()
{
  // 1. Run scenario with original INP
  var results1 = await RunScenario(scenarioId);
  var originalVelocity = results1.PipeHydrs.First(ph => ph.PipeId == "P1").Velocity;

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

  // 3. Run scenario again (should pick up database change)
  var results2 = await RunScenario(scenarioId);
  var newVelocity = results2.PipeHydrs.First(ph => ph.PipeId == "P1").Velocity;

  // 4. Verify results changed (smaller pipe = higher velocity)
  Assert.Greater(newVelocity, originalVelocity);
}

---

Quick Reference: Property IDs

Node Properties (EN_NodeProperty)

0  = EN_ELEVATION
1  = EN_BASEDEMAND
2  = EN_PATTERN
3  = EN_EMITTER
4  = EN_INITQUAL
15 = EN_MIXMODEL
17 = EN_TANKDIAM
18 = EN_MINVOLUME
19 = EN_VOLCURVE
20 = EN_MINLEVEL
21 = EN_MAXLEVEL
22 = EN_MIXFRACTION
23 = EN_TANK_KBULK

Link Properties (EN_LinkProperty)

0  = EN_DIAMETER
1  = EN_LENGTH
2  = EN_ROUGHNESS
3  = EN_MINORLOSS
4  = EN_INITSTATUS
5  = EN_INITSETTING
7  = EN_KWALL
15 = EN_LINKPATTERN
19 = EN_PUMP_HCURVE
20 = EN_PUMP_ECURVE
21 = EN_PUMP_ECOST
22 = EN_PUMP_EPAT
26 = EN_LEAK_AREA
27 = EN_LEAK_EXPAN
29 = EN_VALVE_TYPE

Options (EN_Option)

0 = EN_TRIALS
1 = EN_ACCURACY
3 = EN_TOLERANCE

Time Parameters (EN_TimeParameter)

0 = EN_DURATION
1 = EN_HYDSTEP
2 = EN_QUALSTEP
3 = EN_REPORTSTART
4 = EN_REPORTSTEP

---

Rollout Plan

Week 1: Infrastructure

• Create NetworkSynchronizationService
• Add wrapper methods
• Test with simple pipe sync

Week 2: Core Entities

• Implement all node sync methods
• Implement all link sync methods
• Test with real network

Week 3: Advanced Features

• Implement pattern/curve sync
• Implement options/time sync
• Integration testing

Week 4: Testing & Deployment

• Unit tests (90% coverage)
• Integration tests
• End-to-end tests
• Code review
• Deploy to staging
• UAT
• Deploy to production

---

Performance Considerations

Short-term Strategy (MVP - Recommended)

Sync ALL assets every time:

• ✅ Simple, reliable implementation
• ✅ Works with current architecture (core properties in asset tables, not LinkProperty)
• ⚠️ Overhead: ~0.15-0.6ms per asset

Expected overhead:

• 1,000 assets: ~1 second
• 5,000 assets: ~5 seconds
• 10,000 assets: ~10 seconds
• Context: Typically 5-20% of simulation time (acceptable)

Long-term Optimizations (Future)

When networks grow or overhead becomes noticeable:

Phase 2 (Easy - 1-2 days):

• Add .AsNoTracking() to queries
• Use projection (Select(p => new { ... }))
• Benefit: 20-30% reduction

Phase 3 (Moderate - 1-2 weeks):

• Add ModifiedAt timestamps to all tables
• Track LastSyncAt per scenario
• Only sync changed entities
• Benefit: 90-95% reduction

Phase 4 (Advanced - if needed):

• Dirty flag pattern
• Parallel processing
• Only for extreme networks (50,000+ assets)

Decision Matrix

Network Size	Assets	Overhead	Action
Small	< 500	< 1s	✅ No optimization needed
Medium	500-5,000	1-10s	✅ Monitor
Large	5,000-20,000	10-30s	⚠️ Phase 2 optimization
Very Large	20,000+	30s+	🔴 Phase 3 (change tracking)

Key insight: Start simple. Most networks are small-to-medium. Only optimize if real-world data shows it's a problem.

Full analysis: See database_to_epanet_update_strategy.md - Performance Considerations section

---

Success Metrics

After implementation, verify:

1. ✅ User edits pipe diameter via API → database updated
2. ✅ User runs scenario → synchronization log appears
3. ✅ Simulation results reflect new diameter (velocities change)
4. ✅ No errors or warnings in logs
5. ✅ Performance overhead tracked and logged
6. ✅ Baseline metrics established for future optimization decisions

---

Common Pitfalls to Avoid

❌ DON'T: Sync in wrong order

// BAD: Curves before nodes
await SyncCurvesAsync();  // Curve might not exist yet
await SyncTanksAsync();   // References curve

// GOOD: Nodes before curves
await SyncTanksAsync();   // References curve (ok if not set yet)
await SyncCurvesAsync();  // Now set the actual curve data

❌ DON'T: Ignore EPANET errors

// BAD: Silent failure
_wrapper.EN_setlinkvalue(index, prop, value);  // Returns error code!

// GOOD: Log errors
int error = _wrapper.EN_setlinkvalue(index, prop, value);
if (error != 0)
  _logger.LogError($"Failed to set link {linkId}: EPANET error {error}");

❌ DON'T: Apply invalid values

// BAD: No validation
_wrapper.EN_setnodevalue(index, EN_TANKDIAM, -5.0);  // Negative diameter!

// GOOD: Validate first
if (tank.Diameter > 0)
  _wrapper.EN_setnodevalue(index, EN_TANKDIAM, tank.Diameter);
else
  _logger.LogWarning($"Invalid tank diameter: {tank.Diameter}");

---

Next Steps

1. Review this plan with team
2. Start Week 1 tasks (create service, add wrapper methods)
3. Test incrementally (don't wait until everything is done)
4. Document as you go (code comments, logging)
5. Iterate based on feedback

Estimated Total Effort: 4 weeks for full implementation and testing

---

Questions?

See full design document: database_to_epanet_update_strategy.md

Key Files to Modify:

• epanetconsolecore/EpanetConsoleCore/Services/NetworkSynchronizationService.cs (NEW)
• epanetconsolecore/EpanetWrapper/EpanetWrapper/EpanetWrapper.cs (add methods)
• epanetconsolecore/EpanetConsoleCore/Program.cs (insert sync call after line 232)