LinqConnect Documentation
In This Topic
    Querying Data
    In This Topic
    Querying Data
    LinqConnect Documentation
    Querying Data
    [email protected]

    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:


    C#csharpCopy Code
    DataContext context = new CrmDemoDataContext();
IList<Order> orders = context.GetTable<Order>().ToList();
    Visual BasicCopy Code
    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:


    C#csharpCopy Code
    CrmDemoDataContext context = new CrmDemoDataContext();
IList<Order> orders = context.Orders.ToList();
    Visual BasicCopy Code
    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:


    C#csharpCopy Code
    var SydneyCompanies =
    from comp in context.Companies
    where comp.City == "Sydney"
    select comp;
 
foreach (Company comp in SydneyCompanies)
    Console.WriteLine(comp.CompanyName);
    Visual BasicCopy Code
    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:


    C#csharpCopy Code
    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;
    Visual BasicCopy Code
    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.

     

     

    © 2010 - 2024 Devart. All Rights Reserved.

    [email protected]