Updating complex objects

Question

I'm mainly looking at usage of Dapper, but in general, any comments relating SQL -> C# object mapping best practices would be appreciated.

I've got two super simple objects I'm working with:

public class Subcategory
{
    public Guid Id { get; set; }
    public Guid CategoryId { get; set; }
    public string Name { get; set; }
}

public class Category
{
    public Category()
    {
        Subcategories = new List<Subcategory>();
    }

    public Guid Id { get; set; }
    public string Name { get; set; }
    public ICollection<Subcategory> Subcategories { get; set; }
}

Id for both of them is set by the db on insert via T-SQL's newsequentialid().

I'm writing repository classes for each of the objects that conform to the following interface (actually implemented via an abstract class):

public interface IMaintanceRepository<T> where T: class
{
    Guid Create(T toCreate);
    T Read(Guid id);
    T Update(T toUpdate);
    void Delete(Guid id);
}

Right now, I'm trying to write the Update method in the Category repository. I have the following written, but I'm not sure if it's the most performant Dapper code as written.

public override Category Update(Category toUpdate)
{
    var deleteRemovedSubcategories = @"
        DELETE FROM dbo.Subcategories 
        WHERE CategoryId = @Id 
            AND Id NOT IN @Ids";

    var addNewSubcategories = @"
        INSERT INTO dbo.Subcategories (CategoryId, Name)
        VALUES (@CategoryId, @Name)";

    var updateExistingSubcategories = @"
        CREATE TABLE #subcategories (
            Id uniqueidentifier NOT NULL,
            CategoryId uniqueidentifier NOT NULL,
            Name varchar(50) NOT NULL
        );

        INSERT INTO #subcategories VALUES (@Id, @CategoryId, @Name);

        UPDATE s1
        SET s1.CategoryId = s2.CategoryId, s1.Name = s2.Name
        FROM dbo.Subcategories s1
        INNER JOIN #subcategories s2
        ON s1.Id = s2.Id;

        DROP TABLE #subcategories;";

    var updateCategory = @"
        UPDATE dbo.Categories
        SET Name = @Name
        OUTPUT INSERTED.*
        WHERE Id = @Id;

        SELECT * FROM dbo.Subcategories WHERE CategoryId = @Id";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Execute(deleteRemovedSubcategories, new { Id = toUpdate.Id, Ids = toUpdate.Subcategories.Select(s => s.Id) });
        connection.Execute(addNewSubcategories, toUpdate.Subcategories.Where(s => s.Id == Guid.Empty));
        connection.Execute(updateExistingSubcategories, toUpdate.Subcategories);
        using (var multi = connection.QueryMultiple(updateCategory, toUpdate))
        {
            var category = multi.ReadSingle<Category>();
            category.Subcategories = multi.Read<Subcategory>().ToList();
            return category;
        }
    }
}

Is there any suggested improvements to doing something like this?

Answer 1

I was looking at what you had coded here

var deleteRemovedSubcategories = @"
    DELETE FROM dbo.Subcategories 
    WHERE CategoryId = @Id 
        AND Id NOT IN @Ids";

and it took me a minute to figure out exactly what the Where clause was filtering, what finally gave it away was the name of the C# variable deleteRemovedSubcategories. If it were possible I would change some of these column names and/or T-SQL variables so that it is more clear what you are saying here.