Skip()—Enables you to skip a certain number of records.Take()—Enables you to take a certain number of records.
Public partial class Products{// Select method - selects all rows from product tablepublic static IEnumerableSelect() {MydbDataContext db1 = new MydbDataContext();return db1.Products;}//Select Paged Method - handles pagingpublic static IEnumerableSelectPaged(int startrowindex, int maximumrows) {return Select().Skip(startrowindex).Take(maximumrows);}//Count Method - return countpublic static int selectcount(){MydbDataContext db1 = new MydbDataContext();return db1.Products.Count();}}
protected void Page_Load(object sender, EventArgs e){// ***Performing Like queries***// Query all rows where name starts with A//var query = db1.Products.Where(p => p.Name.StartsWith("A"));// Query all rows where name ends with p//var query = db1.Products.Where(p => p.Name.EndsWith("p"));// Query all rows where name contains p//var query = db1.Products.Where(p => p.Name.Contains("p"));// Query all rows where name lenght is 10//var query = db1.Products.Where(p => p.Name.Length == 10);// Query with SQL like method - This is more flexible as you can as many wildcards patterns as you need// For using Like method you would need to add System.Data.Linq.SqlClient;var query = db1.Products.Where(p => SqlMethods.Like(p.Name,"A%"));GridView1.DataSource = query;GridView1.DataBind();}
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.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
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
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
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
where w.Contains(“z”)
select w;
is translated into this query by the C# compiler:
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
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.
