Legacy Pipeline Atlas worker-1snapshot 2026-07-15

MDClarity · task primitive

LoadEntitiesFromQuery

Overview

LoadEntitiesFromQueryTask loads or updates entities from custom SQL query results into their corresponding entity tables. The task intelligently merges query results with existing entities, generates keys for new items, and tracks changes for auditable entities.

Use this task when: You need to load or update arbitrary entity types (InsuranceMap, Facility, Provider, etc.) from custom SQL queries, external data sources, or complex transformations that aren't handled by standard file import tasks.

Don't use this task when: Loading data from delimited files with known structure (use LoadDataFileTask), importing from standard SQL tables (use ImportSqlTask), or working with specialized entity loaders like LoadDimensionMembersTask or LoadVisitsTask that have domain-specific logic.

Parameters

Parameter Type Required Default Description
EntityType string Yes None The name of the entity type to load (e.g., InsuranceMap, Facility, Provider). Must match a valid entity type in the schema.
Query string Yes /* return columns that make up the chosen Entity */ SQL query that returns columns matching the entity's properties. Can include an optional Key column to specify existing entities to update.
KeyPrefix string No "" (empty) Prefix for auto-generated keys for new entities. If empty/null, defaults to the EntityType name (e.g., InsuranceMap-1, InsuranceMap-2).

Execution Flow

flowchart TD
    A[Start LoadEntitiesFromQuery] --> B[Validate EntityType exists in schema]
    B --> C[Execute SQL Query]
    C --> D[Read column names from result set]
    D --> E{Key column present?}
    E -->|No & allowNewEntities=false| F[Throw Exception]
    E -->|Yes or allowNewEntities=true| G[Read all entities from query results<br/>1000 rows logged per batch]
    G --> H{Any entities have Key values?}
    H -->|Yes| I[Fetch existing entities by Key]
    H -->|No| J[Skip merge step]
    I --> K[Merge: Copy non-queried columns<br/>from existing to new entities]
    K --> L{Entity is IChangeTrackable?}
    L -->|Yes| M[Log field changes to ActivityLog]
    L -->|No| N[Skip activity logging]
    J --> N
    M --> N
    N --> O{Any entities without Keys?}
    O -->|Yes| P[Generate new keys using KeyPrefix<br/>or EntityType name]
    O -->|No| Q[Skip key generation]
    P --> Q
    Q --> R[Save all entities to table via EntityRepo]
    R --> S[End]

    style B fill:#fff4e1
    style K fill:#e1f5ff
    style M fill:#e1f5ff
    style P fill:#e1f5ff
    style R fill:#e1f5ff

Processing Algorithm

The task follows a sophisticated merge algorithm that handles both new and existing entities:

Merge Logic

  1. Query Execution: Execute the provided SQL query and extract column names
  2. Entity Reading: Read all rows into entity objects (logging progress every 1000 rows)
  3. Existing Entity Retrieval: For entities with Key values in the query, fetch existing records from the database
  4. Intelligent Merge: For each entity with a matching existing record:
    • Copy values from the query results for columns present in the query
    • Preserve existing values for columns NOT in the query results
    • This allows partial updates without overwriting all fields
  5. Change Tracking: If the entity implements IChangeTrackable (like Facility), log field changes to the ActivityLog table
  6. Key Generation: For entities without keys, generate sequential keys using the specified prefix or entity type name
  7. Bulk Save: Save all entities in a single repository operation

Example Execution

Scenario: Update InsuranceMap entities where 2 already exist

Query:

SELECT
    [Key] = 'IM-1',  -- Existing entity
    [DataSource] = '1',
    [SourceInsuranceID] = '1234',
    [SourceInsuranceName] = 'Updated Name'
UNION
SELECT
    [Key] = NULL,    -- New entity
    [DataSource] = '2',
    [SourceInsuranceID] = '5678',
    [SourceInsuranceName] = 'New Insurance'

