Direct SQL Execution in DLinq
A very nice feature I find is to be able to execute a custom SQL statement in case you find the functionalities provided by the DataContext object is limited.
There are two main methods:
ExecuteQuery, which has two flavors of the ExecuteQuery method
1- Without parameters
IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
@"select c1.custid as CustomerID, c2.custName as ContactName
from customer1 as c1, customer2 as c2
where c1.custid = c2.custid"
);
We are executing a T-SQL statement by passing the query to the ExecuteQuery method. The return type if a collection of Customer classes.
2- With Parameters
IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
"select contactname from customers where city = {0}",
"London"
);
As you can see above, we are passing the City parameter to get a parameterized query.
There is important note here which is that, ExecuteQuery is capable of materializing the results into a collection of objects as long as the returned columns match the corresponding
entity class' columns.
Also, you can execute a command against the database using the ExecuteCommand as follows:
db.ExecuteCommand("UPDATE Products SET UnitPrice = UnitPrice + 1.00");
In the above command, we are updating the UnitPrice of the Products table.
Hope this helps,
Regards