LINQ - Tutorial - Asked By Amrita on 06-Dec-11 02:06 AM

Can you guys please provide me some good links for LINQ
Jitendra Faye replied to Amrita on 06-Dec-11 02:09 AM

Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. MSDN has a popular set of samples that demonstrate various aspects of LINQ, samples for both Visual C# and Visual Basic. These cover a range of areas, including restriction operators, grouping operators, aggregate operators, and a lot more.


Follow this link , here you will get basic examples-

http://msdn.microsoft.com/en-us/vstudio/aa336746

Riley K replied to Amrita on 06-Dec-11 02:22 AM
LINQ offers a great way to query database model where the whole logical model of the database is generated in the form of entities

You can also query on collections

Suppose to select all rows from table we use Select * from Emp

In LINQ we car assign the results to a Var type or List

  Var results=From n in dc.Employee
                        select n;



to query two tables, an example we can use is using the Northwind database.  Let's assume we want to join the Customers and Orders tables together

We would write a query like this to retrieve the OrderID, and OrderDate from the Order table, and the CustomerID and ContactName from the Customer table:

class Program
  {
  static void Main(string[] args)
  {
  using (DataClasses1DataContext db = new DataClasses1DataContext())
  {
 
 
  var query_results = from d in db.Customers
      join o in db.Orders
      on d.CustomerID equals o.CustomerID
      select new { o.OrderID, o.OrderDate, d.CustomerID, d.ContactName };
    
 
 
  }
  }
   
 
 
}



 You can refer 101 LINQ samples
code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

Regards
Suchit shah replied to Amrita on 06-Dec-11 02:47 AM

I am going to discuss the basic SQL queries and the LINQ queries similar to SQL queries, with visual representations of the LINQ queries. Before I start discussing, here is the structure of the table I am using for this article:

Users

01_userclient_table.png

UserClients

02_user_table.png

LINQ structure

03_linq_structure.png

List of LINQ Queries

Case 1: Select

The SQL query to get all users from the user table with all columns would be:

The LINQ query to do the above is:

var user = from u in Users
select u;

Here is the graphical representation the break down of the LINQ query that you wrote to get data form the user table:

04_select_user.png

Case 2: Select with Columns

This case is similar to the above but the difference is we are not selecting all the columns; instead, I select only two columns: FirstName and LastName. The SQL query to select all rows with only two columns is:

Select FirstName, LastName from [User]

Now the LINQ query:

from u in Users
select new
{
    u.FirstName,
    u.LastName
};

So you need to create a new anonymous type to get only the FirstName and LastName form the user object. The graphical representation of this query is:

05_select_user.png

Case 3: Filter Selected Data

For Integer Data

To apply filter on the selected data, we use the WHERE clause with the column value, so the SQL query would be:

Select firstname,LastName from [User] where id = 3

In LINQ, we need to use the WHERE clause as well, so the query would be:

from u in Users
where u.Id ==3
select new
{
   u.FirstName,
   u.LastName
}

This graphical representation shows the breakdown of the LINQ query related to filtering data:

06_select_filter.png

For String Data

In order to filter strings, we use LIKE:

SELECT  [Id], [FirstName], [LastName], [Email], 
        [DisplayName], [Address1], [Address2], [Password], [Role]
FROM [User]
WHERE [Email] LIKE '%pranay%'

or

SELECT  [Id], [FirstName], [LastName], [Email], 
        [DisplayName], [Address1], [Address2], [Password], [Role]
FROM [User]
WHERE [Email] LIKE 'pranay%'

To apply the filter on the string datatype, you need to use the Contains or StartWith function available in C# so that it generates the same result as the above SQL queries:

from u in Users
where u.Email.Contains ("pranay")
select u

or

from u in Users
where u.Email.StartsWith ("pranay")
select u

The graphical representation of the LINQ query filtering using a string field:

07_select_filter_like.png

Case 4: Joining Two Tables

Inner Join

Inner join is how we can get common records between two tables, i.e., related records form the table(s). Here is a SQL query for an inner join:

SELECT [User].[Id], [FirstName], [LastName], [UserId], [MobileNo]
FROM [User]
INNER JOIN
[UserClients]
ON [User].[id] = [UserId]

LINQ does the same using the Join keyword with Equals to join two collections:

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
select new {
  u.Id,
  u.FirstName,
  u.LastName,
  uc.MobileNo,
  uc.imeiNO,
  uc.Id,
};

The graphical representation of inner join for the LINQ query is as shown below. As you can see, the User connection gets added to UserClients based on the condition in On.. Equals:

Outer Join

Outer Join is how we get common records between two tables, i.e., related records form a table; all records from the left table and not found in the right table gets a null value. A SQL query for an outer join would look like:

SELECT [t0].[Id], [FirstName], [LastName], 
       [UserId] AS [UserId], [MobileNo] AS [MobileNo]
FROM [User] AS [t0]
LEFT OUTER JOIN [UserClients]  ON ([t0].[id]) = [UserId]

