LINQ 2 DB将动态插入表

2024-05-21 • 问答

我想使用动态，以便能够选择要定义的列以及我想让数据库设置DEFAULT值的列。是否可以将dynamic插入表并返回Id？

我在DataConnection和ITable<>上都尝试了各种重载，但是我唯一能达到的结果就是获取受影响的行数。

这是我的代码：

人员表架构

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控制台应用程序

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();
    }
}

第一个插入完成了工作，但无法返回Id，第二个插入使我可以选择列，但不能动态选择。

有没有办法做到这一点？预先感谢！

您可以通过InsertWithInt64IdentityAsync函数检索标识值。这样的插入有很多重载：

var insertedId = 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)
            .InsertWithInt64IdentityAsync();

您还可以过滤掉必须通过具有columnFilter参数的重载插入的属性：

var insertedId = await db.InsertAsync(person,e => e.MemberName.In("Name","Surname","BirthDate"));

对于具有匿名类的变体，您必须创建自己的函数，该函数使用Value.Value.Value + InsertWithInt64IdentityAsync调用通过反射生成调用：

public static class DynamicExtensions
{
    public static IValueInsertable<T> GenerateInsertable<T>(this ITable<T> table,object obj)
    {
        var dynamicAccessor = TypeAccessor.GetAccessor(obj.GetType());
        var accessor = TypeAccessor.GetAccessor<T>();
        var objValue = Expression.Constant(obj);
        IValueInsertable<T> insertable = null;
        foreach (var dynamicMember in dynamicAccessor.Members)
        {
            var found = accessor.Members.Find(m => m.Name == dynamicMember.Name);
            if (found == null)
                continue;
            var sourceParam = Expression.Parameter(typeof(T),"e");
            var property = Expression.Lambda(Expression.MakeMemberAccess(sourceParam,found.MemberInfo),sourceParam);
            Expression value = Expression.MakeMemberAccess(objValue,dynamicMember.MemberInfo);
            if (value.Type != found.Type)
                value = Expression.Convert(value,found.Type);
            value = Expression.Lambda(value);

            if (insertable == null)
            {
                var method = Methods.LinqToDB.Insert.T.ValueExpression.MakeGenericMethod(typeof(T),found.Type);
                insertable = (IValueInsertable<T>)method.Invoke(null,new object[] { table,property,value });
            }
            else
            {
                var method = Methods.LinqToDB.Insert.VI.ValueExpression.MakeGenericMethod(typeof(T),new object[] { insertable,value });
            }
        }

        return insertable;
    }

    public static long? InsertWithIdentityDynamic<T>(this ITable<T> table,object obj)
    {
        var insertable = GenerateInsertable(table,obj);
        if (insertable == null)
            return null;
        
        return insertable.InsertWithInt64Identity();
    }

    public static async Task<long?> InsertWithIdentityDynamicAsync<T>(this ITable<T> table,object obj,CancellationToken cancellationToken = default)
    {
        var insertable = GenerateInsertable(table,obj);
        if (insertable == null)
            return null;

        return await insertable.InsertWithInt64IdentityAsync(cancellationToken);
    }
}

和用法：

var insertedId = await db.GetTable<Person>()
    .InsertWithIdentityDynamicAsync(dynamicPerson);

LINQ 2 DB将动态插入表

iCMS 回答：LINQ 2 DB将动态插入表

大家都在问