Search This Blog

Saturday, December 19, 2009

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.

No comments:

Popular Posts