Execution Steps:

  1. Fetches existing entity with Key = 'IM-1' from database
  2. Entity IM-1 already has Active = true and Insurance = 'I-100' (not in query)
  3. Merges query results into IM-1: updates SourceInsuranceID and SourceInsuranceName, preserves Active and Insurance
  4. Generates new key IM-2 for the entity without a key
  5. New entity IM-2 gets default values for Active and Insurance (not merged, since no existing entity)
  6. Saves both entities

Usage Examples

Example 1: Load New InsuranceMap Entities from External Source

var task = new LoadEntitiesFromQueryTask
{
    EntityType = nameof(InsuranceMap),
    KeyPrefix = "IM",
    Query = @"
        SELECT
            [DataSource] = '1',
            [SourceInsuranceID] = '1234',
            [SourceInsuranceName] = 'Blue Cross',
            [Active] = CAST(1 AS BIT),
            [Insurance] = 'I-1'
        UNION
        SELECT
            [DataSource] = '1',
            [SourceInsuranceID] = '5678',
            [SourceInsuranceName] = 'Aetna',
            [Active] = CAST(1 AS BIT),
            [Insurance] = 'I-2'"
};
// Result: Creates IM-1 and IM-2 with specified values

Example 2: Update Existing Facilities with Partial Data

var task = new LoadEntitiesFromQueryTask
{
    EntityType = nameof(Facility),
    KeyPrefix = "F",
    Query = @"
        SELECT
            [Key] = 'F-[id]',  -- Existing facility
            [Name] = 'Updated Facility Name',
            [FacilityType] = 'Hospital'
        -- Note: Other fields like Address, Phone, etc. are preserved from existing data"
};
// Result: Updates only Name and FacilityType, preserves other fields
// ActivityLog entries created for Name and FacilityType changes

Example 3: Mix of New and Existing Entities

var task = new LoadEntitiesFromQueryTask
{
    EntityType = nameof(InsuranceMap),
    KeyPrefix = "IM",
    Query = @"
        -- Existing entities (will be updated/merged)
        SELECT [Key] = 'IM-1', [DataSource] = '1', [SourceInsuranceID] = '1234', [SourceInsuranceName] = 'Updated 1'
        UNION
        SELECT [Key] = 'IM-5', [DataSource] = '1', [SourceInsuranceID] = '5678', [SourceInsuranceName] = 'Updated 2'
        UNION
        -- New entities (will get auto-generated keys)
        SELECT [Key] = NULL, [DataSource] = '2', [SourceInsuranceID] = '9012', [SourceInsuranceName] = 'New 1'
        UNION
        SELECT [Key] = NULL, [DataSource] = '2', [SourceInsuranceID] = '3456', [SourceInsuranceName] = 'New 2'"
};
// Result: Updates IM-1 and IM-5, creates IM-6 and IM-7 (or next available numbers)

Example 4: Using Empty KeyPrefix to Default to EntityType

var task = new LoadEntitiesFromQueryTask
{
    EntityType = nameof(Provider),
    KeyPrefix = "",  // Will use "Provider" as the prefix
    Query = @"
        SELECT
            [FirstName] = 'John',
            [LastName] = 'Smith',
            [NPI] = '[id]'"
};
// Result: Creates Provider-1 with specified values

Example 5: Loading from FileQueue for Sequential Processing

// First task: Add files to queue
var queueTask = new AddFilesToQueueTask
{
    Directory = @"C:\Data\Facilities\",
    Pattern = "*.csv",
    Table = "FileQueue"
};

// Second task: Load facilities from queued files
var loadTask = new LoadEntitiesFromQueryTask
{
    EntityType = nameof(Facility),
    KeyPrefix = "F",
    Query = @"
        SELECT DISTINCT
            f.FacilityName AS [Name],
            f.FacilityType AS [FacilityType],
            f.Address,
            f.City,
            f.State,
            f.Zip
        FROM FileQueue fq
        CROSS APPLY
            dbo.ParseDelimitedFile(fq.FileName, ',', 1) f  -- Assuming a parsing function
        WHERE fq.FileName LIKE 'facility_%'"
};
// Result: Loads facilities from all queued CSV files

