Command Query Update

To utilize a command query approach to updating data we need three things

1. Db Model
2. User Defined Table Type
3. Stored Procedure that accepts a data table as a parameter.

The following is an example of the code required.

DB Model

namespace SubSonic.Integration.Tests.Database.Models
{
    using Infrastructure;
    using Procedures;

    [Table(nameof(RealEstateProperty))]
    [DbUserDefinedTableType(nameof(RealEstateProperty))]
    [DbCommandQuery(DbQueryType.Update, typeof(UpdateRealEstateProperty))]
    public class RealEstateProperty
    {
        public RealEstateProperty()
        {
            Units = new HashSet<Unit>();
        }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public int StatusID { get; set; }

        [ForeignKey(nameof(StatusID))]
        public virtual Status Status { get; set; }

        public virtual bool HasParallelPowerGeneration { get; set; }

        public virtual bool HasSmartAccessControl { get; set; }

        public virtual bool HasElectricVehicleChargeStations { get; set; }

        [SuppressMessage("Usage", "CA2227:Collection properties should be read only", Justification = "<Pending>")]
        public virtual ISubSonicCollection<Unit> Units { get; set; }
    }
}

DB Stored Procedure Definition

using System.Collections.Generic;

namespace SubSonic.Integration.Tests.Database.Procedures
{
    using Infrastructure;

    [DbStoredProcedure(nameof(UpdateRealEstateProperty))]
    public class UpdateRealEstateProperty
        : DbSubSonicCommandQueryProcedure<Models.RealEstateProperty>
    {
        public UpdateRealEstateProperty(IEnumerable<IEntityProxy> properties)
            : base(properties) { }
    }
}

User Defined Table Type

/****** Object:  UserDefinedTableType [dbo].[RealEstateProperty]    Script Date: 6/8/2020 3:29:37 PM ******/
CREATE TYPE [dbo].[RealEstateProperty] AS TABLE(
	[ID] [int] NOT NULL,
	[StatusID] [int] NOT NULL,
	[HasParallelPowerGeneration] [bit] NOT NULL,
	[HasSmartAccessControl] [bit] NOT NULL,
	[HasElectricVehicleChargeStations] [bit] NOT NULL
)
GO

DB Model Table

CREATE TABLE [dbo].[RealEstateProperty](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StatusId] [int] NOT NULL,
	[HasParallelPowerGeneration] [bit] NOT NULL,
	[HasSmartAccessControl] [bit] NOT NULL,
	[HasElectricVehicleChargeStations] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RealEstateProperty] ADD  DEFAULT ((0)) FOR [HasParallelPowerGeneration]
GO

ALTER TABLE [dbo].[RealEstateProperty] ADD  DEFAULT ((0)) FOR [HasSmartAccessControl]
GO

ALTER TABLE [dbo].[RealEstateProperty] ADD  DEFAULT ((0)) FOR [HasElectricVehicleChargeStations]
GO

ALTER TABLE [dbo].[RealEstateProperty]  WITH CHECK ADD  CONSTRAINT [FK_RealEstateProperty_Status] FOREIGN KEY([StatusId])
REFERENCES [dbo].[Status] ([Id])
GO

ALTER TABLE [dbo].[RealEstateProperty] CHECK CONSTRAINT [FK_RealEstateProperty_Status]
GO

Update Stored Procedure

CREATE PROCEDURE [dbo].[UpdateRealEstateProperty]
	@entities [dbo].[RealEstateProperty] readonly,
	@error VARCHAR(MAX) out
AS
	BEGIN TRAN;

	DECLARE 
		@result INT = 0,
		@output [dbo].[RealEstateProperty];

	BEGIN TRY
		UPDATE [property] SET
			[StatusId] = [data].[StatusID],
			[HasParallelPowerGeneration] = [data].[HasParallelPowerGeneration]
		OUTPUT INSERTED.* INTO @output
		FROM [dbo].[RealEstateProperty] [property]
			JOIN @entities [data] ON [property].[Id] = [data].[ID];
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN;

		SELECT
			@error = ERROR_MESSAGE(),
			@result = ERROR_NUMBER()
			
		RETURN @result;
	END CATCH;

	COMMIT TRAN;

	SELECT
		ID,
		StatusID,
		HasParallelPowerGeneration,
		HasSmartAccessControl,
		HasElectricVehicleChargeStations
	FROM @output;
RETURN @result