Linq2DB CRUD Operations
Tags: linq2db, csharp, dotnet, sql
Published: 2020-12-31 11:04:20

linq2db is a [.NET Foundation](https://dotnetfoundation.org/) project.

Get more information regarding Linq2DB [here](https://github.com/linq2db/linq2db)

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
```cs
using (var db = new DataConnection())
{
    try { db.DropTable<TestTable>(); } catch {}

    db.CreateTable<TestTable>();
}
```
------
###Insert
This method inserts a single object.
```cs
using (var db = new DataConnection())
{
    db.Insert(new TestTable
    {
        Name = "Crazy Frog",
    });
}
```
------
###This method takes Expression Tree and converts it to SQL
```cs
using (var db = new DataConnection())
{
    db.GetTable<TestTable>()
        .Insert(() => new TestTable
        {
            Name      = "Crazy Frog",
            CreatedOn = Sql.CurrentTimestamp
        });
}
```
------
###This method generates INSERT INTO / SELECT statement
```cs
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.
```cs
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

```cs
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.

```cs
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
```cs
using (var db = new DataConnection())
{
    db.InsertOrReplace(
        new TestTable3
        {
            ID   = 5,
            Name = "Crazy Frog",
        });
}
```

##Update
This method updates all columns of existing record
```cs
using (var db = new DataConnection())
{
    db.Update(
        new TestTable3
        {
            ID   = 5,
            Name = "Crazy Frog",
        });
}
```

##This method updates only columns you specify
```cs
using (var db = new DataConnection())
{
    db
        .GetTable<TestTable>()
        .Where(t => t.ID == 1)
        .Update(t => new TestTable
        {
            Name = "Crazy Frog",
        });
}
```

##Overload with WHERE clause
```cs
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
```cs
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
```cs
using (var db = new DataConnection())
{
    db
        .GetTable<TestTable>()
        .Where(t => t.ID == 1)
        .Delete();
}
```

Find more information on 3rd party Linq2db documentation [here](http://blog.linq2db.com/2015/05/linq-crud-operations.html?view=timeslide)