Methods
You can generate SQL statements with the help of methods that support the <IQueryable> interface.
Select, Where, OrderBy, GroupBy, Join – these methods are translated
to SQL naturally as SELECT, WHERE, GROUP BY, ORDER BY, and JOIN clauses accordingly.
The query below retrieves all companies from the USA that have orders later than the year 1998. The results of the query are ordered by OrderId.
CrmDemoContext.CrmDemoDataContext db = new CrmDemoContext.CrmDemoDataContext();
var query = db.Companies.Where(c => c.Country == "USA")
.Join(db.Orders.Where(o => o.OrderDate >= new DateTime(1998, 1, 1)),
c => c.CompanyID,
o => o.Company.CompanyID,
(c, o) => new
{
c.CompanyName,
o.OrderID,
o.OrderDate
}).OrderBy(p => p.OrderID).Select(p => p);
Dim db As New CrmDemoContext.CrmDemoDataContext()
Dim query = db.Companies.Where(Function(c) c.CompanyName = "USA") _
.Join(db.Orders.Where(Function(o) o.OrderDate >= new DateTime(1998, 1, 1)), _
Function(c) c.CompanyID, _
Function(o) o.Company.CompanyID, _
Function(c, o) New With { _
.CompanyName = c.CompanyName, _
.OrderID = o.OrderID, _
.OrderDate = o.OrderDate _
}).OrderBy(Function(p) p.OrderID).Select(Function(p) p)

Generated SQL
SELECT t1."CompanyName", t2."OrderID", t2."OrderDate"
FROM "Company" t1
INNER JOIN ("Orders" t2
LEFT OUTER JOIN "Company" t3 ON t2."CompanyID" = t3."CompanyID") ON t1."CompanyID" = t3."CompanyID"
WHERE (t1."Country" = :p0) AND (t2."OrderDate" >= :p1)
ORDER BY t2."OrderID"

Parameter Info
Parameter Name |
DbType |
Value |
p0 |
String |
USA |
p1 |
DateTime |
01.01.1998 0:00:00 |
Query
and ExecuteQuery
methods allow you to execute
the SQL code directly. The main difference between them
is that the Query method returns an object that implements
the IQueryable<T> interface,
and the ExecuteQuery returns an IEnumerable<T>.
This means that you can enhance the results of the Query execution.
The ExecuteCommand method
does not return any resultset. However, it allows to execution of any SQL
command directly, without any translation.
The query below returns the company list ordered by CompanyName.
CrmDemoContext.CrmDemoDataContext db = new CrmDemoContext.CrmDemoDataContext();
var query = db.Query("SELECT * FROM \"Company\"").OrderBy(c=>c.CompanyName);
Dim db As New CrmDemoContext.CrmDemoDataContext()
db.Query(Of Company)("SELECT * FROM ""Company""").OrderBy(Function(c) c.CompanyName)

Generated SQL
SELECT t1."CompanyID", t1."CompanyName", t1."PrimaryContact", t1."Web", t1."Email",
t1."AddressTitle", t1."Address", t1."City", t1."Region", t1."PostalCode",
t1."Country", t1."Phone", t1."Fax"
FROM (
SELECT * FROM "Company"
) t1
ORDER BY t1."CompanyName"
The SqlMethods class
provides access to a set of native SQL functions: Like, Coalesce, Between, and so on.
The Contains method is close to the SQL Methods. Usually, it is translated to the IN method.
Here is a simple example of this method. We get all products that belong to the "novel" and "poem" categories:
CrmDemoContext.CrmDemoDataContext db = new CrmDemoContext.CrmDemoDataContext();
List<string> list = new List<string>(){"novel", "poem"};
var query = from cat in db.ProductCategories
where list.Contains(cat.CategoryName)
select cat;
Dim db As New CrmDemoContext.CrmDemoDataContext()
Dim list As New List(Of String)
list.Add(<novel>)
list.Add(<poem>)
Dim query = From cat In db.ProductCategories _
Where list.Contains(cat.CategoryName) _
Select cat

