LINQ to SQL - Introduction

LINQ to SQL is feature intorduced in ASP.NET 3.5. Features intorduced in ASP.NET 3.5 to support LINQ to SQL

1. Automatic properties

Automatic properties provide you with a shorthand method for defining a new property. Why are automatic properties relevant to LINQ to SQL? When working with LINQ to SQL, you often use classes to represent nothing more than the list of columns you want to retrieve from the database (the shape of the data) like the select list in a SQL query. In those cases, you just want to do the minimum amount of work possible to create a list of properties, and automatic properties allow you to do this.

2. Initializers

3. Type Inference

When you take advantage of type inference, you allow the C# or VB.NET compiler to determine the type of a variable at compile time. No performance impact results from using type inference (the variable is not late bound). The compiler does all the work of figuring out the data type at compile time.

Here’s an example of how you use type inference with C#:

var message = “Hello World!”;

And here is how you would use type inference with VB.NET:

Dim message = “Hello World!”

4. Anonymous Types

Anonymous types are useful when you need a transient, fleeting type and you don’t want to do the work to create a class.

Here’s an example of creating an anonymous type in C#:

var customer = new {FirstName = “Stephen”, LastName = “Walther”};

Here’s how you would create the same anonymous type in VB.NET:

Dim customer = New With {.FirstName = “Stephen”, .LastName = “Walther”}

In a single line of code, we’ve managed to both create a new class and initialize its properties.

5. Generics

if you want to represent a list of strings, you can declare a list of strings like this (in C#):

List stuffToBuy = new List();

stuffToBuy.Add(“socks”);

stuffToBuy.Add(“beer”);

stuffToBuy.Add(“cigars”);

Here’s how you would declare the list of strings in VB.NET:

Dim stuffToBuy As New List(Of String)

stuffToBuy.Add(“socks”)

stuffToBuy.Add(“beer”)

And, by taking advantage of collection initializers, you can now declare a strongly typed list of strings in a single line like this (in C#):

List stuffToBuy2 = new List {“socks”, “beer”, “cigars”}; 

6. Lambda Expressions

Lambda expressions, another new language feature introduced with .NET Framework 3.5, provide you with an extremely terse way of defining methods.A lambda expression uses the => operator (the “goes into” operator) to separate a list of method parameters from the method body

(object sender, EventArgs e) => lblResult.Text = DateTime.Now.ToString();

 

7. Extension Methods

By taking advantage of extension methods, you can add new methods to existing classes. 

LINQ

LINQ stands for Language Integrated Query. LINQ consists of a set of new language features added to both the C# and VB.NET languages that enable you to perform queries. LINQ enables you to use SQL query–like syntax within C# or VB.NET.

You can perform a standard LINQ query against any object that implements the IEnumerable interface. An object that implements this interface is called a sequence. The C# language supports the following clauses that you can use in a query: 

From —Enables you to specify the data source and a variable for iterating over the data source (a range variable).

Where —Enables you to filter the results of a query.

Select —Enables you to specify the items included in the results of the query.

Group —Enables you to group related values by a common key.

Into —Enables you to store the results of a group or join into a temporary variable.

Orderby —Enables you to order query results in ascending or descending order.

Join —Enables you to join two data sources using a common key.

Let —Enables you to create a temporary variable to represent subquery results. 

Building a LINQ query is like building a backward SQL query. You start by specifying a from clause that indicates where you want to get your data. Next, optionally, you specify a where clause that filters your data. Finally, you specify a select clause that gives shape to your data (determines the objects and properties you want to return). 

So, the query

var results = from w in words

where w.Contains(“z”)

select w;

 

is translated into this query by the C# compiler:

var results = words.Where( w => w.Contains(“z”) ).Select( w => w ); 

The first query uses query syntax and the second query uses method syntax. The two queries are otherwise identical.

 

Here is a list of some of the more interesting and useful methods:

Aggregate() —Enables you to apply a function to every item in a sequence.

Average() —Returns the average value of every item in a sequence.

Count() —Returns the count of items from a sequence.

Distinct() —Returns distinct items from a sequence.

Max() —Returns the maximum value from a sequence.

Min() —Returns the minimum value from a sequence.

Select() —Returns certain items or properties from a sequence.

Single() —Returns a single value from a sequence.

Skip() —Enables you to skip a certain number of items in a sequence and return the remaining elements.

Take() —Enables you to return a certain number of elements from a sequence.

Where() —Enables you to filter the elements in a sequence.

 

LINQ TO SQL

LINQ to SQL enables you to perform LINQ queries against database data. Currently, you can use LINQ to SQL with Microsoft SQL Server 2000 or Microsoft SQL Server 2005

Note: To use LINQ to SQL, you need to add a reference to the System.Data.Linq.dll assembly.

you always want to specify the primary key column by using the IsPrimaryKey property. For example, if you don’t specify a primary key column, you can’t do updates against your database using LINQ. 

you almost always want to include a timestamp column in your database table and indicate the timestamp column by using the IsVersion property. If you don’t do this, LINQ to SQL will check whether the values of all the properties match the values of all the columns before performing an update command to prevent concurrency conflicts. If you specify a version property, LINQ to SQL can check the value of this single property against the database rather than all the columns.

The Column attribute supports the following properties:

AutoSync—Indicates whether the value of the property is synchronized with the value of the database column automatically. Possible values are OnInsert, Always, and None.

CanBeNull—Indicates whether the property can represent a null value.

DbType —Indicates the database column data type.

Expression—Indicates the expression used by a computed database column.

IsDbGenerated—Indicates that the value of the property is generated in the database (for example, an identity column).

IsDiscriminator—Indicates whether the property holds the discriminator value for an inheritance hierarchy.

IsPrimaryKey—Indicates whether the property represents a primary key column.

IsVersion—Indicates whether the property represents a column that represents a row version (for example, a timestamp column).

Name—Indicates the name of the database column that corresponds to the property.

Storage—Indicates a field where the value of the property is stored.

UpdateCheck—Indicates whether the property participates in optimistic concurrency comparisons. The Table attribute supports the following single property:

Name—Indicates the name of the database table that corresponds to the class.