I want to use dynamic in order to be able to choose which columns to define and those I want to let the DB set the DEFAULT
value.
Is there a way to insert a dynamic
into a table and return the Id
?
I tried various overloads both on DataConnection
and ITable<>
but the only result I could achieve is getting the count of affected rows.
This is my code:
Person table schema
CREATE TABLE [dbo].[Person] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[Surname] VARCHAR (50) NOT NULL,
[BirthDate] DATE DEFAULT (getdate()) NOT NULL
);
.NET Core Console App
public class Person
{
public uint Id { get; set; }
public string Name { get; set; }
public string Surname { get; set; }
public DateTime BirthDate { get; set; }
}
public class PeopleDataConnection : DataConnection
{
public PeopleDataConnection(string connectionString)
: base(ProviderName.SqlServer, connectionString)
{ }
public ITable<Person> People => this.GetTable<Person>();
}
class Program
{
static async Task Main(string[] args)
{
await using (var db = new PeopleDataConnection("Server=DESKTOP-I41VSUN;integrated Security=SSPI;Database=MotoGPRiders;"))
{
var dynamicPerson = new { Name = "Cal", Surname = "Crutchlow", BirthDate = new DateTime(1985, 10, 29) };
var person_without_id = new Person { Name = "Valentino", Surname = "Rossi", BirthDate = new DateTime(1979, 2, 16) };
var affectedRowsCount = await db.InsertAsync(dynamicPerson, nameof(Person));
affectedRowsCount = await db.People
.Value(x => x.Name, person_without_id.Name)
.Value(x => x.Surname, person_without_id.Surname)
.Value(x => x.BirthDate, person_without_id.BirthDate)
.InsertAsync();
}
Console.ReadLine();
}
}
The first insert does the job but can't return the Id
, the second lets me choose the columns but not dynamically.
Is there a way to achieve this? Thanks in advance!
You can retrieve identity value via
InsertWithInt64IdentityAsync
function. There a lot of overloads for such insert:Also you can filter out properties which has to be inserted via overload which has
columnFilter
parameter:For variant with anonymous class, you have to create your own function which generates calls via reflection using
Value.Value.Value
+InsertWithInt64IdentityAsync
calls:And usage: