Methods

You can generate SQL statements with the help of methods that are support <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 USA that have orders latest than 1998 year. The results of 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

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>. It is means that you can enhance results of the Query execution.

The ExecuteCommand method does not return any resultset, however it allows to execute any SQL command directly, without any translation.

The query below returns 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 for a set of the native SQL functions, among them are Like, Coalesce, Between and so on.

The Contains method is close to the SQL Methods. It is translated to the IN method usually. 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

The couple of methods from the DataLoadOptions class allows you to use LazyLoading

The LoadWith method is created for loading child entities with the parent one. The SQL is generated using JOIN for performance reasons. In case, there are several levels of nested LoadWith calls the JOIN will be used only for the first-level queries because the performance will suffer greatly in other case. One of the benefits of Devart LINQ to SQL implementation is that unlike 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 to set a condition and one will obtain only the entities that satisfy this condition when quering the child collection.

Of course, you can combine these methods successfully. Here is a simple example where we get all products name of what is started 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 Oracle database.

Parameter Info

These examples illustrate 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 Oracle database.

Parameter Info