Learning Center
Plans & pricing Sign in
Sign Out

Linq (PowerPoint)


									Language independent Query
            Data Manipulation
• Data is in different formats
  – In memory objects , Relational Data , Object
    Relational data , XML & etc.

• Different Querying techniques are used
  – Collections Loops
  – XML Xquery , XPath
  – Relational data T-SQL , PL SQL
      .NET Support for Querying
 SQL and XQUERY are not parts of .NET language
   No compile-time checking
   No IntelliSense

 Queries return typeless DataReaders and DataTables
   Column names are not part of the object
   Must cast column value to correct data type and size

 Persisting objects to tables requires application code
   Write manually
                     What is LINQ?
 LINQ stands for the .NET Language Integrated Query and is
  a new framework extension in .NET 3.5.
 As the name suggests, using LINQ users can write data
  access queries natively in .NET languages.
 Use the same query syntax for all types of data
   Relational , hierarchical , or flat
   Persistent or in-memory

 LINQ is the foundation for many native substructures in
  .NET that enable you to query disparate data sources.
LINQ Architecture
            LINQ Syntax

from id in source
{ from id in source | where condition }
[ orderby ordering, ordering, … ]
select expr | group expr by key
[ into id query ]
         Standard Query Operators
• These work similarly to their SQL counterparts
   –   Select
   –   Where
   –   OrderBy/ThenBy
   –   OrderByDescending/ThenByDescending
   –   GroupBy
   –   Count
   –   Sum/Min/Max/Average
       Positional Query Operators
• First/FirstOrDefault
   – Returns first element satisfying a predicate
• ElementAt
   – Returns element at a numeric position
• Take/Skip
   – Returns all elements before/after a numeric position
• TakeWhile/SkipUntil
   – Returns elements while predicate satisfied
• Reverse
   – Returns elements in reverse order
• Range
   – Returns subset of elements in a range
     Set Based Query Operators
 Combine two sets of elements
   Union
    ▪ Returns all distinct elements in both sets
   Intersection
    ▪ Returns only elements belonging to both sets
   Except
    ▪ Returns elements in Set A but not in Set B
   Repeat
    ▪ Returns multiple copies of a set of elements
   Distinct
    ▪ Removes duplicate elements
                 Other Operators
• Join Operators
   – Join operator ~ INNER join
   – GroupJoin ~ LEFT OUTER join
• Ordering Operators
   –   OrderBy
   –   OrderByDescending
   –   ThenBy
   –   ThenByDescending
• Conversion Operators
   – ToArray()
   – ToList()
               LINQ to Objects
 LINQ to Objects means that we can use LINQ to query
  objects in a collection.
 We can access the in-memory data structures using
  LINQ. Lists, arrays, and dictionaries are some collection
  objects that can be queried using LINQ.
 If we don't use LINQ, we have to use the looping
  method to filter the values in a collection.
 However, using LINQ we can directly query collections
  and filter the required values without using any looping.
 LINQ provides powerful filtering, ordering, and grouping
  capabilities that requires minimum coding.
                LINQ to Objects
 Example
   int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };

   var lowNums =
     from n in numbers
     where n < 5
     select n;

   foreach (var num in lowNums ) {
          LINQ to SQL(DLINQ)
 LINQ to SQL does not execute queries; the
  database server does
  LINQ to SQL queries are compiled to expression
   trees rather than IL
  At runtime LINQ to SQL parses the expression
   trees, constructs equivalent T-SQL statements,
   and sends them to the server
   ▪ Statements are not executed as SQL/CLR
  Thus .NET expressions that cannot translate to T-
   SQL are disallowed
           LINQ to SQL(DLINQ)
 Data Context
  Like an xxxConnection in ADO.NET, the
   DataContext class binds table objects to a
   database connection
  By making the tables members of a DataContext
   subclass, the binding takes place transparently
  Data Context is generated by the DBML designer
  The datacontext knows everything
   ▪ Contains the identity map
   ▪ Contains the change tracking service
          LINQ to SQL(DLINQ)

 A sample query
 CustomerDataContext dc = new
 var results = from c in dc.Customers where c.City ==
  "London" select c;

   foreach (Customer c in results)
 LINQ to SQL – Data Manipulation
 Inserting
      InsertOnSubmit(entity)
      InsertAllOnSubmit(entityCollection)
      SubmitChanges()
 Updating
      SubmitChanges()
 Deleting
      DeleteOnSubmit(entity)
      DeleteAllOnSubmit(entityCollection)
      SubmitChanges()
                        LINQ to SQL
 After SubmitChanges() is called
  1.   Modified objects in the object graph are identified.
       1. New objects to be inserted
       2. Retrieved objects to be deleted
       3. Retrieved objects whose persistent members have changed
  2.   Insert, update and delete statements are constructed with
       modified current values in the set clause and original values in
       the where clause
  3.   Database connection is opened and a transaction is started.
  4.   If there are any errors, the transaction is rolled back.
  5.   If the transaction succeeds, the internal state of the DataContext
       is changed accordingly.
              LINQ to DataSet
 The ADO.NET DataSet provides a disconnected data
  source environment for the applications.
 DataSet has the flexibility to handle data locally in
  cache memory where the application resides.
 The application can continue working with DataSet as
  it is disconnected from the source and not dependent
  on the availability of a data source.
 DataSet maintains information about the changes
  made to data so that updates can be tracked and sent
  back to the database as soon as the data source is
  available or reconnected.
 DataSet is a collection of DataTable objects.
            LINQ to DataSet
• .NET 3.0 has support for LINQ over DataSet.
• There are many operators that LINQ provides
  for querying and manipulating data in
• DataSet exposes DataTable as enumerations
  of DataRow objects.
• The LINQ query operators execute queries on
  the enumeration of DataRow objects.

To top