Command Query Insert

To utilize a command query approach to inserting 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.Insert, typeof(InsertRealEstateProperty))]
    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 ICollection<Unit> Units { get; set; }
    }
}

DB Stored Procedure Definition

using System.Collections.Generic;

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

    [DbStoredProcedure(nameof(InsertRealEstateProperty))]
    public class InsertRealEstateProperty
        : DbSubSonicCommandQueryProcedure<Models.RealEstateProperty>
    {
        public InsertRealEstateProperty(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

Insert Stored Procedure

CREATE PROCEDURE [dbo].[InsertRealEstateProperty]
	@entities [dbo].[RealEstateProperty] readonly,
	@error VARCHAR(MAX) out
AS
	DECLARE @output [dbo].[RealEstateProperty],
			@result INT = 0;

	BEGIN TRY
		INSERT INTO [dbo].[RealEstateProperty]([StatusId], [HasParallelPowerGeneration], [HasSmartAccessControl], [HasElectricVehicleChargeStations])
		OUTPUT inserted.* INTO @output
		SELECT [StatusID], [HasParallelPowerGeneration], [HasSmartAccessControl], [HasElectricVehicleChargeStations] FROM @entities;
	END TRY
	BEGIN CATCH
		
		SELECT
			@error = ERROR_MESSAGE(),
			@result = ERROR_NUMBER()
			
		RETURN @result;
	END CATCH;

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