LinqConnect provides the possibility of accessing a database via the LINQ technology. This means you can use compile-time validation, IntelliSense, and your IDE's debugging mechanisms when building and testing your queries; moreover, you don't have to think about the exact database object names (like "did we name this table 'company' or 'companies'?"), specifics of the particular SQL dialect ("OK, with SQL Server I can fetch N first rows with the TOP keyword, but how do I do this with Oracle?") and so on. Actually, you don't even need to know what SQL stands for.

DataContext and LinqConnect Tables

In SQL, you are querying tables, views or results of other queries. In LINQ, you are working with object collections. Thus, there should be a tool for linking database objects and .NET sequences. Such a tool provided by LinqConnect is the DataContext class.

DataContext fulfills all low-level operations needed to get data from the server: controls the database connection, translates LINQ queries into SQL commands, and materializes entity objects from retrieved result sets.

In particular, DataContext provides the representations of database tables and views. This is done via the Devart.Data.Linq.Table class and the GetTable method. Provided that an entity class (say, 'Order') is mapped to a database table, the GetTable<Order>() call will return a Table object representing this table in LINQ queries. For example, we can get all orders from the database in the following way:

DataContext context = new CrmDemoDataContext();
IList<Order> orders = context.GetTable<Order>().ToList();        

Dim context As DataContext = New CrmDemoDataContext()
Dim orders As IList(Of Order) = context.GetTable(Of Order)().ToList()

However, usually you don't work with 'plain' DataContext and use its descendant, which represents a database with the specific structure. These descendant classes can be generated by Entity Developer, and have wrappers for all mapped tables and views. For example, the above sample uses the CrmDemoDataContext class which is generated by Entity Developer for the CRM Demo sample database. As CrmDemoDataContext instances have a property for each CRM Demo table, the sample could be modified as:

CrmDemoDataContext context = new CrmDemoDataContext();
IList<Order> orders = context.Orders.ToList();

Dim context As CrmDemoDataContext = New CrmDemoDataContext()
Dim orders As IList(Of Order) = context.Orders.ToList()

Deferred Execution

The main thing one should keep in mind about the LinqConnect queries is that they are not immediate: no commands are performed to the database when a query is created. A query is merely a description of the command, not the result of its execution. Let's consider the following example:

var SydneyCompanies =
    from comp in context.Companies
    where comp.City == "Sydney"
    select comp;
 
foreach (Company comp in SydneyCompanies)
    Console.WriteLine(comp.CompanyName);

Dim SydneyCompanies =
    From comp In context.Companies _
    Where comp.City = "Sydney" _
    Select comp
 
For Each comp In SydneyCompanies
    Console.WriteLine(comp.CompanyName)
Next

The SydneyCompanies object here holds the information about the select statement that should be executed against the database. This select statement will be executed only when SydneyCompanies is enumerated (e.g., via the 'foreach' loop). This approach is known as deferred execution.

IQueryable Interface

All LinqConnect queries, as well as the sequences referenced in those queries, are objects that implement the IQueryable interface. This interface is the main instrument of deferred execution: it holds the query description, and executes the query when being enumerated.

Since the same interface is used for both query and the queried sequence, it is natural to expect that one query can be a data source for another one. And indeed it is so. For example, you can filter a table on one condition, and after that filter the resulting query on another condition:

var AustraliaCompanies =
    from comp in context.Companies
    where comp.Country == "Australia"
    select comp;
 
var SydneyCompanies =
    from comp in AustraliaCompanies
    where comp.City == "Sydney"
    select comp;

Dim AustraliaCompanies =
    From comp In context.Companies _
    Where comp.Country = "Australia" _
    Select comp
 
Dim SydneyCompanies =
    From comp In context.Companies _
    Where comp.City = "Sydney" _
    Select comp

The entry point for these queries is the Devart.Data.Linq.Table class described in a previous topic.