Basic LINQ Query Operations (C#)
Visual Studio 2010
This topic gives a brief introduction to LINQ query expressions and some of the typical kinds of operations that you perform in a query. More detailed information is in the following topics:
If you already are familiar with a query language such as SQL or XQuery, you can skip most of this topic. Read about the "from clause" in the next section to learn about the order of clauses in LINQ query expressions. |
NoteIn a LINQ query, the first step is to specify the data source. In C# as in most programming languages a variable must be declared before it can be used. In a LINQ query, the from clause comes first in order to introduce the data source (customers) and the range variable (cust).
//queryAllCustomers is an IEnumerable<Customer> var queryAllCustomers = from cust in customers select cust;
The range variable is like the iteration variable in a foreach loop except that no actual iteration occurs in a query expression. When the query is executed, the range variable will serve as a reference to each successive element incustomers. Because the compiler can infer the type of cust, you do not have to specify it explicitly. Additional range variables can be introduced by a let clause. For more information, see let clause (C# Reference).
For non-generic data sources such as ArrayList, the range variable must be explicitly typed. For more information, see How to: Query an ArrayList with LINQ and from clause (C# Reference). |
Probably the most common query operation is to apply a filter in the form of a Boolean expression. The filter causes the query to return only those elements for which the expression is true. The result is produced by using the whereclause. The filter in effect specifies which elements to exclude from the source sequence. In the following example, only those customers who have an address in London are returned.
var queryLondonCustomers = from cust in customers where cust.City == "London" select cust;
You can use the familiar C# logical AND and OR operators to apply as many filter expressions as necessary in thewhere clause. For example, to return only customers from "London" AND whose name is "Devon" you would write the following code:
where cust.City=="London" && cust.Name == "Devon"
To return customers from London or Paris, you would write the following code:
where cust.City == "London" || cust.City == "Paris"
For more information, see where clause (C# Reference).
Often it is convenient to sort the returned data. The orderby clause will cause the elements in the returned sequence to be sorted according to the default comparer for the type being sorted. For example, the following query can be extended to sort the results based on the Name property. Because Name is a string, the default comparer performs an alphabetical sort from A to Z.
var queryLondonCustomers3 = from cust in customers where cust.City == "London" orderby cust.Name ascending select cust;
To order the results in reverse order, from Z to A, use the orderby…descending clause.
For more information, see orderby clause (C# Reference).
The group clause enables you to group your results based on a key that you specify. For example you could specify that the results should be grouped by the City so that all customers from London or Paris are in individual groups. In this case, cust.City is the key.
The types are explicit in the following examples to illustrate the concept. You could also use implicit typing forcustQuery, group, and customer to let the compiler determine the exact type. |
// queryCustomersByCity is an IEnumerable<IGrouping<string, Customer>> var queryCustomersByCity = from cust in customers group cust by cust.City; // customerGroup is an IGrouping<string, Customer> foreach (var customerGroup in queryCustomersByCity) { Console.WriteLine(customerGroup.Key); foreach (Customer customer in customerGroup) { Console.WriteLine(" {0}", customer.Name); } }
When you end a query with a group clause, your results take the form of a list of lists. Each element in the list is an object that has a Key member and a list of elements that are grouped under that key. When you iterate over a query that produces a sequence of groups, you must use a nested foreach loop. The outer loop iterates over each group, and the inner loop iterates over each group's members.
If you must refer to the results of a group operation, you can use the into keyword to create an identifier that can be queried further. The following query returns only those groups that contain more than two customers:
// custQuery is an IEnumerable<IGrouping<string, Customer>> var custQuery = from cust in customers group cust by cust.City into custGroup where custGroup.Count() > 2 orderby custGroup.Key select custGroup;
For more information, see group clause (C# Reference).
Join operations create associations between sequences that are not explicitly modeled in the data sources. For example you can perform a join to find all the customers and distributors who have the same location. In LINQ the join clause always works against object collections instead of database tables directly.
var innerJoinQuery = from cust in customers join dist in distributors on cust.City equals dist.City select new { CustomerName = cust.Name, DistributorName = dist.Name };
In LINQ you do not have to use join as often as you do in SQL because foreign keys in LINQ are represented in the object model as properties that hold a collection of items. For example, a Customer object contains a collection ofOrder objects. Rather than performing a join, you access the orders by using dot notation:
Order the result and return the first one.
var callStats = (from c in database.CallLogs
group c by c.RemoteParty into d
select new
{
RemoteParty = d.Key,
TotalDuration = d.Sum(x => x.Duration)
});
callStats = callStats.OrderByDescending( a => a.TotalDuration )
.FirstOrDefault();How to simulate IN Clause in LINQ queries
Hi,When working with the LINQ queries for SQL one of the common queries that we need to run is the select query with IN clause. In SQL IN clause is used to provide more than one value to match in the where clause.Something like the query belowSelect * from Table
where column1 in (‘Value1’, ‘Value2’, ‘Value3’)To do a similar query in LINQ we can use
var list = from t in table
where t.column1 = Value1’
And t.column1 = Value2’
t.column1 = Value3’
Select t But what if the number of values are not known is design time, coming from another list etc? In that case you need to write the LINQ query like this.List<string> names = new List<string>();
names.Add("Value1");
names.Add("Value2");
names.Add("Value3");
names.Add("Value4"); var list = from t in table
where names.contains(t.column1)
select tYou can pass the value of a full table also by querying the table first and then fetching all the values of the table in the list.Yours shibashish mohantyLINQ Query Expressions (C# Programming Guide)
Visual Studio 2010
Language-Integrated Query (LINQ) is the name for a set of technologies based on the integration of query capabilities directly into the C# language (also in Visual Basic and potentially any other .NET language). With LINQ, a query is now a first-class language construct, just like classes, methods, events and so on.
For a developer who writes queries, the most visible "language-integrated" part of LINQ is the query expression. Query expressions are written in a declarative query syntax introduced in C# 3.0. By using query syntax, you can perform even complex filtering, ordering, and grouping operations on data sources with a minimum of code. You use the same basic query expression patterns to query and transform data in SQL databases, ADO.NET Datasets, XML documents and streams, and .NET collections.The following example shows the complete query operation. The complete operation includes creating a data source, defining the query expression, and executing the query in a foreach statement.class LINQQueryExpressions
{
static void Main()
{
// Specify the data source.
int[] scores = new int[] { 97, 92, 81, 60 };
// Define the query expression.
IEnumerable<int> scoreQuery =
from score in scores
where score > 80
select score;
// Execute the query.
foreach (int i in scoreQuery)
{
Console.Write(i + " ");
}
}
}
// Output: 97 92 81
LINQ group by query with projection
This post falls into the category of being posted for my own future reference - hopefully you'll find it useful too (and apologies to regular readers for the recent silence on theJoyOfCode.com - I've been away on holiday).I was recently working on a project and had the need to write a LINQ query (this is good old L2O, or Linq-to-objects) that grouped some objects together. If this were SQL (which it isn't) the table might have looked like this:
Name Dept StartDate Bill Bloggs HR 1976-06-11 Barry Williams HR 2000-08-02 Terry Dickins IT 2007-11-20 Andy Robinson IT 2006-10-01
An I wanted to group by department, selecting the earliest StartDate. The T-SQL to do so may have looked like this:SELECT Dept, MIN(StartDate)
FROM Employees
GROUP BY Dept
That was easy enough. Now, what if my Employees table was really a Listof Employee objects, each with a Name, Dept and StartDate property?
var groups = from emp in employees
group emp by emp.Dept into g
select new { Dept = g.Key, StartDate = g.Min(e => e.DateTime) };
Hmmm.... not so easy peasy. One of the reasons I'm a big fan of LINQ is that I think it often makes your intent clearer. I'm really not sure that that is the case here but perhaps that is just because we're not as used to it yet.Let's look at the query and dissect it line by line.
var groups = from emp in employees
That's easy enough - we're creating an anonymous type called groups by selecting from the employees collection of objects.
group emp by emp.Dept into g
Now we're saying we want to create groups of employees by bunching together employees with the same Dept property. This effectively creates a new 'g' variable that will hold the groups in question. The g variable is another IEnumerable, as you'd expect, but it also offers a Key property, which in this case is the Dept property of its employees.
select new { Dept = g.Key, StartDate = g.Min(e => e.StartDate) };
Here we create our 'projection' - a new anonymous type with a Dept property (the key of the group) and a StartDate property where we use a Lambda on g to select the minimum start date of that particular group.
C# random letter generator
Generating letters at random can be useful. Here is some very simple code to achieve this;
01020304050607080910111213141516 /// <summary> /// Gets a letter from the English alphabet at random /// </summary> public static class RandomLetter { private const string _characters = "abcdefghijklmnopqrstuvwxyz"; private static readonly Random _random = new Random(Environment.TickCount); /// <summary> /// Gets the next random letter /// </summary> public static char Next() { return Char.Parse(_characters.Substring(_random.Next(26), 1)); } }
Here is how to use the code;
12 for (int i = 0; i < 10000; i++)Console.WriteLine(RandomLetter.Next());
Also, this code would be easy to translate to other language, such as Spanish for example.Enjoy
Introducing Linq
Linq is short for Language Integrated Query. If you are used to using SQL to query databases, you are going to have something of a head start with Linq, since they have many ideas in common. Before we dig into Linq itself, let's step back and look at what makes SQL different from C#.
Imagine we have a list of orders. For this example, we will imagine they are stored in memory, but they could be in a file on disk too. We want to get a list of the costs of all orders that were placed by the customer identified by the number 84. If we set about implementing this in C# before version 3 and a range of other popular languages, we would probably write something like (assuming C# syntax for familiarity):List<double> Found = new List<double>();
foreach (Order o in Orders)
if (o.CustomerID == 84)
Found.Add(o.Cost);Here we are describing how to achieve the result we want by breaking the task into a series of instructions. This approach, which is very familiar to us, is called imperative programming. It relies on us to pick a good algorithm and not make any mistakes in the implementation of it; for more complex tasks, the algorithm is more complex and our chances of implementing it correctly decrease.
If we had the orders stored in a table in a database and we used SQL to query it, we would write something like:SELECT Cost FROM Orders WHERE CustomerID = 84
Here we have not specified an algorithm, or how to get the data. We have just declared what we want and left the computer to work out how to do it. This is known as declarative or logic programming.
Linq brings declarative programming features into imperative languages. It is not language specific, and has been implemented in the Orcas version of VB.Net amongst other languages. In this series we are focusing on C# 3.0, but the principles will carry over to other languages.
Understanding A Simple Linq Query
Let's jump straight into a code example. First, we'll create an Order class, then make a few instances of it in a List as our test data. With that done, we'll use Linq to get the costs of all orders for customer 84.class Order
{
private int _OrderID;
private int _CustomerID;
private double _Cost;
public int OrderID
{
get { return _OrderID; }
set { _OrderID = value; }
}
public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
public double Cost
{
get { return _Cost; }
set { _Cost = value; }
}
}
class Program
{
static void Main(string[] args)
{
// Set up some test orders.
var Orders = new List<Order> {
new Order {
OrderID = 1,
CustomerID = 84,
Cost = 159.12
},
new Order {
OrderID = 2,
CustomerID = 7,
Cost = 18.50
},
new Order {
OrderID = 3,
CustomerID = 84,
Cost = 2.89
}
};
// Linq query.
var Found = from o in Orders
where o.CustomerID == 84
select o.Cost;
// Display results.
foreach (var Result in Found)
Console.WriteLine("Cost: " + Result.ToString());
}
}The output of running this program is:Cost: 159.12
Cost: 2.89
Let's walk through the Main method. First, we use collection and object initializers to create a list of Order objects that we can run our query over. Next comes the query - the new bit. We declare the variable Found and request that its type be inferred for us by using the "var" keyword.
We then run across a new C# 3.0 keyword: "from".from o in Orders
This is the keyword that always starts a query. You can read it a little bit like a "foreach": it takes a collection of some kind after the "in" keyword and makes what is to the left of the "in" keyword refer to a single element of the collection. Unlike "foreach", we do not have to write a type.
Following this is another new keyword: "where".where o.CustomerID == 84
This introduces a filter, allowing us to pick only some of the objects from the Orders collection. The "from" made the identifier "o" refer to a single item from the collection, and we write the condition in terms of this. If you type this query into the IDE yourself, you will notice that it has worked out that "o" is an Order and intellisense works as expected.
The final new keyword is "select".select o.Cost
This comes at the end of the query and is a little like a "return" statement: it states what we want to appear in the collection holding the results of the query. As well as primitive types (such as int), you can instantiate any object you like here. In this case, we will end up with Found being a List<int>, though.
You may be thinking at this point, "hey, this looks like SQL but kind of backwards and twisted about a bit". That is a pretty good summary. I suspect many who have written a lot of SQL will find the "select comes last" a little grating at first; the other important thing to remember is that all of the conditions are to be expressed in C# syntax, not SQL syntax. That means "==" for equality testing, rather than "=" in SQL. Thankfully, in most cases that mistake will lead to a compile time error anyway.
A Few More Simple Queries
We may wish our query to return not only the Cost, but also the OrderID for each result that it finds. To do this we take advantage of anonymous types.var Found = from o in Orders
where o.CustomerID == 84
select new { OrderID = o.OrderID, Cost = o.Cost };Here we have defined an anonymous type that holds an OrderID and a Cost. This is where we start to see the power and flexibility that they offer; without them we would need to write custom classes for every possible set of results we wanted. Remembering the projection syntax, we can shorten this to:var Found = from o in Orders
where o.CustomerID == 84
select new { o.OrderID, o.Cost };And obtain the same result. Note that you can perform whatever computation you wish inside the anonymous type initializer. For example, we may wish to return the Cost of the order with an additional sales tax of 10% added on to it.var Found = from o in Orders
where o.CustomerID == 84
select new {
o.OrderID,
o.Cost,
CostWithTax = o.Cost * 1.1
};Conditions can be more complex too, and are built up in the usual C# way, just as you would do in an "if" statement. Here we apply an extra condition that we only want to see orders valued over a hundred pounds.var Found = from o in Orders
where o.CustomerID == 84 && o.Cost > 100
select new {
o.OrderID,
o.Cost,
CostWithTax = o.Cost * 1.1
};Ordering
It is possible to sort the results based upon a field or the result of a computation involving one or more fields. This is achieved by using the new "orderby" keyword.var Found = from o in Orders
where o.CustomerID == 84
orderby o.Cost ascending
select new { o.OrderID, o.Cost };After the "orderby" keyword, we write the expression that the objects will be sorted on. In this case, it is a single field. Notice this is different from SQL, where there are two words: "ORDER BY". I have added the keyword "ascending" at the end, though this is actually the default. The result is that we now get the orders in order of increasing cost, cheapest to most expensive. To get most expensive first, we would have used the "descending" keyword.
While I said earlier that the ordering condition is based on fields in the objects involved in the query, it actually doesn't have to be. Here's a way to get the results in a random order.Random R = new Random();
var Found = from o in Orders
where o.CustomerID == 84
orderby R.Next()
select new { OrderID = o.OrderID, Cost = o.Cost };Joins
So far we have just had one type of objects to run our query over. However, real life is usually more complex than this. For this example, let's introduce another class named Customer.class Customer
{
private int _CustomerID;
private string _Name;
private string _Email;
public int CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
public string Name
{
get { return _Name; }
set { _Name = value; }
}
public string Email
{
get { return _Email; }
set { _Email = value; }
}
}In the Main method, we will also instantiate a handful of Customer objects and place them in a list.var Customers = new List<Customer> {
new Customer {
CustomerID = 7,
Name = "Emma",
Email = "emz0r@worreva.com"
},
new Customer {
CustomerID = 84,
Name = "Pedro",
Email = "pedro@cerveza.es"
},
new Customer {
CustomerID = 102,
Name = "Vladimir",
Email = "vladimir@pivo.ru"
}
};We would like to produce a list featuring all orders, stating the ID and cost of the order along with the name of the customer. To do this we need to involve both the List of orders and the List of customers in our query. This is achieved using the "join" keyword. Let's replace our query and output code with the following.// Query.
var Found = from o in Orders
join c in Customers on o.CustomerID equals c.CustomerID
select new { c.Name, o.OrderID, o.Cost };
// Display results.
foreach (var Result in Found)
Console.WriteLine(Result.Name + " spent " +
Result.Cost.ToString() + " in order " +
Result.OrderID.ToString());The output of running this program is:Pedro spent 159.12 in order 1
Emma spent 18.5 in order 2
Pedro spent 2.89 in order 3
We use the "join" keyword to indicate that we want to refer to another collection in our query. We then once again use the "in" keyword to declare an identifier that will refer to a single item in the collection; in this case it has been named "c". Finally, we need to specify how the two collections are related. This is achieved using the "on ... equals ..." syntax, where we name a field from each of the collections. In this case, we have stated that the CustomerID of an Order maps to the CustomerID of a Customer.
When the query is evaluated, an object in the Customers collection is located to match each object in the Orders collection. Note that if there were many customers with the same ID, there may be more than one matching Customer object per Order object. In this case, we get extra results. For example, change Vladimir to also have an OrderID of 84. The output of the program would then be:Pedro spent 159.12 in order 1
Vladimir spent 159.12 in order 1
Emma spent 18.5 in order 2
Pedro spent 2.89 in order 3
Vladimir spent 2.89 in order 3
Notice that Vladimir never featured in the results before, since he had not ordered anything.
Getting All Permutations With Multiple "from"s
It is possible to write a query that gets every combination of the objects from two collections. This is achieved by using the "from" keyword multiple times.var Found = from o in Orders
from c in Customers
select new { c.Name, o.OrderID, o.Cost };Earlier I suggested that you could think of "from" as being a little bit like a "foreach". You can also think of multiple uses of "from" a bit like nested "foreach" loops; we are going to get every possible combination of the objects from the two collections. Therefore, the output will be:Emma spent 159.12 in order 1
Pedro spent 159.12 in order 1
Vladimir spent 159.12 in order 1
Emma spent 18.5 in order 2
Pedro spent 18.5 in order 2
Vladimir spent 18.5 in order 2
Emma spent 2.89 in order 3
Pedro spent 2.89 in order 3
Vladimir spent 2.89 in order 3
Which is not especially useful. You may have spotted that you could have used "where" in conjunction with the two "from"s to get the same result as the join:var Found = from o in Orders
from c in Customers
where o.CustomerID == c.CustomerID
select new { c.Name, o.OrderID, o.Cost };However, don't do this, since it computes all of the possible combinations before the "where" clause, which goes on to throw most of them away. This is a waste of memory and computation. A join, on the other hand, never produces them in the first place.
Grouping
Another operations that you may wish to perform is categorizing objects that have the same value in a given field. For example, we might want to categorize orders by CustomerID. The result we expect back is a list of groups, where each group has a key (in this case, the CustomerID) and a list of matching objects. Here's the code to do the query and output the results.// Group orders by customer.
var OrdersByCustomer = from o in Orders
group o by o.CustomerID;
// Iterate over the groups.
foreach (var Cust in OrdersByCustomer)
{
// About the customer...
Console.WriteLine("Customer with ID " + Cust.Key.ToString() +
" ordered " + Cust.Count().ToString() + " items.");
// And what they ordered.
foreach (var Item in Cust)
Console.WriteLine(" ID: " + Item.OrderID.ToString() +
" Cost: " + Item.Cost.ToString());
}The output that it produces is as follows:Customer with ID 84 ordered 2 items.
ID: 1 Cost: 159.12
ID: 3 Cost: 2.89
Customer with ID 7 ordered 1 items.
ID: 2 Cost: 18.5This query looks somewhat different to the others that we have seen so far in that it does not end with a "select". The first line is the same as we're used to. The second introduces the new "group" and "by" keywords. After the "by" we name the field that we are going to group the objects by. Before the "by" we put what we would like to see in the resulting per-group collections. In this case, we write "o" so as to get the entire object. If we had only been interested in the Cost field, however, we could have written:// Group orders by customer.
var OrdersByCustomer = from o in Orders
group o.Cost by o.CustomerID;
// Iterate over the groups.
foreach (var Cust in OrdersByCustomer)
{
// About the customer...
Console.WriteLine("Customer with ID " + Cust.Key.ToString() +
" ordered " + Cust.Count().ToString() + " items.");
// And the costs of what they ordered.
foreach (var Cost in Cust)
Console.WriteLine(" Cost: " + Cost.ToString());
}Which produces the output:Customer with ID 84 ordered 2 items.
Cost: 159.12
Cost: 2.89
Customer with ID 7 ordered 1 items.
Cost: 18.5You are not restricted to just a single field or the object itself; you could, for example, instantiate an anonymous type there instead.
Query Continuations
At this point you might be wondering if you can follow a "group ... by ..." with a "select". The answer is yes, but not directly. Both "group ... by ..." and "select" are special in so far as they produce a result. You must terminate a Linq query with one or the other. If you try to do something like:var CheapOrders = from o in Orders
where o.Cost < 10;Then it will lead to a compilation error. Since both "select" and "group ... by ..." terminate a query, you need a way of taking the results and using them as the input to another query. This is called a query continuation, and the keyword for this is "into".
In the following example we take the result of grouping orders by customer and then use a select to return an anonymous type containing the CustomerID and the number of orders that the customer has placed.var OrderCounts = from o in Orders
group o by o.CustomerID into g
select new {
CustomerID = g.Key,
TotalOrders = g.Count()
};Notice the identifier "g", which we introduce after the keyword "into". This identifier represents an item in the collection containing the results of the previous query. We use in the select statement. Remember that each element of the collection we are querying in this second query is actually a collection itself, since this is what "group ... by ..." produces. Therefore, we can call Count() on it to get the number of elements, which is the number of orders per customer. We grouped by the CustomerID field, so that is our Key.
Query continuations can be used to chain together as many selection and grouping queries as you need in whatever order you need.
Under The Hood
Now we have looked at the practicalities of using Linq, I am going to spend a little time taking a look at how it works. Don't worry if you don't understand everything in this section, it's here for those who like to dig a little deeper.
Throughout the series I have talked about how all of the language features introduced in C# 3.0 somehow help to make Linq possible. While anonymous types have shown up pretty explicitly and you can see from the lack of type annotations we have been writing that there is some type inference going on, where are the extension methods and lambda expressions?
There's a principle in language design and implementation called "syntactic sugar". We use this to describe cases where certain syntax isn't directly compiled, but is first transformed into some other more primitive syntax and then passed to the compiler. This is exactly what happens with Linq: your queries are transformed into a sequence of method calls and lambda expressions.
The C# 3.0 specification goes into great detail about these transformations. In practice, you probably don't need to know about this, but let's look at one example to help us understand what is going on. Our simple query from earlier:var Found = from o in Orders
where o.CustomerID == 84
select o.Cost;After transformation by the compiler, becomes:var Found = Orders.Where(o => o.CustomerID == 84)
.Select(o => o.Cost);And this is what actually gets compiled. Here the use of lambda expressions becomes clear. The lambda passed to the Where method is called on each element of Orders to determine whether it should be in the result or not. This produces another intermediate collection, which we then call the Select method on. This calls the lambda it is passed on each object and builds up a final collection of the results, which is then assigned to Found. Beautiful, huh?
Finally, a note on extension methods. Both Where and Select, along with a range of other methods, have been implemented as extension methods. The type they use for "this" is IEnumerable, meaning that any collection that implements that interface can be used with Linq. Without extension methods, it would not have been possible to achieve this level of code re-use.
DLinq and XLinq
In this article I have demonstrated Linq working with objects instantiated from classes that we implemented ourselves and stored in built-in collection classes that implement IEnumerable. However, the query syntax compiles down to calls on extension methods. This means that it is possible to write alternative implementations of Linq that follow the same syntax but perform different operations.
Two examples of this, which will ship with C# 3.0, are DLinq and XLinq. DLinq enables the same language integrated query syntax to do queries on databases by translating the Linq into SQL. XLinq enables queries on XML documents.
Conclusion
Linq brings declarative programming to the C# language and will refine and unify the way that we work with objects, databases, XML and whatever anyone else writes the appropriate extension methods for. It builds upon the language features that we have already seen in the previous parts of the series, but hiding some of them away under syntactic sugar. While the query language has a range of differences to SQL, there are enough similarities to make knowledge of SQL useful to those who know it. However, its utility is far beyond providing yet another way to work with databases.
Beginning LinQ To SQL In C#.NET
Posted on January 25, 2011
What Is LINQ To SQL?
Full meaning of LINQ is 'Language Integrated Query', which replaces the traditional sql query execution process. Moreover, it doesn't only applicable to manipulate database results, but it can also be used to manipulates array/list collections. LinQ was released as part of the .NET framework 3.0 and can be used from languages supported by .NET framework like C#, VB etc. The term 'LINQ To SQL' refers to the technology by which we can use LINQ for access SQL Databases. Here in this tutorial, I will show step by step ways to get started with LINQ To SQL programming with C#.Mapping LINQ To SQL Class From SQL Server Database:
First step to be able to use LinQ on our SQL database, we will need to define a way, by which .NET can recognize the database as Classes/Objects, so we will need to map the database tables/stored procedures to LinQ to SQL classes. To accomplish this task successfully, first open your project in the solution explorer, right click->add->new item, in the 'data' categories, there is a type named 'LINQ To SQL Classes'. Select that. We will get a .dbml file created along with designer interface.
The designer interface has two part, one for dragging tables from server explorer(to create classes from tables automatically), another is for methods where we can drag stored procedures etc. After dragging all classes from server explorer, we are done. Here is a sample db structure that we will be using on the way of this tutorial:
Select Data Using LinQ To SQL:
After we make the dbml files appropriately, its very simple to get started our actually implementation in c# code. Most interesting thing is, we will be using sql query like syntax right from the c# code for getting database results. Suppose, we are trying to validate a user against a given username/password from database. Here is a sample codes of a function for such purpose:public bool IsValidUser(string userName, string passWord)
{
DBNameDataContext myDB = new DBNameDataContext();
var userResults = from u in myDB.Users
where u.Username == userName
&& u.Password == passWord
select u;
return Enumerable.Count(userResults) > 0;
}
You can see, the syntax is much like sql, but not exactly same though. First, when you create a new dbml file with name 'DBName.dbml', there is created a corresponding DataContext class in .net and named something like 'DBNameDataContext' (These DataContext classes are now responsible for .NET To Database communications) . In the linq query syntax, this object will be used to present the database.Next, whenever we write something like "from u in myDB.Users" visual studio automatically treat 'u' as a object of User class, that actually represents database's 'users' table(you will might also notice if your database table contains plural form like 'users', it automatically makes it as singular when creating the linq to sql class like 'User', though table name used in the query will still be plural).Next, notice one of the most useful advantage of LINQ, if you make any kind of data type mistake in your code, you will be notified immediately while compiling your project. This will saves lots of your debugging time and lessen the db errors at the same time. its possible here as now all you are using are acting like a .NET class, so its simply validating the property's data type. Of course, you will have to remember that, if you change your db structure/column data type etc later on, you should have to drag the tables from server explorer to dbml once again to reflect your db changes to the linq to sql classes.Next, Note the result is assign to a variable of type 'var'. This data type is also new from .NET framework 3.0 and used to represent data with dynamic types. That means, here any kind of data returned from the linq query will be assigned to that variable and you will have to just cast that to the appropriate data type.Next, "Enumerable.Count", this function count the number of rows(Or number of objects) returned by the query. You can use this function on 'var' type result object without need of casting it to any intermediate form.Select Operation Without SQL Syntax in LinQ:
The above example showed how to use LinQ To SQL syntax for querying database for retrieve data. However, there is alternative simple ways also for avoid using query like syntax by using integrated 'Where' method. Here is a simple code example to accomplish that:public bool IsValidUser(string userName, string passWord)
{
DBNameDataContext myDB = new DBNameDataContext();
List<User> users = myDB.Users.Where(u => u.Username == userName && u.Password==passWord);
if(users.Count>0)
{
return true;
}
return false;
}
Retrieve A Single Row With LinQ:
On the above example, we have learned to execute a sql like statement. However, LINQ provides much more flexibility than that. Like, if you need a single item/row from database table, it can be done very easily. Here is a code sample for such cases:public User GetUser(string userName)
{
DBNameDataContext myDB = new DBNameDataContext();
User user = myDB.Users.Single(u, u.UserName=>userName);
return user;
}
The above example will return a single record from database table. In the "u.UserName=>userName" part, you can mention any column name you want to be validated.'Foreach' Loop Through All LinQ To SQL Returned Results:
In case, when LinQ query returns multiple results, we often need to traverse through all the result rows(here all LinQ objects) and process in some way. It can be done very easily with aforeach loop. Although for loop also can be used, however foreach is much better in performance(to know details, you can refer to my for vs foreach article). here is the code samples for using the foreach loop for traversing through all result objects:foreach(User user in userResults)
{
//checking the result as like object
if(user.Role == 'admin')
{
//do whatever you need
}
}How to: Join by Using Composite Keys (C# Programming Guide)
Visual Studio 2010
This example shows how to perform join operations in which you want to use more than one key to define a match. This is accomplished by using a composite key. You create a composite key as an anonymous type or named typed with the values that you want to compare. If the query variable will be passed across method boundaries, use a named type that overrides Equals and GetHashCode for the key. The names of the properties, and the order in which they occur, must be identical in each key.The following example demonstrates how to use a composite key to join data from three tables:var query = from o in db.Orders
from p in db.Products
join d in db.OrderDetails
on new {o.OrderID, p.ProductID} equals new {d.OrderID,
d.ProductID} into details
from d in details
select new {o.OrderID, p.ProductID, d.UnitPrice};
Type inference on composite keys depends on the names of the properties in the keys, and the order in which they occur. If the properties in the source sequences do not have the same names, you must assign new names in the keys. For example, if the Orders table and OrderDetails table each used different names for their columns, you could create composite keys by assigning identical names in the anonymous types:join...on new {Name = o.CustomerName, ID = o.CustID} equals
new {Name = d.CustName, ID = d.CustID }
Composite keys can be also used in a group clause.
No comments:
Post a Comment
Please don't spam, spam comments is not allowed here.