CODEWITHSUNDEEP
c#collectionsdomain-driven-design
CesarGon
CesarGon

Reputation: 15345

Best strategy to update collection on database

We are developing a system using C# 4 and SQL Server 2008 R2, and following domain-driven design principles. No ORM is used. There are situations where we have an entity that holds a collection of another entity, such as:

public class Project
{
    public IdCollection ParticipantUsers
    {
        get
        {
            //...
        }
    }

    public void AddParticipantUser(Id idUser)
    {
        //...
    }

    public void RemoveParticipantUser(Id idUser)
    {
        //...
    }
}

The ParticipantUsers property of Project returns a collection of the ids of the users that participate in it. The AddParticipantUser and RemoveParticipantUser methods, well, you get it.

The problem we found is as follows. When a project instance is sent to the repository to be made updated on the database, the ParticipantUsers collection of ids may have changed from the last time it was retrieved: some ids may have been added, some may have been removed, and some may be exactly the same. We can use a brute force approach and delete all participant users for this project from the database, and then re-insert the current ones, but I would like to explore better approaches.

What better approaches can you think of, that allow me to only insert those ids that have been added, delete those that have been removed, and leave untouched those that are still there in the collection?

Upvotes: 3

Views: 473

Answers (5)

JefClaes
JefClaes

Reputation: 3373

Have you considered serializing all the id's into a single column? This way, you're just updating one record. If you're worried about collections having changed in the meanwhile, you might want to implement optimistic concurrency.

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88092

Our approach (using SQL Server 2008 R2) is to utilize a table-value parameter (TVP) in combination with a merge statement.

This way the code passes the list of items as a table to a stored procedure. The proc then uses that table in a merge statement which does the insert/update/delete.

You need a User-Defined Table Type, for example:

CREATE TYPE [dbo].[Participants] AS TABLE(
    [ProjectId] [int] NOT NULL,
    [ParticipantId] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [ProjectId] ASC,
    [ParticipantId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

Then you'll need a C# class to hold the data. Just modify your IdCollection to be similar to:

public class IdCollection : List<ParticipantUsers>, IEnumerable<SqlDataRecord> {
    #region IEnumerable<SqlDataRecord> Members

    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator() {
        SqlDataRecord rec = new SqlDataRecord(
            new SqlMetaData("ProjectId", System.Data.SqlDbType.Int),
            new SqlMetaData("ParticipantId", System.Data.SqlDbType.Int)
        );

        foreach (ParticipantUser ans in this) {
            rec.SetInt32(0, ans.ProjectId);
            rec.SetInt32(1, ans.ParticipantId);
            yield return rec;
        }
    }

    #endregion

}

Your stored procedure will look something like this:

CREATE PROCEDURE [dbo].[ParticpantUpdate]
    @Particpants core.Participants READONLY
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO dbo.ProjectParticipants pp
        USING @Particpants RG
            ON (RG.ProjectId = pp.ProjectId)
            AND (RG.ParticipantId = pp.ParticipantId)
        WHEN NOT MATCHED BY TARGET THEN
            INSERT( ProjectId, ParticipantId)
            VALUES( RG.ProjectId, RG.ParticipantId)
        WHEN NOT MATCHED BY SOURCE
                AND target.ProjectId in (SELECT ProjectId from @Participants) THEN
                DELETE;
    ;


END

To call the proc (using Enterprise Library):

SqlDatabase db = DatabaseFactory.CreateDatabase("myconnstr") as SqlDatabase;

using ( DbCommand dbCommand = db.GetStoredProcCommand("dbo.ParticipantUpdate") ) {
    db.AddInParameter(dbCommand, "Participants", SqlDbType.Structured, participantList);

    db.ExecuteNonQuery(dbCommand);

} // using dbCommand

Upvotes: 1

Eben Roux
Eben Roux

Reputation: 13256

I guess event sourcing would be ideal if you do much of this sort of thing but typically we, as developers, don't get to use new techniques much :)

Since I am no fan of an ORM what I have done previously is simply keep a list of the changes along the same lines as a unit of work would do. So each time a participant is added I would add it to the AddedParticipants collection and for removed participants I would add to the RemovedParticipants collection. I guess one could take it further to a single list with type of change for each entry. But I am sure you get the idea.

The repository would then use the tracked changes in performing the relevant data manipulation. May not be the prettiest solution but it gets the job done.

I might add that for shorter lists I do simply delete and re-insert but, as you mentioned, it may be somewhat heavy in this scenario and one size does not fit all :)

Of course the reloading the list and comparing would work just fine as Arie has suggested.

Upvotes: 2

Yugang Zhou
Yugang Zhou

Reputation: 7283

Actually I think the delete-reinsert approach is better than any solution based on comparison.

Firstly, it's easy to implement.

Secondly, it avoids another data fetch for comparison.

Could you explain further about why this approach is not preferred, maybe I don't catch it.

Upvotes: 2

Arie
Arie

Reputation: 5373

orgData - is the list/table/other enumerable object that contains your original data from database (IdCollection ParticipantUsers before changes in this case)

newData - is the list/table/other enumerable object that contains your data to update 

var orgList = orgData.Select(a => a.Id);
var newList = newData.Select(a => a.Id);

var itemsToAdd = newData.Where(a => !orgList.Contains(a.Id));
var itemsToDel = orgData.Where(a => !newList.Contains(a.Id));

Upvotes: 0

Related Questions