Search This Blog

Saturday, December 19, 2009

STANDARD QUERY OPERATORS



LINQ examples, Keywords, faq's

.

In Visual Studio you can write LINQ queries in Visual Basic or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any collection of objects that supports IEnumerable or the generic IEnumerable<(Of <(T>)>) interface. LINQ support for the ADO.NET Entity Framework is also planned, and LINQ providers are being written by third parties for many Web services and other database implementations.

• SQL Server databases: LINQ to SQL
• XML documents: LINQ to XML
• ADO.NET Datasets: LINQ to DataSet
• .NET collections, files, strings and so on: LINQ to Objects

Query Keywords (C#)

from
Specifies a data source and a range variable (similar to an iteration variable).

where
Filters source elements based on one or more Boolean expressions separated by logical AND and OR operators ( && or || ).

select
Specifies the type and shape that the elements in the returned sequence will have when the query is executed.

group
Groups query results according to a specified key value.

into
Provides an identifier that can serve as a reference to the results of a join, group or select clause.

orderby
Sorts query results in ascending or descending order based on the default comparer for the element type.

join
Joins two data sources based on an equality comparison between two specified matching criteria.

let
Introduces a range variable to store sub-expression results in a query expression.

in
Contextual keyword in a join clause.

on
Contextual keyword in a join clause.

equals
Contextual keyword in a join clause.

by
Contextual keyword in a group clause.

ascending
Contextual keyword in an orderby clause.

descending
Contextual keyword in an orderby clause.


Generation of c# class based on Database tables, Procs etc by using following command(in VS Command prompt)

sqlmetal /server:ServerName /database:DBName /namespace:LinqEnabledWebSite /code:ClasssName.cs /language:csharp /user:uid /password:pwd /sprocs


LINQ Queries


// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");
var companyNameQuery = from cust in nw.Customers where cust.City == "London" select cust.CompanyName;

LINQ FAQ’s

Q. What is LINQ?

LINQ is a set of language features introduced in .NET 3.5 (shipping Visual Studio 2008) that allow you to write queries similar to SQL like statements in Visual Basic or C# programs (other language support is available, but these are the main ones). LINQ knows the data-source you are trying to query (SQL Server, object collection, or another source) and executes the query expression returning the result in the form you ask.


Q. What is LINQ to SQL?
LINQ to SQL is a specific LINQ implementation for SQL Server. LINQ to SQL converts LINQ queries written in C# or Visual Basic into dynamic SQL, execute it on the server and marshal the results in return. LINQ to SQL also supports making changes to the returned objects and applying those updates back to the database. These updates can be applied by the LINQ framework using dynamic SQL or Stored Procedures. See: LINQ to SQL main page, and LINQ to SQL 5 minute overview.



Q. How do I enable LINQ to SQL in my code?
To use LINQ to SQL in Visual Studio 2008 -

1) add a reference to the System.Data.Linq.dll (if it is grayed out, go to your project properties and set the target framework to .NET 3.5)
2) add the following using statement at the top of your class file: using System.Data.Linq;



Q. What versions of C# Supports LINQ?
C#3.0 that ships in the .NET 3.5 framework as part of Visual Studio 2008 supports LINQ. It was possible to run a LINQ preview in Visual Studio 2005 mid 2006, but as the beta releases of Visual Studio 2008 became available this support was dropped.


Q. Do I need to know LINQ syntax to program?
No. Developing using LINQ is completely optional. LINQ does remove a lot of the traditional ADO.NET complexity.


Q. What .NET languages currently support LINQ?
Currently C# and Visual Basic shipping with the .NET Framework 3.5 (in Visual Studio 2008) have specific language features and debugger support for LINQ.


Q. Am I exposed to SQL Injection attacks?
No. LINQ to SQL passes all data to the database via SQL parameters. So, although the SQL query is composed dynamically, the values are substitued server side through parameters safeguarding against the most common cause of SQL injection attacks. Security is a big topic though, so stay diligant!


Q. Does LINQ to SQL support Stored Procedures?
Yes. LINQ to SQL supports calling Stored Procedures by directly wrapping them in strongly typed C# or VB method signatures. It also supports you specifying a specific Insert, Update and Delete stored procedures to make the appropriate changes to the database based on onject updates made in C# and VB.


Q. How can I support Optional Parameters when calling a Stored Procedure through LINQ to SQL?
See this article on the subject: Stored Procedure Optional Parameters using LINQ to SQL


Q. Can LINQ to SQL perform batch updates and deletes? Or does it always do one row update at a time?
By default, LINQ to SQL will perform a single row update when datacontext.SubmitChanges() is called after many rows have been deleted, or modified.

LINQ Queries ( Joins )

join clause

The join clause is useful for associating elements from different source sequences that have no direct relationship in the object model. The only requirement is that the elements in each source share some value that can be compared for equality. For example, a food distributor might have a list of suppliers of a certain product, and a list of buyers. A join clause can be used, for example, to create a list of the suppliers and buyers of that product who are all in the same specified region.

A join clause takes two source sequences as input. The elements in each sequence must either be or contain a property that can be compared to a corresponding property in the other sequence. The join clause compares the specified keys for equality by using the special equals keyword. All joins performed by the join clause are equijoins. The shape of the output of a join clause depends on the specific type of join you are performing. The following are three most common join types:

• Inner join
• Group join
• Left outer join

Inner Join

The following example shows a simple inner equijoin. This query produces a flat sequence of “product name / category” pairs. The same category string will appear in multiple elements. If an element from categories has no matching products, that category will not appear in the results.

var innerJoinQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID
select new { ProductName = prod.Name, Category = category.Name };
//produces flat sequence


Group Join

A join clause with an into expression is called a group join.

var innerGroupJoinQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
select new { CategoryName = category.Name, Products = prodGroup };

A group join produces a hierarchical result sequence, which associates elements in the left source sequence with one or more matching elements in the right side source sequence. A group join has no equivalent in relational terms; it is essentially a sequence of object arrays.

If no elements from the right source sequence are found to match an element in the left source, the join clause will produce an empty array for that item. Therefore, the group join is still basically an inner-equijoin except that the result sequence is organized into groups.

If you just select the results of a group join, you can access the items, but you cannot identify the key that they match on. Therefore, it is generally more useful to select the results of the group join into a new type that also has the key name, as shown in the previous example.

You can also, of course, use the result of a group join as the generator of another subquery:

var innerGroupJoinQuery2 =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
from prod2 in prodGroup
where prod2.UnitPrice > 2.50M
select prod2;


Left Outer Join

In a left outer join, all the elements in the left source sequence are returned, even if no matching elements are in the right sequence. To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. You can use null as the default value for any reference type, or you can specify a user-defined default type. In the following example, a user-defined default type is shown:

var leftOuterJoinQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
from item in prodGroup.DefaultIfEmpty(new Product{Name = String.Empty, CategoryID = 0})
select new { CatName = category.Name, ProdName = item.Name };


The equals operator
A join clause performs an equijoin. In other words, you can only base matches on the equality of two keys. Other types of comparisons such as "greater than" or "not equals" are not supported. To make clear that all joins are equijoins, the join clause uses the equals keyword instead of the == operator. The equals keyword can only be used in a join clause and it differs from the == operator in one important way. With equals, the left key consumes the outer source sequence, and the right key consumes the inner source. The outer source is only in scope on the left side of equals and the inner source sequence is only in scope on the right side.

Popular Posts