In LINQ, to achieve outer join, you need to use the DefaultIfEmpty() function like:

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
 u.Id,
 u.FirstName,
 u.LastName,
 m.UserId,
 m.MobileNo
};

The graphical representation of the outer join LINQ query is same as that for the inner join, but there is one more step for the function DefaultIfEmpty():

Case 5: Ordering Data

In SQL to order fetched data, we need to apply the ORDER BY clause with the ASC or DESC keyword, so the SQL query would be:

--Ascending
Select * from [User] order by firstName

or:

--Descending
Select * from [User] order by firstName desc

LINQ uses ORDER BY combined with the ASCENDING and DESCENDING keywords so that the final LINQ query would be:

//Ascending
var user = from u in Users
orderby u.FirstName
 select new
{
   u.FirstName,
   u.LastName 
}

or

//Descending
var user = from u in Users
orderby u.FirstName descending
select new
{
   u.FirstName,
   u.LastName 
};

Here is the graphical breakdown of the LINQ query:

Case 6: Grouping Data

Groups of selected data allow to perform aggregate function likes SUM, MAX, MIN, COUNT etc. To group data in SQL, you need to use the GROUP BY clause, but the thing to remember is you need to include the select list column in your group by clause or you will get a syntax error:

SELECT COUNT(*) AS [test], [UserId]
FROM [UserClients]
GROUP BY [UserId]

LINQ uses Group ... By to group data, so the query looks like:

var user =  from u in UserClients
group u by u.UserId into c
select new
{
 t1 = c.Key,
 tcount = c.Count()
};

Note: After you apply group by on a collection of objects in LINQ, your group by column gets converted to a key column which you can see in the above LINQ query, UserId. The graphical breakdown of the Group...By LINQ query is:


Case 7: Filter Data Using IN and NOT IN Clauses

Most developers who start working on LINQ queries get confused when they have to write IN and NOT IN queries using LINQ. Here is the SQL query:

//IN
SELECT [Id], [UserId], [IMEINo]
FROM [UserClients]
WHERE [UserId] IN (3, 4)

or:

//NOT IN
SELECT [Id], [UserId],  [IMEINo]
FROM [UserClients]
WHERE [UserId] IN (3, 4)

As you see above, the query uses IN and NOT IN clauses to filter from a list of records. The LINQ query to achieve this task makes use of the Contains function of C#, which does filtering of records from a list of records:

//IN
int[] chosenOnes = { 3, 4 };
var user = from u in UserClients
where chosenOnes.Contains(u.UserId.Value)
select new  { u.id,u.userid, u.ImeiNo};

or:

//NOT IN
int[] chosenOnes = { 3, 4 };
var user = from u in UserClients
where !chosenOnes.Contains(u.UserId.Value)
select u;

Note: IN and NOT IN use the same function in the LINQ query, but it just use a ! (not) symbol for it. Here is the graphical representation:

Case 8: Filtering Data by Row Numbers

I am now going to show how you can filter your data by row numbersthat you assigned to your record(s). To filter data in SQL Server (SQL Server 2005), we use the RowNumber function and then we use <=, >=, or BETWEEN. Here is the SQL query:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [id]) AS [ROW_NUMBER],
           [id], [FirstName], [LastName], [Email], [DisplayName], 
           [Address1], [Address2], [Password], [Role]
    FROM [User] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 11 AND 20
ORDER BY [t1].[ROW_NUMBER]

In the above query, as you can see, the ROW_NUMBER() function assigns a number to the records, and we can use that number in an outer query to filter data between 11 to 20. LINQ makes use of two functions:

  • Skip: Bypasses a specified number of elements in a sequence and then returns the remaining elements
  • Take: Returns a specified number of contiguous elements from the start of a sequence

The LINQ query is something like:

var users = from u in Users
select u;

var filterUsers= users.OrderBy (p => p.Id).Skip (10).Take(10);

In the above code, we are selecting data first and than we are applying Skip and Take to get data between the 11 to 20 records. Here is the graphical representation;

Case 9: SQL ISNULL function

Solution 1

We can use the ternary operator as in the below example and MobileNo = "N/A" for the null values:

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
UserId = m.UserId,
MobileNo = (m.MobileNo == null) ? "N/A" : m.MobileNo
};

Solution 2

Use the special Coalescing operator operator (??) as in the below example, and MobileNo = "N/A" for the null values:

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
UserId = m.UserId,
MobileNo = m.MobileNo == null ?? "N/A" 
};
Suchit shah replied to Amrita on 06-Dec-11 02:51 AM

LINQ is one of the most important features in .NET Framework 3.5 (Visual Studio 2008). It's the new way to mapping database tables to classes, and as we know, we call this O/R Mapping. An article on how to write LINQ code quickly is always welcome for beginners, and I think that reading samples is the best way to learn a new technique.

These are samples created while I was learning and using LINQ, and I want to share them now. Hope they will be helpful. I will use Northwind database as a sample, which you can download from the link at the top of this article.

I recommend that you read 101 LINQ Samples if you would like to learn more.
Also there are good stuff on net
http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

http://msdn.microsoft.com/en-us/library/bb386913.aspx