DataVault场景下，能否通过单个POCO对多个表执行增改操作？

Great question—this is a super common pain point when adapting ORMs to Data Vault patterns, and the good news is you absolutely can make this work with a single POCO, even with PetaPoco (or other ORMs if you switch later). Let’s break down how to approach this, starting with PetaPoco since it’s your top candidate.

For PetaPoco: Custom Repository Wrappers + Multi-Table Mapping #

PetaPoco doesn’t natively support multi-table CRUD out of the box, but you can wrap its functionality in a repository class to abstract the Hub/Satellite split away from your application code. Here’s how to implement this for your Client POCO:

1. Insert Operation (Atomic Hub + Satellite Create) #

First, you’ll want to handle inserts atomically using a transaction to ensure both the Hub and Satellite are created together (or neither is, if something fails). Here’s a sample repository method:

public void InsertClient(Client client)
{
    using var db = new Database("YourConnectionString");
    using var transaction = db.GetTransaction();

    try
    {
        // Insert into Hub first, get the generated SeqId
        var hubId = db.Insert(new 
        {
            ClientId_BK = client.ClientId_BK
        }, "H_Client_SeqId"); // Specify the identity column name

        // Map the generated SeqId back to the POCO
        client.H_Client_SeqId = (long)hubId;

        // Insert into Satellite using the new Hub SeqId
        db.Insert(new 
        {
            H_Client_SeqId = client.H_Client_SeqId,
            Address = client.Address,
            Phone = client.Phone
            // Add all other Satellite attributes here
        }, "HS_Client");

        transaction.Complete();
    }
    catch
    {
        transaction.Abort();
        throw; // Re-throw to let the caller handle the error
    }
}

Your application code only needs to populate the ClientId_BK, Address, Phone, etc., and call this method—no need to worry about the separate tables.

2. Query Operation (Join Hub + Satellite into a Single POCO) #

For reading, PetaPoco’s MultiPoco feature lets you map a JOIN query directly to your Client POCO. You can define a helper method like this:

public Client GetClientByBusinessKey(string clientIdBk)
{
    using var db = new Database("YourConnectionString");
    var sql = Sql.Builder
        .Select("h.ClientId_BK, h.H_Client_SeqId, s.Address, s.Phone") // Include all needed fields
        .From("H_Client h")
        .Join("HS_Client s ON h.H_Client_SeqId = s.H_Client_SeqId")
        .Where("h.ClientId_BK = @0", clientIdBk);

    // Map the joined result directly to your Client POCO
    return db.SingleOrDefault<Client>(sql);
}

If your POCO has a lot of attributes, you can use SELECT * (though be cautious with schema changes) or generate the select list dynamically to avoid typing every field.

General ORM Approach (Framework-Agnostic) #

If you ever switch away from PetaPoco, the core pattern stays the same:

• Use a Repository/Service Layer: Encapsulate all multi-table logic here, so your application code only interacts with the Client POCO.
• Leverage Transactions: Always wrap Hub/Satellite operations in a transaction to maintain Data Vault consistency.
• Attribute-Based Mapping: Use your ORM’s column mapping attributes to explicitly link POCO properties to their respective table columns. For example, in PetaPoco you’d add [Column("ClientId_BK")] to your property (though it’s optional if names match), and for Satellite fields, you can note their table in comments or use custom attributes if your ORM supports it.
• Upsert Logic: For updates, first check if the Hub exists via the Business Key. If it does, update the Satellite; if not, perform the insert flow above.

Key Notes #

• Atomicity is Critical: Never insert/update a Hub without its corresponding Satellite (or vice versa) without a transaction—this breaks Data Vault’s integrity rules.
• Performance: For large datasets, batch operations might be needed, but the single-POCO abstraction still holds (you’d just process batches of Client objects instead of one at a time).
• Mapping Tools: If your POCO has dozens of attributes, consider using a tool like AutoMapper to map between the POCO and the individual Hub/Satellite DTOs (though for PetaPoco, the direct anonymous object approach works well too).

内容的提问来源于stack exchange，提问作者AutomatedChaos