Generated SQL
SELECT t1."CategoryID", t1."CategoryName", t1."ParentCategory"
FROM db."Product Categories" t1
WHERE t1."CategoryName" IN (:p0,:p1)

Parameter Info
Parameter Name |
DbType |
Value |
p0 |
String |
novel |
p1 |
String |
poem |
A couple of methods from the
DataLoadOptions class
allow you to use LazyLoading
The LoadWith
method is created to load child entities with the parent one.
The SQL is generated using JOIN for performance reasons. If there are several levels of
nested LoadWith calls, JOIN will be used only for the first-level queries because the
performance will suffer significantly in another case.
One of the benefits of Devart LINQ to SQL implementation
is that, unlike the Microsoft DataLoadOptions class, Devart code allows to call
LoadWith on the separate query level (not only on the Context level).
The AssociateWith method,
unlike the previous one, restricts the child collection. It allows one to set a condition,
and one will obtain only the entities that satisfy this condition when querying the child collection.
Of course, you can combine these methods successfully.
Here is a simple example where we get all product names that start from "A":
CrmDemoContext.CrmDemoDataContext db = new CrmDemoContext.CrmDemoDataContext();
var query = from product in
db.Products.LoadWith(p => p.ProductCategory).Where(p => p.ProductName.StartsWith("A"))
select new
{
Name = product.ProductName,
Price = product.Price,
Category = product.ProductCategory.CategoryName
};
Dim db As New CrmDemoContext.CrmDemoDataContext()
Dim query = From prod In _
db.Products.LoadWith(Function(p) p.ProductCategory).Where(Function(p) p.ProductName.StartsWith("A")) _
Select New With{ _
.Name = prod.ProductName, _
.Price = prod.Price, _
.Category = prod.ProductCategory.CategoryName}

Generated SQL
SELECT t1."ProductName", t1."Price", t2."CategoryName"
FROM "Products" t1
LEFT OUTER JOIN db."Product Categories" t2 ON t1."CategoryID" = t2."CategoryID"
WHERE t1."ProductName" LIKE :p0
The SQL above is generated for the Oracle database.

Parameter Info
Parameter Name |
DbType |
Value |
p0 |
String |
A% |
These examples illustrate the usage of LoadWith and AssociateWith methods. Here, we receive all products that belong to the "poem" category.
CrmDemoContext.CrmDemoDataContext db = new CrmDemoContext.CrmDemoDataContext();
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<ProductCategory>(pc => pc.Products);
dlo.AssociateWith<ProductCategory>(pc => pc.Products.Where(p=>p.Price > 10 ));
db.LoadOptions = dlo;
var query = (from cat in db.ProductCategories
where cat.CategoryName == "poem"
select cat).SingleOrDefault();
Dim db As New CrmDemoContext.CrmDemoDataContext()
Dim dlo As New DataLoadOptions
dlo.LoadWith(Of ProductCategory)(Function(pc As ProductCategory) _
pc.Products)
dlo.AssociateWith(Of ProductCategory)(Function(pc As ProductCategory) _
pc.Products.Where(Function(p As Product) p.Price > 10))
db.LoadOptions = dlo
Dim query = (From cat In db.ProductCategories _
Where cat.Categoryname = "poem" _
Select cat).SingleOrDefault()

Generated SQL
SELECT t1."CategoryID", t1."CategoryName", t1."ParentCategory", t3."ProductID", t3."ProductName",
t3."CategoryID" AS "CategoryID1", t3."UnitName", t3."UnitScale", t3."InStock", t3."Price",
t3."DiscontinuedPrice", t3."Discontinued"
FROM (
SELECT t2."CategoryID", t2."CategoryName", t2."ParentCategory"
FROM "Product Categories" t2
WHERE t2."CategoryName" = :p0
) t1
LEFT OUTER JOIN "Products" t3 ON (t1."CategoryID" = t3."CategoryID") AND (t3."Price" > :p1)
ORDER BY t1."CategoryID"
The SQL above is generated for the Oracle database.

Parameter Info
Parameter Name |
DbType |
Value |
p0 |
String |
poem |
p1 |
Double |
10 |