Raj

Posted on Dec 31, 2020

Linq2DB CRUD Operations

#linq2db #csharp #dotnet #sql

Get more information regarding Linq2DB here

Here I have listed some methods for basic CRUD operations using linq2db.

Note:- This information is not for beginners as here chunks of code are present not complete crud operations are performed.

CreateTable and DropTable

using (var db = new DataConnection())
{
    try { db.DropTable<TestTable>(); } catch {}

    db.CreateTable<TestTable>();
}

Insert

This method inserts a single object.

using (var db = new DataConnection())
{
    db.Insert(new TestTable
    {
        Name = "Crazy Frog",
    });
}

This method takes Expression Tree and converts it to SQL

using (var db = new DataConnection())
{
    db.GetTable<TestTable>()
        .Insert(() => new TestTable
        {
            Name      = "Crazy Frog",
            CreatedOn = Sql.CurrentTimestamp
        });
}

This method generates INSERT INTO / SELECT statement

using (var db = new DataConnection())
{
    db
        .GetTable<TestTable>()
        .Where(t => t.Name == "Crazy Frog")
        .Insert(
            db.GetTable<TestTable2>(),
            t => new TestTable2
            {
                Name      = t.Name + " II",
                CreatedOn = t.CreatedOn.Value.AddDays(1)
            });
}

Into, Value, Insert

Alternative for those who don't like new operator for Insert.

using (var db = new DataConnection())
{
    db
        .GetTable<TestTable>()
        .Where(t => t.Name == "Crazy Frog")
        .Into(db.GetTable<TestTable2>())
            .Value(t => t.Name,      t => t.Name + " II")
            .Value(t => t.CreatedOn, t => t.CreatedOn.Value.AddDays(1))
        .Insert();
}

InsertWithIdentity

This method inserts a row and returns identity value

using (var db = new DataConnection())
{
    object identity = db.GetTable<TestTable>()
        .InsertWithIdentity(() => new TestTable
        {
            Name      = "Crazy Frog",
            CreatedOn = Sql.CurrentTimestamp
        });
}

InsertOrUpdate

This method updates an existing record or inserts a new one if it does not exist.

using (var db = new DataConnection())
{
    db.GetTable<TestTable3>()
        .InsertOrUpdate(
            () => new TestTable3
            {
                ID   = 5,
                Name = "Crazy Frog",
            },
            t => new TestTable3
            {
                Name = "Crazy Frog IV",
            });
}

InsertOrReplace

This method updates all columns of an existing record or inserts new one if it does not exist

using (var db = new DataConnection())
{
    db.InsertOrReplace(
        new TestTable3
        {
            ID   = 5,
            Name = "Crazy Frog",
        });
}

Update

This method updates all columns of existing record

using (var db = new DataConnection())
{
    db.Update(
        new TestTable3
        {
            ID   = 5,
            Name = "Crazy Frog",
        });
}

This method updates only columns you specify

using (var db = new DataConnection())
{
    db
        .GetTable<TestTable>()
        .Where(t => t.ID == 1)
        .Update(t => new TestTable
        {
            Name = "Crazy Frog",
        });
}

Overload with WHERE clause

using (var db = new DataConnection())
{
    db
        .GetTable<TestTable>()
        .Update(
            t => t.ID == 1,
            t => new TestTable
            {
                Name = "Crazy Frog",
            });
}

An alternative for those who don't like new operator for Update

using (var db = new DataConnection())
{
    db
        .GetTable<TestTable>()
        .Where(t => t.ID == 1)
        .Set(t => t.Name,      t => "Crazy Frog IV")
        .Set(t => t.CreatedOn, t => t.CreatedOn.Value.AddHours(1))
        .Update();
}

Delete

This method deletes an existing record

using (var db = new DataConnection())
{
    db
        .GetTable<TestTable>()
        .Where(t => t.ID == 1)
        .Delete();
}

Find more information on 3rd party Linq2db documentation here

DEV Community