LINQ Notes

November 20, 2011

11-1: Some of the Visual Basic clauses for working with LINQ

Clause Description
From Identifies the source of data for the query.
Where Provides a condition that specifies which elements are retrieved from the data source.
Order By Indicates how the elements that are returned by the query are sorted.
Select Specifies the content of the returned elements.
Let Performs a calculation and assigns an alias to the result that can then be used within the query.
Join Combines data from two data sources.
Aggregate Lets you include aggregate functions, such as Sum, Average, and Count, in a query.
Group By Groups the returned elements and, optionally, applies aggregate functions to each group.

Advantages of using LINQ

  • Makes it easier for you to query a data source by integrating the query language with Visual Basic.
  • Makes it easier to develop applications that query a data source by providing IntelliSense, compile-time syntax checking, and debugging support.
  • Makes it easier for you to query different types of data sources because you use the same basic syntax for each type.
  • Makes it easier for you to use objects to work with relational data sources by providing designer tools that create object-relational mappings.

Description

  • Language-Integrated Query (LINQ) provides a set of query operators that are implemented using extension methods. These methods are static members of the Enumerable and Queryable classes.
  • You can work with LINQ by calling the extension methods directly or by using Visual Basic clauses that are converted to calls to the methods at compile time.
  • A query that calls LINQ methods directly is called a method-based query. A query that uses Visual Basic clauses is called a query expression. You use a method-based query or query expression to identify the data you want to retrieve from a data source.
  • To use LINQ with a data source, the data source must implement the IEnumerable(Of T) interface or another interface that implements IEnumerable(Of T) such as IQueryable(Of T). A data source that implements one of these interfaces is called an enumerable type.

Finally, if you’re working with a relational data source such as a SQL Server database, you can use designer tools provided by Visual Studio to develop an object-relational mapping. Then, you can use LINQ to query the objects defined by this mapping, and the query will be converted to the form required by the data source. This can make it significantly easier to work with relational data sources.

Visual Basic 2008 features that support LINQ

Visual Basic 2008 introduced a variety of new features to support LINQ. These features are listed in the first table in figure 11-2. Most of these features can be used outside of LINQ. For the most part, though, you’ll use these features when you code LINQ queries. You’ll learn more about these features later in this chapter.

LINQ providers included with Visual Basic 2008

Figure 11-2 also presents the LINQ providers that are included with Visual Basic 2008. As I’ve already mentioned, you’ll learn how to use the LINQ to Objects provider in this chapter to query generic lists. Then, in chapter 12, you’ll learn how to use the LINQ to DataSet provider to query the data in a dataset. In chapter 13, you’ll learn how to use the Object Relational Designer to create an object model for use with the LINQ to SQL provider, and you’ll learn how to use that model to query a SQL Server database. Then, in chapter 14, you’ll learn how to update the data in a SQL Server database using LINQ to SQL with an object model. In chapter 16, you’ll learn how to use the LINQ to XML provider to load XML from a file, query and modify the XML in your application, and save the updated XML to a file. You’ll also learn how to create XML documents and elements from scratch or from other documents and elements.

Another provider you can use with Visual Basic is LINQ to Entities. This provider works with an Entity Data Model that maps the data in a relational database to the objects used by your application. In chapter 17, you’ll learn how to use the Entity Data Model Designer to create an Entity Data Model. Then, in chapter 18, you’ll learn how to use LINQ to Entities to work with this model.

Visual Basic 2008 features that support LINQ

Feature Description
Query expressions Expressions with a syntax similar to SQL that can be used to retrieve and update data. Converted into method calls at compile time.
Implicitly typed variables Variables whose types are inferred from the data that’s assigned to them. Used frequently in query expressions and with query variables.
Anonymous types An unnamed type that’s created temporarily when a query returns selected fields from the data source.
Object initializers Used with query expressions that return anonymous types to assign values to the properties of the anonymous type.
Extension methods Provide for adding methods to a data type from outside the definition of the data type.

LINQ providers included with Visual Basic 2008

