LinqConnect Documentation
Methods
Methods
LinqConnect Documentation
Methods
support@devart.com

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
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>. 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

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
Parameter Info

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

The SQL above is generated for the Oracle database.

Parameter Info

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

The SQL above is generated for the Oracle database.

Parameter Info