Data.Linq.Extensions

Home / Data / Linq

BLToolkit Linq Extensions

Along with standard functions BLToolkit supports additional extensions.

Select

Sometimes we need to retrieve some data from database server itself. For example, it can be the current server datetime:

SELECT 
    CURRENT_TIMESTAMP as [c1]

It can be done in the following way::

static void SimpleSelectTest()
{
    using (var db = new NorthwindDB())
    {
        var value = db.Select(() => Sql.CurrentTimestamp);

        Console.WriteLine(value);
    }
}

Database, Owner, and Table names

Normally database, owner, and table names are defined by using attributes. However, sometimes we need to specify those names right in our query. The following methods can be used to do that:

static void NameTest()
{
    using (var db = new NorthwindDB())
    {
        var list = db.Parent
            .DatabaseName("BLToolkitData")
            .OwnerName   ("dbo")
            .TableName   ("Parent")
            .ToList();
    }
}

DML operations

Most of the existing Linq providers implement data manipulation operations (Insert, Update, Delete) by using Entity Services functionality. Instead, BLToolkit provides complete set of DML operation including manipulation of multiple records by single query.

Insert

The following method allows inserting new record in a table:

db.Employee.Insert(() => new Northwind.Employee
{
    FirstName = "John",
    LastName  = "Shepard",
    Title     = "Spectre",
    HireDate  = Sql.CurrentTimestamp
});

SQL:

INSERT INTO [Employees] 
(
    [FirstName],
    [LastName],
    [Title],
    [HireDate]
)
VALUES
(
    'John',
    'Shepard',
    'Spectre',
    CURRENT_TIMESTAMP
)

Unfortunately, this way does not allow working with objects that do not have default constructor or have read only properties. For this kind of objects BLToolkit provides an alternative Insert:

db
    .Into(db.Employee)
        .Value(e => e.FirstName, "John")
        .Value(e => e.LastName,  "Shepard")
        .Value(e => e.Title,     "Spectre")
        .Value(e => e.HireDate,  () => Sql.CurrentTimestamp)
    .Insert();

Take note of the way to assign the last field - lambda is used instead of value. It's required as we assign an SQL expression value to the field.

The following functions insert multiple rows:

db.Region
    .Where(r => r.RegionID > 2)
    .Insert(db.Region, r => new Northwind.Region()
    {
        RegionID          = r.RegionID + 100,
        RegionDescription = "Copy Of " + r.RegionDescription
    });

The alternative:

db.Region
    .Where(r => r.RegionID > 2)
    .Into(db.Region)
        .Value(_ => _.RegionID,          r => r.RegionID + 100)
        .Value(_ => _.RegionDescription, r => "Copy Of " + r.RegionDescription)
    .Insert();

SQL:

INSERT INTO [Region] 
(
    [RegionID],
    [RegionDescription]
)
SELECT
    [r].[RegionID] + 100,
    'Copy Of ' + [r].[RegionDescription]
FROM
    [Region] [r]
WHERE
    [r].[RegionID] > 2

InsertWithIdentity

This method inserts a record into the table which has an identity field and returns this field value. The identity field has to be decorated with the Identity attribute.

var value = db.Employee.InsertWithIdentity(() => new Northwind.Employee
{
    FirstName = "John",
    LastName  = "Shepard",
    Title     = "Spectre",
    HireDate  = Sql.CurrentTimestamp
});

var value =
    db
        .Into(db.Employee)
            .Value(e => e.FirstName, "John")
            .Value(e => e.LastName,  "Shepard")
            .Value(e => e.Title,     () => "Spectre")
            .Value(e => e.HireDate,  () => Sql.CurrentTimestamp)
        .InsertWithIdentity();

SQL:

INSERT INTO [Employees] 
(
    [FirstName],
    [LastName],
    [Title],
    [HireDate]
)
VALUES
(
    'John',
    'Shepard',
    'Spectre',
    CURRENT_TIMESTAMP
)

SELECT SCOPE_IDENTITY()

Update

The following methods update one or more rows in a table:

Update using predicate:

db.Employee
    .Update(
        e => e.Title == "Spectre",
        e => new Northwind.Employee
        {
            Title = "Commander"
        });

Update using sub query:

db.Employee
    .Where(e => e.Title == "Spectre")
    .Update(e => new Northwind.Employee
    {
        Title = "Commander"
    });

Read only property update:

db.Employee
    .Where(e => e.Title == "Spectre")
    .Set(e => e.Title, "Commander")
    .Update();

All those queries produce the following SQL:

UPDATE
    [e]
SET
    [Title] = 'Commander'
FROM
    [Employees] [e]
WHERE
    [e].[Title] = 'Spectre'

Update with involving a table field in a query:

db.Employee
  .Where(e => e.Title == "Spectre")
  .Set(e => e.HireDate, e => e.HireDate.Value.AddDays(10))
  .Update();

SQL:

UPDATE
  [e]
SET
  [HireDate] = DateAdd(Day, 10, [e].[HireDate])
FROM
  [Employees] [e]
WHERE
  [e].[Title] = 'Spectre'

Delete

Delete using predicate:

db.Employee.Delete(e => e.Title == "Spectre");

Delete using sub query:

db.Employee
    .Where(e => e.Title == "Spectre")
    .Delete();

SQL:

DELETE [e]
FROM
    [Employees] [e]
WHERE
    [e].[Title] = 'Spectre'

DbManager Extensions

As well as Linq extensions to do DML operations without using an existing data object, BLToolkit can also do these operation on existing data objects easily. These extensions can operate from the DbManager or DataContext level.

Northwind.Employee employee=GetEmployee(); //load an employee somewhere else
employee.Title="Spectre";
db.Update<Northwind.Employee>(employee);

This will update the employee record in the database according to the field set by the identity. This prevents having to manually type each field in the case that you're not sure which fields have been changed.

There are also similar Insert, InsertWithIdentity, and Delete extension methods.