Related Tasks Comparison

Task Use Case Data Source Merge Logic Key Generation
LoadEntitiesFromQuery Load/update any entity type Custom SQL query Smart merge (preserves non-queried fields) Auto-generates for entities without keys
ImportSqlTask Import into non-entity tables SQL query Full replace or append N/A (not entity-based)
LoadDataFileTask Import from delimited files File with spec Append or replace table N/A (file-driven)
ExecuteMergeSqlTask Synchronize table data SQL query MERGE statement (insert/update/delete) N/A (SQL-driven)
LoadDimensionMembersTask Sync dimension metadata Entity tables Full replace dimension members Entity keys used directly
LoadVisitsTask Load visit batch data Custom SQL queries Specialized visit merging Visit key generation

Key Differentiators:

Performance Considerations

Database Impact

Optimization Tips

Scenario Recommendation Rationale
Large bulk loads Include Key column in query only for updates Avoids fetching existing entities when loading all-new data
Frequent small updates Include Key for all rows in query Enables efficient merge and preserves existing field values
Mixed new/existing Explicitly set Key to NULL for new entities Makes intent clear and ensures proper merge behavior
Change auditing needed Use loggable entity types (Facility, etc.) Automatically captures field changes to ActivityLog
Large result sets Ensure query is properly indexed Task reads entire result set into memory before processing
Partial field updates Only include fields to update in SELECT Non-included fields preserved from existing entities

Concurrency Considerations

Error Handling

The task uses fail-fast error handling with immediate exception throwing:

// From EntityLoader.Load() method
if (field == null)
{
    throw new Exception($"{entityName} is not a valid entity type. Please provide a valid entity type.");
}

if (!allowNewEntities && !columnNames.Any(x => x == "Key"))
{
    throw new Exception("Key column not found");
}

if (!allowNewEntities && oldEntities.Count != entities.Count)
{
    throw new EntityNotFoundException($"Entities specified in query do not exist in table {field.Name}");
}

Key Behaviors:

Important: Activity log changes are queued and logged before the save operation. If the save fails, activity logs may still be written to the database.

Common Pitfalls

Issue Problem Solution
Entity type name mismatch EntityNotFoundException: "CustomEntity is not a valid entity type" Use exact entity class names (e.g., InsuranceMap, not Insurance_Map). Check CustomerSchema.cs for valid types.
Duplicate keys within same data source SqlException on save due to unique constraint violation (e.g., DataSource + SourceInsuranceID for InsuranceMap) Ensure query doesn't return duplicate combinations of unique constraint fields. Add DISTINCT or check source data quality.
KeyPrefix validation failure InvalidOperationException: "Key Prefix must have a value" from LoadEntitiesFromQueryTaskField.Verify() Set KeyPrefix to a non-empty value or explicitly set to "" (empty string) to use EntityType as prefix. Null values fail validation.
Unintended field overwrites Existing entity fields unexpectedly reset to defaults Only include fields you want to update in the SELECT statement. Non-selected fields are preserved from existing entities.
Missing column names Entity properties not populated despite being in query Ensure query column names exactly match entity property names (case-sensitive). Use [PropertyName] aliases if needed.
No activity logs for changes Expected activity logs not created for updated entities Only entities implementing IChangeTrackable (like Facility) generate activity logs. Check if your entity type supports change tracking.
Keys not auto-generated Expecting auto-generated keys but entities have null keys Ensure Key column is either omitted from query or explicitly set to NULL for new entities. Non-null empty strings may not trigger generation.
Merge not happening Existing entity fields unexpectedly lost Ensure Key column is included in the SELECT statement and contains the exact existing entity key. Keys are case-sensitive.

Code References