Provider Description
LINQ to Objects Lets you query in-memory data structures such as generic lists and arrays. LINQ to DataSet Lets you query the data in a typed or untyped dataset. See chapter 12 for more information.
LINQ to SQL Lets you query and update the data in a SQL Server database. See chapters 13 and 14 for more information.
LINQ to XML Lets you query and modify in-memory XML or the XML stored in a file. See chapter 16 for more information.
LINQ to Entities Lets you query and update the data in any relational database. See chapter 18 for more information.

LINQ Notes 1

November 23, 2009

Book name: programming microsoft linq

1.LINQ to Objects

2.LINQ to ADO.NET

LINQ to SQL

LINQ to DataSet

LINQ to Entities (see note below)

3.LINQ to XML

4. Parallel LINQ (PLINQ

 

INTO clause
—————
 You can use the into keyword to store the results of a

select, group, or join statement in a temporary variable

var categoriesAndProducts =
    from   c in categories
    join   p in products on c.IdCategory equals p.IdCategory
    select new {
        c.IdCategory,
        CategoryName = c.Name,
        Product = p.Description
    };

foreach (var item in categoriesAndProducts) {
    Console.WriteLine(item);

 

2.Let Clause
———-
The let clause allows you to store the result of a

subexpression in a variable that can be used somewhere else

in the query. This clause is useful when you need to reuse

the same expression many times in the same query and you do

not want to define it every single time you use it. Using

the let clause, you can define a new range variable for that

expression and reference it within the query. Once assigned,

a range variable defined by a let clause cannot be changed.

However, if the range variable holds a queryable type, it

can be queried. In Listing 2-21, you can see an example of

this clause applied to select the same product categories

with the count of their products, sorted by the counter

itself.

Listing 2-21: A C# 3.0 sample of usage of the let clause
 

var categoriesByProductsNumberQuery =
    from    c in categories
    join    p in products on c.IdCategory equals

p.IdCategory
        into productsByCategory
    let     ProductsCount = productsByCategory.Count()
    orderby ProductsCount
    select  new { c.IdCategory, ProductsCount};

foreach (var item in categoriesByProductsNumberQuery) {
    Console.WriteLine(item);
}
Aggregate, which is useful for applying an aggregate

function to a data source. It can be used to begin a new

query instead of a From clause.

Distinct, which can be used to eliminate duplicate values in

query results.

Skip, which can be used to skip the first N elements of a

query result.

Skip While, which can be used to skip the first elements of

a query result that verify a predicate that is provided.

Take, which can be used to take the first N elements of a

query result.

Take While, which can be used to take the first elements of

a query result that verify a predicate that is provided.
——

LINQ to Objects

1. projection operators:
select and select many

2.orderby and orderbydescending

3. Thenby and thenbydescending

4. reverse operator

5. Join

GroupJoin
In cases in which you need to define something similar to a

LEFT OUTER JOIN or a RIGHT OUTER JOIN, you need to use the

GroupJoin operator

6. Set Operators

Distinct
Union,INtersect, Except

7. Aggregate Operators
Count , LongCount
LongCount variations simply return a long instead of an

integer.

SUM,MIn,MAx,
Aggregate

8.Generation Operators
Repeat , Range.empty

9. Quantifier Operators
Any,All,CONTAINS

10.Partitioning Operators
Take (Top in Sql)
TakeWhile
Skip, SkipWhile

11. Element Operators
First
FirstOrDefault : If you need to find the first element only

if it exists, without any exception in case of failure, you

can use the FirstOrDefault method. This method works like

First, but if there are no elements that verify the

predicate or if the source sequence is empty, it returns a

default value:

LAst
LastOrDefault

Single
SingleORDefault
ElementAt and ElementAtOrDefault
DefaultEmpty

 

—-
1. SubmitChanges
in-memory changes(dataset) to the database by calling

SubmitChanges:

Product product = db.Products.Single(p => p.ProductID ==

42);
product.UnitPrice *= 1.05M;
db.SubmitChanges();

2.
Association between entities
 

[Table(Name=”Orders”)]
public class Order {
    [Column(IsPrimaryKey=true)] public int OrderID;
    [Column] private string CustomerID;
    [Column] public DateTime? OrderDate;

    [Association(Storage=”_Customer”, ThisKey=”CustomerID”,

IsForeignKey=true)]
    public Customer Customer {
        get { return this._Customer.Entity; }
        set { this._Customer.Entity = value; }
    }

    private EntityRef<Customer> _Customer;

}
3.DataLoadOptions, AssociateWith
[Table(Name=”Customers”)]
public class Customer {
    [Column(IsPrimaryKey=true)] public string CustomerID;
    [Column] public string CompanyName;
    [Column] public string Country;

    [Association(OtherKey=”CustomerID”)]
    public EntitySet<Order> Orders;
}

4.
static void QueryManipulation() {
    DataContext db = new DataContext( ConnectionString );
    Table<Customer> Customers = db.GetTable<Customer>();
    db.Log = Console.Out;

    // All Customers
    var query =
        from   c in Customers
        select new {c.CompanyName, c.State, c.Country };

    DisplayTop( query, 10 );

    // User interaction add a filter
    // to the previous query
    // Customers from USA
    query =
        from   c in query
        where  c.Country == “USA”
        select c;

    DisplayTop( query, 10 );

    // User interaction add another
    // filter to the previous query
    // Customers from WA, USA
    query =
        from   c in query
        where  c.State == “WA”
        select c;

    DisplayTop( query, 10 );
}

static void DisplayTop<T>( IQueryable<T> query, int rows ) {
    foreach( var row in query.Take(rows)){
        Console.WriteLine( row );
    }
}
5.How to call sps in linq

Stored Procedures:

ExecuteMethodCall,
 
Consider the Customers by City stored procedure:

CREATE PROCEDURE [dbo].[Customers By City]( @param1

NVARCHAR(20) )
AS BEGIN
    SET NOCOUNT ON;
    SELECT CustomerID, ContactName, CompanyName, City
    FROM   Customers AS c
    WHERE  c.City = @param1
END

You can define a method decorated with a Function attribute

that calls the stored procedure through the

ExecuteMethodCall method of the DataContext class. In

Listing 4-12, we define CustomersByCity as a member of a

class derived from DataContext.

Listing 4-12: Stored procedure declaration
 

class SampleDb : DataContext {
    // …
    [Function(Name = “Customers by City”, IsComposable =

false)]
    public ISingleResult<CustomerInfo>

CustomersByCity(string param1) {
        IExecuteResult executeResult =
            this.ExecuteMethodCall(
                     this,
                     (MethodInfo)

(MethodInfo.GetCurrentMethod()),
                     param1);
        ISingleResult<CustomerInfo> result =
            (ISingleResult<CustomerInfo>)

executeResult.ReturnValue;
        return result;
    }
}
b.
Stored procedure with multiple results

class SampleDb : DataContext {
    // …
    [Function(Name = “TwoCustomerGroups”, IsComposable =

false)]
    [ResultType(typeof(CustomerInfo))]
    [ResultType(typeof(CustomerShortInfo))]
    public IMultipleResults TwoCustomerGroups() {
        IExecuteResult executeResult =
                 this.ExecuteMethodCall(
                     this,
                     (MethodInfo)

(MethodInfo.GetCurrentMethod()));
        IMultipleResults result =
            (IMultipleResults) executeResult.ReturnValue;
        return result;
    }
}
6. How to call a UDF in LINQ
Function attribute is used for both stored procedures and

UDFs, the IsComposable argument is set to true to map a UDF,

and is set to false to map a stored procedure.
a. Scalar valued udf
class SampleDb : DataContext {
    // …
    [Function(Name = “dbo.MinUnitPriceByCategory”,

IsComposable = true)]
    public decimal? MinUnitPriceByCategory(int? categoryID)

{
        IExecuteResult executeResult =
            this.ExecuteMethodCall(
                this,
                ((MethodInfo)

(MethodInfo.GetCurrentMethod())),
                categoryID);
        decimal? result = (decimal?)

executeResult.ReturnValue;
        return result;
    }
}
b. Table Valued UDF
A table-valued UDF always sets IsComposable to true in

Function arguments, but it calls the

DataContext.CreateMethodCallQuery instead of

DataContext.ExecuteMethodCall.

Listing 4-15: Table-valued user-defined function
 

class SampleDb : DataContext {
    // …
    [Function(Name = “dbo.CustomersByCountry”, IsComposable

= true)]
    public IQueryable<Customer> CustomersByCountry(string

country) {
        return this.CreateMethodCallQuery<Customer>(
            this,
            ((MethodInfo) (MethodInfo.GetCurrentMethod())),
            country);
    }
}
8. CompiledQueryClass
If you need to repeat the same query many times, eventually

with different argument values, you might be worried about

the multiple query construction. Several databases, such as

SQL Server, try to parameterize received SQL queries

automatically to optimize the compilation of the query

execution plan. However, the program that sends a

parameterized query to SQL Server will get better

performance because SQL Server does not spend time to

analyze it if the query is similar to another one already

processed. LINQ already does a fine job of query

optimization, but each time that the same query tree is

evaluated, the LINQ to SQL engine parses the query tree to

build the equivalent SQL code. You can optimize this

behavior by using the CompiledQuery class.
9.
Direct Queries
Sometimes you might need access to database SQL features

that are not available with LINQ. For example, imagine that

you want to use Common Table Expressions (CTEs) or the PIVOT

command with SQL Server. LINQ does not have an explicit

constructor to do that, even if its SQL Server provider

could use these features to optimize some queries. Listing

4-22 shows how you can use the ExecuteQuery<T> method of the

DataContext class to send a query directly to the database.

The T in ExecuteQuery<T> is an entity class that represents

a returned row.

Listing 4-22: Direct query
 

var query = db.ExecuteQuery<EmployeeInfo>( @”
    WITH EmployeeHierarchy (EmployeeID, LastName, FirstName,
                            ReportsTo, HierarchyLevel) AS
     ( SELECT EmployeeID,LastName, FirstName,
              ReportsTo, 1 as HierarchyLevel
       FROM   Employees
       WHERE  ReportsTo IS NULL

       UNION ALL

       SELECT      e.EmployeeID, e.LastName, e.FirstName,
                   e.ReportsTo, eh.HierarchyLevel + 1 AS

HierarchyLevel
       FROM        Employees e
       INNER JOIN  EmployeeHierarchy eh
               ON  e.ReportsTo = eh.EmployeeID

    )
    SELECT   *
    FROM     EmployeeHierarchy
    ORDER BY HierarchyLevel, LastName, FirstName” );
10.Deferred Loading of Entities
We have seen that using graph traversal to query data is a

very comfortable way to proceed. However, sometimes you

might want to stop the LINQ to SQL provider from

automatically deciding what entities have to be read from

the database and when, thereby taking control over that part

of the process. You can do this by using the

DeferredLoadingEnabled and LoadOptions properties of the

DataContext class.

The code in Listing 4-24 makes the same QueryOrder call

under three different conditions, driven by the code in the

DemoDeferredLoading method.

Listing 4-24: Deferred loading of entities
 

public static void DemoDeferredLoading() {
    Console.Write(“DeferredLoadingEnabled=true  “);
    DemoDeferredLoading(true);
    Console.Write(“DeferredLoadingEnabled=false “);
    DemoDeferredLoading(false);
    Console.Write(“Using LoadOptions            “);
    DemoLoadWith();
}

static void DemoDeferredLoading(bool deferredLoadingEnabled)

{
    nwDataContext db = new

nwDataContext(Connections.ConnectionString);
    db.DeferredLoadingEnabled = deferredLoadingEnabled;

    QueryOrder(db);

}

static void DemoLoadWith() {
    nwDataContext db = new

nwDataContext(Connections.ConnectionString);
    db.DeferredLoadingEnabled = false;

    DataLoadOptions loadOptions = new DataLoadOptions();
    loadOptions.LoadWith<Order>(o => o.Order_Details);
    db.LoadOptions = loadOptions;

    QueryOrder(db);

}

static void QueryOrder(nwDataContext db) {
    var order = db.Orders.Single((o) => o.OrderID == 10251);
    var orderValue = order.Order_Details.Sum(od =>

od.Quantity * od.UnitPrice);
    Console.WriteLine(orderValue);
}

 
 
11.Deferred Loading of Properties
LINQ to SQL provides a deferred loading mechanism that acts

at the property level, loading data only when that property

is accessed for the first time. You can use this mechanism

when you need to load a large number of entities in memory,

which usually requires space to accommodate all the

properties of the class that correspond to table columns of

the database. If a certain field is very large and is not

always accessed for every entity, you can delay the loading

of that property.

To request the deferred loading of a property, you simply

use the Link<T> type to declare the storage variable for the

table column, as you can see in Listing 4-25.

Listing 4-25: Deferred loading of properties
 

[Table(Name = “Customers”)]
public class DelayCustomer {
    private Link<string> _Address;

    [Column(IsPrimaryKey = true)] public string CustomerID;
    [Column] public string CompanyName;
    [Column] public string Country;

    [Column(Storage = “_Address”)]
    public string Address {
        get { return _Address.Value; }
        set { _Address.Value = value; }
    }
}

public static class DeferredLoading {
    public static void DelayLoadProperty() {
        DataContext db = new

DataContext(Connections.ConnectionString);
        Table<DelayCustomer> Customers =

db.GetTable<DelayCustomer>();
        db.Log = Console.Out;

        var query =
            from   c in Customers
            where  c.Country == “Italy”
            select c;

        foreach (var row in query) {
            Console.WriteLine(
                “{0} – {1}”,
                row.CompanyName,
                row.Address);
        }
    }
}
12.Read-Only DataContext Access
If you need to access data exclusively in a read-only way,

you might want to improve performance by disabling a

DataContext service that supports data modification:

DataContext db = new DataContext( ConnectionString );
db.ObjectTrackingEnabled = false;
var query = …

The ObjectTrackingEnabled property controls the change

tracking service .By default, ObjectTrackingEnabled is set

to true.

13.
Limitations of LINQ to SQL
LINQ to SQL has some limitations when converting a LINQ

query into a corresponding SQL statement. For this reason,

some valid LINQ to Objects statements are not supported in

LINQ to SQL. In this section, we cover the most important

operators that cannot be used in aLINQ to SQL query.

 More Info  A complete list of unsupported methods and types

is available in the product documentation, “Data Types and

Functions (LINQ to SQL),” which is available at

http://msdn2.microsoft.com/en-us/library/bb386970.aspx.
 

Aggregate Operators
The general-purpose Aggregate operator is not supported.

However, specialized aggregate operators such as Count,

LongCount, Sum, Min, Max, and Average are fully supported.

Any aggregate operator other than Count and LongCount

requires particular care to avoid an exception if the result

is null. If the entity class has a member of a non-nullable

type and you make an aggregation on it, a null result (for

example when no rows are aggregated) throws an exception.

You need to cast the aggregated value to a nullable type

before considering it in the aggregation function in order

to avoid that exception. You can see an example of the

necessary cast in Listing 4-26.

Listing 4-26: Null handling with aggregate operators
 

decimal? totalFreight =
   (from   o in Orders
    where  o.CustomerID == “NOTEXIST”
    select o).Min( o => (decimal?) o.Freight );

 
 

This cast is necessary only if you declared the Freight

property with decimal:

[Table(Name = “Orders”)]
public class Order {
    [Column] public decimal Freight;
}

Another solution is declaring Freight as a nullable type

using decimal?, but it is not a good idea to have different

nullable settings between entities and corresponding tables

in the database.

 More Info  You can find a more complete discussion about

this issue in this post written by Ian Griffiths:

http://www.interact-sw.co.uk/iangblog/2007/09/10/linq-aggreg

ates.
 

Partitioning Operators
The TakeWhile and SkipWhile operators are not supported.

Take and Skip operators are supported, but be careful with

Skip because the generated SQL query could be complex and

not very efficient when there are a large number of rows to

skip, particularly if the target database is SQL Server

2000.

Element Operators
The following operators are not supported: ElementAt,

ElementAtOrDefault, Last, and LastOrDefault.

String Methods
Many of the .NET String type methods are supported in LINQ

to SQL because there is a corresponding method in T-SQL.

However, there is no support for methods that are

cultureaware (those that receive arguments of type

CultureInfo, StringComparison, and IFormatProvider) and for

methods that receive or return a char array.

DateTime Methods
There are differences between the DateTime type in .NET and

the DATETIME and SMALLDATETIME types in SQL Server. The

range of values and the precision is greater in .NET than in

SQL Server, allowing for a correct representation of SQL

Server types in .NET, but not the opposite. Moreover,

DATETIME in SQL Server does not have the notion of a time

zone, thus it cannot be supported by LINQ to SQL. Finally,

some .NET DateTime methods are not supported, mainly because

of the lack of a corresponding function in T-SQL.

Unsupported SQL Functionalities
LINQ to SQL does not have syntax to make use of the SQL LIKE

operator and STDDEV aggregation.

 

——-
Querying a DataTable with LINQ

Field<t>

 You can use the Field<T> accessor method instead of using a

direct cast on the result of the standard DataRow accessor

(such as o[“OrderDate”]). The query shown in Listing 7-6

gets the orders that show a date of 1998 or later.

Listing 7-6: Querying a DataTable with LINQ
 

DataSet ds = LoadDataSetUsingDataAdapter();
DataTable orders = ds.Tables[“Orders”];

var query =
    from    o in orders.AsEnumerable()
    where   o.Field<DateTime>(“OrderDate”).Year >= 1998
    orderby o.Field<DateTime>(“OrderDate”) descending
    select  o;
———-
A DataView can also be created from a LINQ query on a

DataTable by using the AsDataView extension method
——-

var order = oldCustomer.Orders.Single( o => o.OrderID ==

10248 );
————
Tools of LINQ: SQLMetal.exe

————–

1. CRUD and CUD Operations
The term CRUD means Create, Read, Update, and Delete. These

are the fundamental operations provided by a storage system,

and they correspond to the SQL statements INSERT, SELECT,

UPDATE, and DELETE, respectively. Using LINQ to SQL, read

operations are typically performed in an indirect way, by

executing LINQ queries or by accessing LINQ entities through

their relationships without a direct call of the SELECT SQL

statement. For this reason, in LINQ to SQL documentation you

will find another acronym
2,changeSet.Updates
changeSet.Deletes
changeSet.Inserts
public static void DumpChanges(ChangeSet changeSet) {
    if (changeSet.Deletes.Count > 0) {
        Console.WriteLine(“** DELETES **”);
        foreach (var del in changeSet.Deletes){
            Console.WriteLine(Dump(del));
        }
    }
    if (changeSet.Updates.Count > 0) {
        Console.WriteLine(“** UPDATES **”);
        foreach (var upd in changeSet.Updates){
            Console.WriteLine(Dump(upd));
        }
    }
    if (changeSet.Inserts.Count > 0) {
        Console.WriteLine(“** INSERTS **”);
        foreach (var ins in changeSet.Inserts){
            Console.WriteLine(Dump(ins));
        }

    }
    Console.WriteLine(changeSet);
}

public static string Dump(this object data) {
    if (data is Customer) {
        Customer customer = (Customer) data;
        return String.Format(
            “CustomerID={0}, CompanyName={1}”,
            customer.CustomerID, customer.CompanyName);
    }
    else {
        throw new NotSupportedException(
            String.Format(
                “Dump is not supported on {0}”,
                data.GetType().FullName));
    }
}
3.
InsertOnSubmit
DeleteOnSubmit

var newCustomer = new Customer {
                        CustomerID = “DLEAP”,
                        CompanyName = “DevLeap”,
                        Country = “Italy” };
db.Customers.InsertOnSubmit(newCustomer);

var oldDetail = db.Order_Details.Single(
              od => od.OrderID == 10422
                    && od.ProductID == 26);
db.Order_Details.DeleteOnSubmit(oldDetail);
4.
Cascading Deletes and Updates

if you need to remove a row, you always have to do this in a

direct way, calling the DeleteOnSubmit method on the

corresponding Table collection. When you remove an object,

you need to be sure that there are no more entities

referencing it; otherwise, an exception will be thrown when

SubmitChanges is called, because the SQL DELETE statement

will violate some referential integrity constraint (such as

FOREIGN KEY being declared in the database). You can unbind

related entities by setting their foreign key to NULL, but

this might throw an exception if constraints do not allow

NULL values. Another option is to remove the child objects

from an object you want to remove by calling the

DeleteOnSubmit method on them. You can do that by leveraging

the DeleteAllOnSubmit method:

var order = db.Orders.Single(o => o.OrderID == 10248);
db.Orders.DeleteOnSubmit(order);
db.Order_Details.DeleteAllOnSubmit(order.Order_Details);

 

b.
Another cascading operation that is possible on a relational

database is the cascading update. For example, changing the

primary key of a Customer changes all the foreign keys in

related entities referring to that Customer. However, LINQ

to SQL does not allow changing the primary key of an entity.

You need to create a new Customer, change the references

from the old Customer to the new one, and finally remove the

old Customer. This operation is shown in Listing 5-2.

Listing 5-2: Replace a Customer on existing orders
 

var oldCustomer = db.Customers.Single(c => c.CustomerID ==

“FRANS”);
Customer newCustomer = new Customer();
newCustomer.CustomerID = “CHNGE”;
newCustomer.Address = oldCustomer.Address;
newCustomer.City = oldCustomer.City;
newCustomer.CompanyName = oldCustomer.CompanyName;
newCustomer.ContactName = oldCustomer.ContactName;
newCustomer.ContactTitle = oldCustomer.ContactTitle;
newCustomer.Country = oldCustomer.Country;
newCustomer.Fax = oldCustomer.Fax;
newCustomer.Orders = oldCustomer.Orders;
newCustomer.Phone = oldCustomer.Phone;
newCustomer.PostalCode = oldCustomer.PostalCode;
newCustomer.Region = oldCustomer.Region;
10.
Entity States

Untracked This is not a true state. It identifies an object

that is not tracked by LINQ to SQL. A newly created object

is always in this state until it is not attached to a

DataContext. Because this state is the relationship of an

entity in a given DataContext, an entity created as a result

of a query of a DataContext instance is Untracked by other

DataContext instances. Finally, after deserialization, an

entity instance is always Untracked.

Unchanged The initial state of an object retrieved by using

the current DataContext.

PossiblyModified An object attached to a DataContext. In

Figure 5-1, the two states Unchanged and PossiblyModified

are represented by the same box (state).

ToBeInserted An object not retrieved by using the current

DataContext. This is a newly created object that has been

added with an InsertOnSubmit to a Table<T> collection, or

that has been added to an EntitySet<T> of an existing entity

instance.

ToBeUpdated An object that has been modified since it was

retrieved. This state is set by a change to any property of

an entity retrieved by using the current DataContext.

ToBeDeleted An object marked for deletion by calling

DeleteOnSubmit.

Deleted An object that has been deleted in the database. The

entity instance for this object still exists in memory with

this particular state. If you want to use the same primary

key of a Deleted entity, you need to define a new entity in

a different DataContext.
12.Entity Synchronization
After you write an entity to the database by calling

SubmitChanges, it is possible that changes are made directly

on the database to some column of the table. Identities,

triggers, and time stamps are all cases in which the actual

value written on the database cannot be known in advance by

the entity itself and needs to be read from the database

after the SubmitChanges call. If you use an entity after the

call to SubmitChanges, you probably need to update these

values by reading the entity from the database. LINQ to SQL

helps automate this process by providing the AutoSync

parameter to the Column attribute that decorates entity

properties. For each column, this parameter can have one of

the following values provided by the

System.Data.Linq.Mapping.AutoSync enumeration:

Default There is an automatic handling of the entity update,

based on known metadata of the column itself. For example,

an IsDbGenerated column will be read after an Insert

operation, and an IsVersion column will be updated after any

Update or Insert operation.

Always The column is always updated from the database after

any SubmitChanges call.

Never The column is never updated from the database.

OnInsert The column is updated from the database after the

SubmitChanges call that inserts the entity.

OnUpdate The column is updated from the database after the

SubmitChanges call that updates the entity.
——-
Generating a DBML File from a Database
To generate a DBML file, you need to specify the /dbml

option, followed by the filename to create. The syntax to

specify the database to use depends on the type of the

database. For example, a standard SQL Server database can be

specified with the /server and /database options:

sqlmetal /server:localhost /database:Northwind

/dbml:northwind.dbml

Windows authentication is used by default. If you want to

use SQL Server authentication, you can use the /user and

/password options. Alternatively, you can use the /conn

option, which cannot be used with /server, /database, /user,

or /password. The following command line that uses /conn is

equivalent to the previous one, which used /server and

/database:

sqlmetal

/conn:”Server=localhost;Database=Northwind;Integrated

Security=yes”
    /dbml:northwind.dbml

If you have the Northwind MDF file in the current directory

and are using SQL Server Express, the same result can be

obtained by using the following line, which makes use of the

input file parameter:

sqlmetal /dbml:northwind.dbml Northwnd.mdf

Similarly, an SDF file handled by SQL Server Compact 3.5 can

be specified as in the following line:

sqlmetal /dbml:northwind.dbml Northwind.sdf

By default, only tables are extracted from a database. You

can also extract views, user-defined functions, and stored

procedures by using /views, /functions, and /sprocs,

respectively, as shown here:

sqlmetal /server:localhost /database:Northwind /views

/functions /sprocs
    /dbml:northwind.dbml
 Note  Remember that database views are treated like tables

by LINQ to SQL.
 

Generating Source Code and a Mapping File from a Database
To generate an entity’s source code, you need to specify the

/code option, followed by the filename to create. The

language is inferred by the filename extension, using CS for

C# and VB for Visual Basic. However, you can explicitly

specify a language by using /language:csharp or /language:vb

to get C# or Visual Basic code, respectively. The syntax to

specify the database to use depends on the type of the

database. A description of this syntax can be found in the

preceding section, “Generating a DBML File from a Database.”

For example, the following line generates C# source code for

entities extracted from the Northwind database:

sqlmetal /server:localhost /database:Northwind

/code:Northwind.cs

If you want all the tables and the views in Visual Basic,

you can use the following command line:

sqlmetal /server:localhost /database:Northwind /views

/code:Northwind.vb

Optionally, you can add the generation of an XML mapping

file by using the /map option, as in the following command

line:

sqlmetal /server:localhost /database:Northwind

/code:Northwind.cs /map:Northwind.xml
 Important  When the XML mapping file is requested, the

generated source code does not contain any attribute-based

mapping.
 

There are a few options to control how the entity classes

are generated. The /namespace option controls the namespace

of the generated code. (By default, there is no namespace.)

The /context option specifies the name of the class

inherited from DataContext that will be generated. (By

default, it is derived from the database name.) The

/entitybase option allows you to define the base class of

the generated entity classes. (By default, there is no base

class.) For example, the following command line generates

all the entities in a LinqBook namespace, deriving them from

the DevLeap.LinqBase base class:

sqlmetal /server:localhost /database:Northwind

/namespace:LinqBook
    /entitybase:DevLeap.LinqBase /code:Northwind.cs
 Note  If you specify a base class, you have to be sure that

the class exists when the generated source code is compiled.

It is a good practice to specify the full name of the base

class.
 

If you want to generate serializable classes, you can

specify /serialization:unidirectional in the command line,

as in the following example:

sqlmetal /server:localhost /database:Northwind

/serialization:unidirectional
    /code:Northwind.cs

 

———————————-