I have the following 2 tables:
public class TestA
{
public int Id { get; set; }
public string Name { get; set; }
public IEnumerable<TestB> Test { get; set; }
}
public class TestB
{
public int Id { get; set; }
public int TestAId { get; set; }
}
The idea is I can access all the TestB records related to TestA from my TestA object. What I am trying to figure out is how do I insert into both Test A and TestB, especially if TestB has multiple rows related to Test A. Here is what I tried:
var data = new TestA
{
Name = "ABC",
Id = 1,
Test = new List<TestB>
{
new TestB() { Id = 1, TestAId = 1},
new TestB() {Id = 2, TestAId = 1},
new TestB() {Id = 3, TestAId = 1}
}
};
var query = "INSERT INTO TestA(Id, Name) VALUES(@Id, @Name) INSERT INTO TestB(Id, TestAId) VALUES(@Test.TestB.Id, @Test.TestB.TestAId)";
using (var con = conn)
{
con.Execute(query, data);
}
I know that we can give dapper a list and it is smart enough to enumerate that list and insert individual records, but how do I accomplish the same thing if that list is child of an object? I tried making my values also be @Test.Id, @Test.TestAId
but it had exact same error.
The member of type Test.TestB cannot be used as a parameter value
Could you try this:
var data = new TestA
{
Name = "ABC",
Id = 1,
Test = new List<TestB>
{
new TestB() { Id = 1, TestAId = 1},
new TestB() {Id = 2, TestAId = 1},
new TestB() {Id = 3, TestAId = 1}
}
};
var query = "INSERT INTO TestA(Id, Name) VALUES(@Id, @Name) INSERT INTO TestB(Id, TestAId) VALUES(@Test.TestB.Id, @Test.TestB.TestAId)";
using (var con = conn)
{
var query = "INSERT INTO TestA(Id, Name) VALUES(@Id, @Name)";
con.Execute(query, new { Id = data.Id, Name = data.Name });
foreach (var item in data.Test)
{
string processQuery = "INSERT INTO TestB(Id, TestAId) VALUES (@Id, @TestAId )";
connection.Execute(processQuery, item);
}
}