多对多插入

Question

下面是我想插入SQL的文件，我已经加载到一个c#对象(MyObject)，我使用的是Dapper, c#。我的主表是Person，子表Test, Staging表包含ID,PersonID, testd复合键

Question1:我不清楚如何将标签插入到Person中，因为标签是Person对象中的列表。

Question2:我如何修复下面的代码，因为我不清楚如何在每个循环中通过实体Test循环。我引用了Dapper多对多插入

{
"_id": "1",
"name": "Leiz Gab",
"tags": [
"tagA",
"tagB",
"tagC"
],
"tests": [
{
"id": 0,
"name": "A bsd"
},
{
"id": 1,
"name": "S Bsd"
}
]
}

我代码:

class Program
{
static void Main(string[] args)
{
var cs = @"Server=DESKTOP-FSNG2D1;Database=msdb;Trusted_Connection=True;";
using var con = new SqlConnection(cs);
con.Open();
List<MyObject> processList = new List<MyObject>();
MyObject obj = new MyObject();           
obj.A = "AB";
obj.B = "CD";
Test tst = new Test();
tst.Firstname = "A";
tst.Surname = "Avd";
Test tst1 = new Test();
tst1.Firstname = "B";
tst1.Surname = "Brgu";
obj.D.Add(tst);
obj.D.Add(tst1);
string pINSERT = "INSERT INTO [msdb].[dbo].[AzureBlob] VALUES(@A, @B, @C,@D)";
string tINSERT = "INSERT INTO [msdb].[dbo].[Test] VALUES(@Surname, @Firstname)";
string aINSERT = "INSERT INTO [msdb].[dbo].[Staging] (PersonID, TestID) VALUES(@PersonID, @TestID)";
var res = con.ExecuteScalarAsync(pINSERT);
foreach (var a in obj.D)
{
var testID = con.ExecuteScalarAsync<int>(tINSERT, a);
var arows =  con.ExecuteAsync(aINSERT, new { PersonID = res, TestID = testID });
}
}
}
public class MyObject
{
public string A { get; set; }
public string B { get; set; }
public List<string> C { get; set; }
public List<Test> D new List<Test>();
}
public class Test
{
public int ID { get; set; }
public string Surname { get; set; }
public string Firstname { get; set; }
}
public class Staging
{
public int ID { get; set; }
public int PersonID { get; set; }
public int TestID { get; set; }
}

Answer 1

在MyObject中设置list属性如下所示。您需要在使用列表之前初始化它。public List D {get;设置;} = new List();

Answer 2

第二次查询:

INSERT INTO [msdb].[dbo].[Test] VALUES(@Surname, @Firstname);
SELECT SCOPE_IDENTITY()

这应该意味着您的代码将接收生成的id，并从那里开始工作