LINQ Query : How to Join in LINQ (with example)

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 in London who order products from suppliers who are in Paris. In LINQ the join clause always works against object collections instead of database tables directly. 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 of Order objects. Rather than performing a join, you access the orders by using dot notation:

from order in Customer.Orders...

LINQ Query : How to group in LINQ Query (with example)

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.



// queryCustomersByCity is an IEnumerableIGrouping>
var queryCustomersByCity =
from cust in customers
group cust by cust.City;

// customerGroup is an IGrouping
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 IEnumerableIGrouping>
var custQuery =
from cust in customers
group cust by cust.City into custGroup
where custGroup.Count() > 2
orderby custGroup.Key
select custGroup;

LINQ Query : How to order/sort in LINQ (with example)

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;

LINQ Query : Filtering / Where

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 where clause. 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 the where 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"

LINQ query :Obtaining a Data Source

In 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
var queryAllCustomers = from cust in customers
select cust

LINQ :Performing Operations on Source Elements

An output sequence might not contain any elements or element properties from the source sequence. The output might instead be a sequence of values that is computed by using the source elements as input arguments. The following simple query, when it is executed, outputs a sequence of strings whose values represent a calculation based on the source sequence of elements of type double.

LINQ Queries : Transforming in-Memory Objects into XML

LINQ queries make it easy to transform data between in-memory data structures, SQL databases, ADO.NET Datasets and XML streams or documents. The following example transforms objects in an in-memory data structure into XML elements.

class XMLTransform
{
static void Main()
{
// Create the data source by using a collection initializer.
List students = new List()
{
new Student {First="Svetlana", Last="Omelchenko", ID=111, Scores = new List{97, 92, 81, 60}},
new Student {First="Claire", Last="O’Donnell", ID=112, Scores = new List{75, 84, 91, 39}},
new Student {First="Sven", Last="Mortensen", ID=113, Scores = new List{88, 94, 65, 91}},
};

// Create the query.
var studentsToXML = new XElement("Root",
from student in students
let x = String.Format("{0},{1},{2},{3}", student.Scores[0],
student.Scores[1], student.Scores[2], student.Scores[3])
select new XElement("student",
new XElement("First", student.First),
new XElement("Last", student.Last),
new XElement("Scores", x)
) // end "student"
); // end "Root"

// Execute the query.
Console.WriteLine(studentsToXML);

// Keep the console open in debug mode.
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
}

The code produces the following XML output:

< Root>

Svetlana
Omelchenko
97,92,81,60


Claire
O'Donnell
75,84,91,39


Sven
Mortensen
88,94,65,91

LINQ Query with Example :Selecting a Subset of Each Source Element

There are two primary ways to select a subset of each element in the source sequence:

1.To select just one member of the source element, use the dot operation. In the following example, assume that a Customer object contains several public properties including a string named City. When executed, this query will produce an output sequence of strings.

var query = from cust in Customers
select cust.City;

2. To create elements that contain more than one property from the source element, you can use an object initializer with either a named object or an anonymous type. The following example shows the use of an anonymous type to encapsulate two properties from each Customer element:

var query = from cust in Customer
select new {Name = cust.Name, City = cust.City};

LINQ:Joining Multiple Inputs into One Output Sequence

You can use a LINQ query to create an output sequence that contains elements from more than one input sequence. The following example shows how to combine two in-memory data structures, but the same principles can be applied to combine data from XML or SQL or DataSet sources. Assume the following two class types:


class Student
{
public string First { get; set; }
public string Last {get; set;}
public int ID { get; set; }
public string Street { get; set; }
public string City { get; set; }
public List Scores;
}

class Teacher
{
public string First { get; set; }
public string Last { get; set; }
public int ID { get; set; }
public string City { get; set; }
}

The following example shows the query:

class DataTransformations
{
static void Main()
{
// Create the first data source.
List students = new List()
{
new Student {First="Svetlana",
Last="Omelchenko",
ID=111,
Street="123 Main Street",
City="Seattle",
Scores= new List {97, 92, 81, 60}},
new Student {First="Claire",
Last="O’Donnell",
ID=112,
Street="124 Main Street",
City="Redmond",
Scores= new List {75, 84, 91, 39}},
new Student {First="Sven",
Last="Mortensen",
ID=113,
Street="125 Main Street",
City="Lake City",
Scores= new List {88, 94, 65, 91}},
};

// Create the second data source.
List teachers = new List()
{
new Teacher {First="Ann", Last="Beebe", ID=945, City = "Seattle"},
new Teacher {First="Alex", Last="Robinson", ID=956, City = "Redmond"},
new Teacher {First="Michiyo", Last="Sato", ID=972, City = "Tacoma"}
};

// Create the query.
var peopleInSeattle = (from student in students
where student.City == "Seattle"
select student.Last)
.Concat(from teacher in teachers
where teacher.City == "Seattle"
select teacher.Last);

Console.WriteLine("The following students and teachers live in Seattle:");
// Execute the query.
foreach (var person in peopleInSeattle)
{
Console.WriteLine(person);
}

Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
}
/* Output:
The following students and teachers live in Seattle:
Omelchenko
Beebe
*/

Data Transformations with LINQ (C#)

Language-Integrated Query (LINQ) is not only about retrieving data. It is also a powerful tool for transforming data. By using a LINQ query, you can use a source sequence as input and modify it in many ways to create a new output sequence. You can modify the sequence itself without modifying the elements themselves by sorting and grouping. But perhaps the most powerful feature of LINQ queries is the ability to create new types. This is accomplished in the select clause. For example, you can perform the following tasks:

* Merge multiple input sequences into a single output sequence that has a new type.
* Create output sequences whose elements consist of only one or several properties of each element in the source sequence.
* Create output sequences whose elements consist of the results of operations performed on the source data.
* Create output sequences in a different format. For example, you can transform data from SQL rows or text files into XML.

Type Relationships in LINQ Query Operations (C#)

To write queries effectively, you should understand how types of the variables in a complete query operation all relate to each other. If you understand these relationships you will more easily comprehend the LINQ samples and code examples in the documentation. Furthermore, you will understand what occurs behind the scenes when variables are implicitly typed by using var.

LINQ query operations are strongly typed in the data source, in the query itself, and in the query execution. The type of the variables in the query must be compatible with the type of the elements in the data source and with the type of the iteration variable in the foreach statement. This strong typing guarantees that type errors are caught at compile time when they can be corrected before users encounter them.

IEnumerable variables in LINQ Queries

LINQ query variables are typed as IEnumerable<(Of <(T>)>) or a derived type such as IQueryable<(Of <(T>)>). When you see a query variable that is typed as IEnumerable, it just means that the query, when it is executed, will produce a sequence of zero or more Customer objects.

IEnumerable customerQuery =
from cust in customers
where cust.City == "London"
select cust;

foreach (Customer customer in customerQuery)
{
Console.WriteLine(customer.LastName + ", " + customer.FirstName);
}

Language-Integrated Query (LINQ)

Language-Integrated Query (LINQ) is a set of features in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio 2008 includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.