EF Core Migration: InsertData() with using a value from a select statement

Question

User-1188570427 posted

Hello,

I'm new to EF Core migrations.

I am trying to InsertData() but a value of that insert statement is dependent on a SELECT query to obtain and Id.

How can I do this in the UP() method?

Answer 1

User-17257777 posted

Hi tvb2727,

From your description, I am a little confused. In which scene you need to do like this? I think it is more common do it in a controller action. If you want to initialize data, it's a better choice to do it with Seed Data. 

Best Regards,

Jiadong Meng

Answer 2

User-1188570427 posted

Hi tvb2727,

From your description, I am a little confused. In which scene you need to do like this? I think it is more common do it in a controller action. If you want to initialize data, it's a better choice to do it with Seed Data. 

Best Regards,

Jiadong Meng

Hello,

Yes, so we are inserting data into the database as a new record. Part of the insert statement has a value that is from a different table. I need to query the database to pull that value in from the table somehow?

        migrationBuilder.InsertData("Types", new string[] { "TypeId", "TypeName" }, new string[] { "6", "GridDefaultState" }, "dbo");

Above you see the "TypeId" is set directly to 6. But what if I needed to pull that "TypeId" from the database and set it as a variable?

Answer 3

User475983607 posted

It's very simple.  Write a T-SQL script in SSMS to build and test the script.  Copy the T-SQL to your migration file using migrationBuilder.Sql().

    public partial class SeedLeaClaimsMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
BEGIN TRANSACTION;  

BEGIN TRY  
	--
	--INSERT User Claim
	-- 
	INSERT INTO [dbo].[AspNetUserClaims] (
		 [ClaimType]
		,[ClaimValue]
		,[UserId]
	)
	SELECT 'http://schemas.dpi.state.nc.us/2018/04/identity/claims/qwert',
			u.access_code, 
			au.id
	FROM mydb.dbo.user AS u
		INNER JOIN dbo.AspNetUsers AS au ON u.user_id = au.Id 

END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  

    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;
		
	PRINT 'Transaction failed'  
END CATCH;  

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
	PRINT 'Transaction completed successfully'
GO ");
        }