more Select, for which BLToolkit creates a subquery. But sub-queries are not all Select. Thus, for example, the following code will not be created subquery:
from o in db.Order
let sum = o.Freight
where Math.Round(sum) >= 10
select o.Freight; |
BLToolkit finds that in such a subquery, there is no expression, but only appeal to the fields of the table and eliminate unnecessary nesting.
Additional features BLToolkit
In addition to supporting standard features Linq, BLToolkit implementing a number of additional extensions.
Select
Sometimes you need to get from the database server some data not included in any of the tables. For example, it may be the next request for the current date of the server:
SELECT
CURRENT_TIMESTAMP as [c1] |
BLToolkit Such functionality is implemented as follows:
static void SimpleSelectTest()
{
using (var db = new NorthwindDB())
{
var value = db.Select(() => Sql.CurrentTimestamp);
Console.WriteLine(value);
}
} |
DML operations
All existing Linq providers implement data manipulation operations (Insert, Update, Delete) by the Entity Services, which causes a lot of criticism among developers for many reasons. BLToolkit offers a complete set of operations DML, including operations to manipulate the set of records with one request.
Insert
The next method is to add a new entry in the database 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 method can not work with objects that do not have a default constructor, or has read-only properties. Therefore, for these objects BLToolkit offers 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(); |
SQL statement for this query is similar to the previous.
Notice the way to set the value of the last field - is used instead of just the value of lambda. This is necessary in order to assign a field value server variable.
The next group of functions allows you to work with multiple entries:
db.Region
.Where(r => r.RegionID > 2)
.Insert(db.Region, r => new Northwind.Region()
{
RegionID = r.RegionID + 100,
RegionDescription = "Copy Of " + r.RegionDescription
} |
And 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 is used to insert a record into a table that has an Identity field, and the return value of this field. Identity field should be labeled with IdentityAttribute.
var value = db.Employee.InsertWithIdentity(() => new Northwind.Employee
{
FirstName = "John",
LastName = "Shepard",
Title = "Spectre",
HireDate = Sql.CurrentTimestamp
} |
?
var value =
db
.Into(db.Employee)
.Va |