Table of contents
- Introduction
- Background
- Queries
- Functions
- Dynamic LINQ
- Lambda Expressions
- Class model used in this article
- LINQ Queries
- Basic query
- Projection/Selection of fields
- Sorting entities
- Filtering entities / Restriction
- Local variables
- Collection methods
- Set functions
- Element functions
- Conversion functions
- Quantifier functions
- Aggregation functions
- Advanced queries
- Joining tables
- Join operator
- Grouping operator
- Nested queries
- Conclusion
Introduction
Language
INtegrated
Queries are SQL-like C# queries that can be used to manipulate collections of objects.
In this article, I will show some cases of usage that show how LINQ can be used to query collections of objects.
The goal of this article is to be a beginners guide for LINQ, and a reference/reminder for others.
Background
When people hear about LINQ, they in most cases think about something
like
the Entity Framework, i.e., the possibility to write queries directly
in C# code that will be directly translated to SQL statements and
executed against the database. It is important to know that this is not
LINQ.
LINQ is a set of C# functions, classes, and operators that enable
developers to execute queries against
a collections of objects.
True LINQ queries are executed against collections.
There are a lot of extensions of LINQ that translate queries to SQL,
XML/XPath, REST, etc. In this article, I will talk about basic LINQ to
collection queries.
There are two forms of LINQ operations - queries and functions. You can see more details about them in the following sections.
Queries
In the LINQ package is added a set of predefined operators (queries) that enable developers to create SQL-like queries on
a collection of objects.
These queries return new collections of data according to the query conditions. Queries are used in
the following form:
Collapse |
Copy Code
from <<element>> in <<collection>>
where <<expression>>
select <<expression>>
As a result of the query, a generic collection (
IEnumerable<T>) is returned. Type
<T> in the generic collection
is determined using the type of expression in the
select <<expression>> part of
the query. An example of a LINQ query that returns
book titles for books with prices less than 500 is shown in the following listing:
Collapse |
Copy Code
from book in myBooks
where book.Price < 500
select book.Title
This query goes through a collection of books
myBooks that takes book entities which have a price property less than 500,
and for each title, returns the title. The result of the query is an object
IEnumerable<String> because
String is a type
of return expression in the
select query (the assumption is that the
Title property of a book is
string).
Functions
LINQ adds a set of useful function that can be applied to
collections. Functions are added as new methods of collection objects
and can be used in
the following form:
- <collectionObject>.methodname()
- <collectionObject>.methodname(<collectionObject>)
- <collectionObject>.methodname(<<expression>>)
All LINQ queries are executed on the collections of type
IEnumerable<T> or
IQueryable<T>, and as results are returned as new
collections (
IEnumerable<T>), objects, or simple types. Examples of LINQ queries or functions that return collections are:
Collapse |
Copy Code
IEnumerable<T> myBooks = allBooks.Except(otherBooks);
IEnumerable<string> titles = myBooks.Where(book=>book.Price<500)
.Select(book=>book.Title);
int count = titles.Count();
First of these three functions creates a new collection where are placed all books expect the ones that are in the
otherBooks collection.
The second function takes all books with prices less than 500, and returns a list of their titles. The
third function finds a number of titles in the
titles collection.
This code shows the usage of each of the three LINQ functions shown above.
Note that there is a dual form of the LINQ queries and functions. For
most of the queries you can write equivalent function form. Example is
shown in the following code:
Collapse | Copy Code
from book in myBooks
where book.Price < 500
select book.Title
|
Collapse | Copy Code
myBooks
.Where(book=>book.Price<500)
.Select(book=>book.Title);
|
In the following sections can be found more examples about LINQ to collections.
Dynamic LINQ
As explained above, LINQ queries and functions return either classes
or collections of classes. In most cases, you will use existing domain
classes (Book, Author,
Publisher, etc.) in the return types of queries. However, in some cases
you might want to return custom classes that do not exist in your class
model.
As an example, you might want to return only the ISBN and title of the
book, and therefore you do not want to return
an entire
Book object with all properties
that you do not need at all. A similar problem will be if you want to
return fields from different classes (e.g., if you want to return
the title of the book and name of publisher).
In this case, you do not need to define new classes that contain only
fields you want to use as return values. LINQ enables you to return so
called
"anonymous classes" - dynamically created classes that do not need to be
explicitly defined in your class model.
An example of such a kind of query is shown in the following example:
Collapse |
Copy Code
var items = from b in books
select new { Title: b.Title,
ISBN: b.ISBN
};
The variable
items is a collection of dynamically created classes where each object has
Title and
ISBN
properties. This class is not explicitly defined in your
class model - it is created dynamically for this query. If you try to
find the type of variable items, you will probably see something like
IEnumerable<a'> -
the .NET Framework
gives some dynamic name to the class (e.g., a'). This way you can use temporary classes just for the results of queries without
the need to define them in the code.
Many people think that this is a bad practice because we have used objects
here without type. This is not true - the
items variable does have a type, however
the type
is not defined in some file. However, you have everything you need from the typed object:
- Compile-time syntax check - if you make some error in typing (e.g., put ISBN instead of ISBN),
the compiler will show you warning and abort compilation.
- Intellisense support - Visual Studio will show you a list of properties/methods of
the object as it does for regular objects.
However, there is a way to use untyped objects in .NET. If you replace the keyword
var with
the keyword
dynamic,
the variable
items will be truly untyped. An example is shown in the following listing:
Collapse |
Copy Code
dynamic items = from b in books
select new { Title: b.Title,
ISBN: b.ISBN
};
In this case you have a true untyped object - there will be no
compile-time check (properties will be validated at run-time only) and
you will not have
any Intellisense support for dynamic objects.
Although
var is better than
dynamic (always use
var where it is possible), there are some cases where you will be forced
to use
dynamic instead of
var. As an example, if you want to return
the result of some LINQ query as a return value of a method you cannot
declare the return type of the method as
var because the scope of
the anonymous class ends in the method body. In that case you will need to either define
an explicit
class or declare the return value as
dynamic.
In this article I will use either explicit or anonymous classes.
Lambda Expressions
While you are working with LINQ, you will find some "weird syntax" in the form
x => y. If you are not familiar with this syntax, I will explain it shortly.
In each LINQ function you will need to define some condition that
will be used to filter objects. The most natural way to do this is to
pass some function
that will be evaluated, and if an object satisfies a condition defined
in the function, it will be included in the result set of the LINQ
function.
That kind of condition function will need to take an object and return a
true/false value that will tell LINQ whether or not this object should
be included
in the result set. An example of that kind of function that checks if
the book
is cheap is shown in the following listing:
Collapse |
Copy Code
public bool IsCheapBook(Book b)
{
return (b.Price < 10);
}
If the book price is less than 10, it is cheap. Now when you have this condition, you can use it in the LINQ clause:
Collapse |
Copy Code
var condition = new Func<Book, bool>(IsBookCheap);
var cheapBooks = books.Where(condition);
In this code we have defined a "function pointer" to the function
IsBookCheap in the form
Func<Book, bool>,
and this function is passed to the LINQ query. LINQ will evaluate this
function for each book object in the books collection and return
a book in the resulting
enumeration if it satisfies the condition defined in the function.
This is not a common practice because conditions are more dynamic and
it is unlikely that you can create a set of precompiled functions
somewhere in the code,
and they will be used by all LINQ queries. Usually we need one
expression per LINQ query so it is better to dynamically generate and
pass
a condition to LINQ. Fortunately C# allows us to do this using
delegates:
Collapse |
Copy Code
var cheapBooks = books.Where(delegate(Book b){ return b.Price < 10; } );
In this example, I have dynamically created
Function<Book, bool>, and put it directly in the
Where( ) condition.
The result is the same as in the previous example but you do not need to define
a separate function for this.
If you think that this is too much typing for a simple inline
function, there is a shorter syntax - lambda expressions. First you can
see that we
don't need the
delegate word (the compiler should know that we need to pass
a delegate as an argument). Also, why do we need to define the type of the argument
(
Book b)? As we are applying this function to the collection of books, we know that
b is a
Book - therefore we can remove this part too.
Also, why should we type
return - an expression that defines the return condition will be enough. The only thing we would need to have is
a separator that will
be placed between the argument and the expression that will be returned - in C#,
we use
=> symbol.
When we remove all the unnecessary stuff and put a separator
=>, we are getting
a lambda expression syntax in the form
argument => expression.
An original delegate and equivalent lambda expression replacement is shown in the following example:
Collapse |
Copy Code
Funct<Book, bool> delegateFunction = delegate(Book b){ return b.Price < 10; } ;
Funct<Book, bool> lambdaExpression = b => b.Price< 10 ;
As you can see, a lambda expression is just a minimized syntax for inline functions. Note that
we can use lambda expressions for any kind of function (not only functions that
return
bool values). As an example, you can define a lambda expression that takes
a book and author, and returns a book title in the format book
"title (author name)". An example of that kind of lambda expression is shown in the following listing:
Collapse |
Copy Code
Func<Book, Author, string> format = (book, author) => book.Title + "(" + author.Name + ")";
This function will take two arguments (
Book and
Author), and return
a string as a result (the last type in the
Func<> object is always
the return type).
In the lambda expression are defined two arguments in the brackets and the string expression that will be returned.
Lambda expressions are widely used in LINQ, so you should get used to them.
Class model used in this article
In the examples, we will use a data structure that represents information about books, their authors, and publishers.
The class diagram for that kind of data structure is shown on the figure below:
Each book can have several authors and one publisher. The fields associated to entities are shown on
the diagram.
Book has information about ISBN, price, number of pages,
publication date, and title. Also, it has a reference to a publisher, and a reference to
a set of authors. Author has a first name and last name without reference back
to a book, and publisher has just a name without reference to books he published.
There will be the assumption that a collections of books, publishers, and authors are placed in
the SampleData.Books, SampleData.Publishers, and SampleData.Authors fields.
LINQ
queries
In this section I will show some examples of basic queries/functions that can be used. If you are
a beginner this should be a good starting point for you.
Basic query
The following example shows the basic usage of LINQ. In order to use a LINQ to
Entities query, you will need to have a collection (e.g., array of books)
that will be queried. In this basic example, you need to specify what collection will be queried ("
from <<
element>>
in <<
collection>>" part) and what data will be selected in
the query ("
select <<
expression>>"
part).
In the example below, the query is executed against a books array, book
entities are selected, and returned as result of queries.
The result of the query
is
IEnumerable<Book> because the type of the expression in
the "'
select <<
expression>>" part is the class
Book.
Collapse |
Copy Code
Book[] books = SampleData.Books;
IEnumerable<Book> bookCollection = from b in books
select b;
foreach (Book book in bookCollection )
Console.WriteLine("Book - {0}", book.Title);
As you might have noticed, this query does nothing useful - it just
selects all books from the book collection and puts them in the
enumeration. However, it shows
the basic usage of the LINQ queries. In the following examples you can
find more useful queries.
Projection/Selection of fields
Using LINQ, developers can transform a collection and create new collections where elements contain just some fields.
The following code shows how you can create
a collection of book titles extracted from a collection of books.
Collapse |
Copy Code
Book[] books = SampleData.Books;
IEnumerable<string> titles = from b in books
select b.Title;
foreach (string t in titles)
Console.WriteLine("Book title - {0}", t);
As a result of this query,
IEnumerable<string> is created because
the type of expression in the
select part is string.
An equivalent example written as a select function and lambda expression is shown in the following code:
Collapse |
Copy Code
Book[] books = SampleData.Books;
IEnumerable<string> titles = books.Select(b=>b.Title);
foreach (string t in titles)
Console.WriteLine("Book title - {0}", t);
Any type can be used as a result collection type. In the following example,
an enumeration of anonymous classes is returned, where each element in the enumeration
has references to the book and the first author of the book:
Collapse |
Copy Code
var bookAuthorCollection = from b in books
select new { Book: b,
Author: b.Authors[0]
};
foreach (var x in bookAuthorCollection)
Console.WriteLine("Book title - {0}, First author {1}",
x.Book.Title, x.Author.FirstName);
This type of queries are useful when you need to dynamically create a new kind of collection.
Flattening collections returned in a Select query
Imagine that you want to return a collection of authors for a set of books. Using
the
Select method, this query would look like the one in the following example:
Collapse |
Copy Code
Book[] books = SampleData.Books;
IEnumerable< List<Author> > authors = books.Select(b=>b.Authors);
foreach (List<Author> bookAuthors in authors)
bookAuthors.ForEach(author=>Console.WriteLine("Book author {0}", author.Name);
In this example, from the book collection are taken a list of authors for each book. When you use
the
Select method, it will return an element in the resulting
enumeration and each element will have the type
List<Author>, because that is a type of property that is returned in
the
Select method. As a result,
you will need to iterate twice over the collection to display all
authors - once to iterate through the enumeration, and then for each
list in
the enumeration, you
will need to iterate again to access each individual author.
However, in some cases, this is not what you want. You might want to have a single flattened list of authors and not
a two level list. In that case, you will
need to use
SelectMany instead of the
Select method as shown in the following example:
Collapse |
Copy Code
Book[] books = SampleData.Books;
IEnumerable<Author> authors = books.SelectMany(b=>b.Authors);
foreach (Author authors in authors)
Console.WriteLine("Book author {0}", author.Name);
The
SelectMany method merges all collections returned in
the lambda expression into the single flattened list. This way you can
easily manipulate
the elements of a collection.
Note that in the first example, I have used the
ForEach method when I have iterated through the list of authors in order to display them.
The
ForEach method is not
part of LINQ because it is a regular extension method added to the list class. However it is
a very useful alternative for compact inline loops (that is probably the reason
why many people think that it is part of LINQ). As the
ForEach method,
it is not part of LINQ, you cannot use it on an enumeration as a regular LINQ method
because it is defined as an extension for
List<T> and not
Enumerable<T> - if you like this method, you will need to convert your enumerable to
a list in order to use it.
Sorting entities
Using LINQ, developers can sort entities within a collection. The following code shows how you can take
a collection of books, order elements by book publisher name and
then by title, and select books in an ordered collection. As a result of the query, you will get
an
IEnumerable<Book> collection sorted by book publishers and titles.
Collapse |
Copy Code
Book[] books = SampleData.Books;
IEnumerable<Book> booksByTitle = from b in books
orderby b.Publisher.Name descending, b.Title
select b;
foreach (Book book in booksByTitle)
Console.WriteLine("Book - {0}\t-\tPublisher: {1} ",
book.Title, book.Publisher.Name );
Alternative code using functions is shown in the following example:
Collapse |
Copy Code
Book[] books = SampleData.Books;
IEnumerable<Book> booksByTitle = books.OrderByDescending(book=>book.Publisher.Name)
.ThenBy(book=>book.Title);
foreach (Book book in booksByTitle)
Console.WriteLine("Book - {0}\t-\tPublisher: {1} ",
book.Title, book.Publisher.Name );
This type of queries is useful if you have complex structures where you will need to order
an entity using the property of a related entity (in this example,
books are ordered by publisher name field which is not placed in the book class at all).
Filtering entities / Restriction
Using LINQ, developers can filter entities from a collection and
create a new collection containing just entities that satisfy
a certain condition.
The following example creates a collection of books containing the word
"our" in the title with price less than 500. From
the array of books are selected
records whose title contains the word "our", price is compared with 500,
and these books are selected and returned as members of
a new collection.
In ''
where <<
expression>>'' can be used a valid C#
boolean expression that uses the fields in a collection, constants,
and variables in a scope (i.e., price). The type of the returned collection is
IEnumerable<Book> because in
the ''
select <<
expression>>''
part is the selected type
Book.
Collapse |
Copy Code
Book[] books = SampleData.Books;
int price = 500;
IEnumerable<Book> filteredBooks = from b in books
where b.Title.Contains("our") && b.Price < price
select b;
foreach (Book book in filteredBooks)
Console.WriteLine("Book - {0},\t Price {1}", book.Title, book.Price);
As an alternative, the
.Where() function can be used as shown in the following example:
Collapse |
Copy Code
Book[] books = SampleData.Books;
int price = 500;
IEnumerable<Book> filteredBooks = books.Where(b=> (b.Title.Contains("our")
&& b.Price < price) );
foreach (Book book in filteredBooks)
Console.WriteLine("Book - {0},\t Price {1}", book.Title, book.Price);
Local variables
You can use local variables in LINQ queries in order to improve the readability of your queries.
Local variables are created using the
let <<localname>> = <<expression>> syntax inside the LINQ query.
Once defined, local variables can be used in any part in the LINQ query (e.g.,
where or
select clause). The following example shows how
you can select a set of first authors in the books containing the word 'our' in
the title, using local variables.
Collapse |
Copy Code
IEnumerable<Author> firstAuthors = from b in books
let title = b.Title
let authors = b.Authors
where title.Contains("our")
select authors[0];
foreach (Author author in firstAuthors)
Console.WriteLine("Author - {0}, {1}",
author.LastName, author.FirstName);
In this example, variables
Title and
Authors reference
the title of the book and the list of authors. It might be easier to reference these items via variables instead of
a direct reference.
Collection
methods
Using LINQ, you can modify existing collections, or collections created using other
LINQ queries. LINQ provides you a set of functions that can be applied to collections.
These functions can be grouped into the following types:
- Set functions - functions that can be used for collection
manipulation operations like merging, intersection, reverse ordering,
etc.,
- Element function - functions that can be used to take particular elements from collections,
- Conversion functions - functions used to convert a type of collection to another,
- Aggregation functions - SQL-like functions that enable you to find a
maximum, sum, or average value of some field in collections,
- Quantifier functions - used to quickly traverse through a collection.
These functions are described in the following sections.
Set functions
Set operators enable you to manipulate collections and use standard
set operations like unions, intersects, etc. LINQ set operators are:
- Distinct - used to extract distinct elements from a collection,
- Union - creates a collection that represents the union of two existing collections,
- Concat - add elements from one collection to another collection,
- Intersect - creates a collection that contains elements that exist in both collections,
- Except - creates a collection that contains elements that exist in one, but do not exist in another collection,
- Reverse - creates a copy of a collection with elements in reversed order,
- EquallAll - checks whether two collections have the same elements in the same order,
- Take - this function takes a number of elements from one collection, and places them in
a new collection,
- Skip - this function skips a number of elements in a collection,
Assuming that the
booksByTitle and
filteredBooks collection are created in previous examples,
the following code finds all books in
booksByTitle
that do not exist in
filteredBooks, and reverses their order.
Collapse |
Copy Code
IEnumerable<Book> otherBooks = booksByTitle.Except(filteredBooks);
otherBooks = otherBooks.Reverse();
foreach (Book book in otherBooks)
Console.WriteLine("Other book - {0} ", book.Title);
In the following example,
booksByTitle and
filteredBooks are concatenated and
the number of elements and number of distinct elements is shown.
Collapse |
Copy Code
IEnumerable<Book> mergedBooks = booksByTitle.Concat(filteredBooks);
Console.WriteLine("Number of elements in merged collection is {0}", mergedBooks.Count());
Console.WriteLine("Number of distinct elements in merged collection is {0}", mergedBooks.Distinct().Count());
Paging example
In this example is shown an example of client side paging using the
Skip(int) and
Take(int) methods. Assuming that there are ten books per page,
the first three pages are skipped using
Skip(30) (ten books per page placed on three pages), and all books that should be shown on
the fourth page are taken using
Take(10). An example code is:
Collapse |
Copy Code
IEnumerable<Book> page4 = booksByTitle.Skip(30).Take(10);
foreach (Book book in page4)
Console.WriteLine("Fourth page - {0} ", book.Title);
There is also an interesting usage of the
Skip/
Take functions in the
SkipWhile/
TakeWhile form:
Collapse |
Copy Code
IEnumerable<Book> page1 = booksByTitle.OrderBy(book=>book.Price)
.SkipWhile(book=>book.Price<100)
.TakeWhile(book=>book.Price<200);
foreach (Book book in page1)
Console.WriteLine("Medium price books - {0} ", book.Title);
In this example, books are ordered by price, all books with price
less than 100 are skipped, and all books with price less than 200 are
returned.
This way all books with price between 100 and 200 are found.
Element functions
There are several useful functions that can be applied when you need to extract
a particular element from a collection:
First - used to find the first element in a collection. Optionally you can pass
a condition to this function in order to find the first element that satisfies
the condition.FirstOrDefault - used to find the first element in a collection. If that kind of element cannot be found,
the default element for that type (e.g., 0 or null) is returned.ElementAt - used to find the element at a specific position.
The following example shows the usage of the
FirstOrDefault and
ElementAt functions:
Collapse |
Copy Code
Book firstBook = books.FirstOrDefault(b=>b.Price>200);
Book thirdBook = books.Where(b=>b.Price>200).ElementAt(2);
Note that you can apply functions either on the collection, or on the result of some other LINQ function.
Conversion
functions
There are a few conversion functions that enable you to convert the
type of one collection to another. Some of these functions are:
ToArray - used to convert elements of collection IEnumerable<T> to array of elements
<T>.ToList - used to convert elements of collection IEnumerable<T> to list
List<T>.ToDictionary - used to convert elements of a collection to a
Dictionary. During conversion, keys and values must be specified.OfType - used to extract the elements of the collection IEnumerable<T1> that implements
the interface/class T2, and put them in the collection IEnumerable<T2>.
The following example shows the usage of the
ToArray and
ToList functions:
Collapse |
Copy Code
Book[] arrBooks = books.ToArray();
List<Book> lstBook = books.ToList();
ToDictionary is an interesting method that enables you to quickly index
a list by some field. An example of such a kind of query is shown in the following listing:
Collapse |
Copy Code
Dictionary<string, Book> booksByISBN = books.ToDictionary(book => book.ISBN);
Dictionary<string, double> pricesByISBN = books.ToDictionary( book => book.ISBN,
book=>book.Price);
If you supply just one lambda expression,
ToDictionary will use it as a key of new dictionary while
the elements will be the objects. You can also supply
lambda expressions for both key and value and create a custom dictionary. In the example above,
we create a dictionary of books indexed by the ISBN key, and a dictionary of prices indexed by ISBN.
Quantifier functions
In each collection, you can find a number of logical functions that can be used to quickly travel through
a collection and check for some condition.
As an example, some of the functions you can use are:
Any - checks whether any of the elements in the collection satisfies
a certain condition.All - checks whether all elements in the collection satisfies a certain condition.
An example of usage of functions is shown in the following example:
Collapse |
Copy Code
if(list.Any(book=>book.Price<500))
Console.WriteLine("At least one book is cheaper than 500$");
if(list.All(book=>book.Price<500))
Console.WriteLine("All books are cheaper than 500$");
In the example above, the
All and
Any functions will check whether the condition that price is less than 500 is satisfied for books in the list.
Aggregation functions
Aggregation functions enable you to perform aggregations on elements
of a collection. Aggregation functions that can be used in LINQ are
Count,
Sum,
Min,
Max, etc.
The following example shows the simple usage of some aggregate functions applied to
an array of integers:
Collapse |
Copy Code
int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
Console.WriteLine("Count of numbers greater than 5 is {0} ", numbers.Count( x=>x>5 ));
Console.WriteLine("Sum of even numbers is {0} ", numbers.Sum( x=>(x%2==0) ));
Console.WriteLine("Minimum odd number is {0} ", numbers.Min( x=>(x%2==1) ));
Console.WriteLine("Maximum is {0} ", numbers.Max());
Console.WriteLine("Average is {0} ", numbers.Average());
As you can see, you can use either standard aggregation functions, or you can preselect
a subset using a lambda condition.
Advanced
queries
This section shows how you can create advanced queries. These kinds
of queries includes joining different collections and using group by
operators.
Joining tables
LINQ enables you to use SQL-like joins on a collection of objects. Collections are joined
the same way as tables in SQL. The following example shows how you can join
three collections publishers, books, and authors, place some restriction conditions in
the
where section, and print results of
the query:
Collapse |
Copy Code
var baCollection = from pub in SampleData.Publishers
from book in SampleData.Books
from auth in SampleData.Authors
where book.Publisher == pub
&& auth.FirstName.Substring(0, 3) == pub.Name.Substring(0, 3)
&& book.Price < 500
&& auth.LastName.StartsWith("G")
select new { Book = book, Author = auth};
foreach (var ba in baCollection)
{ Console.WriteLine("Book {0}\t Author {1} {2}",
ba.Book.Title,
ba.Author.FirstName,
ba.Author.LastName);
}
This query takes publishers, books, and authors; joins books and
publishers via Publisher reference, joins authors and publications by
the first three letters of the name.
In addition results are filtered by books that have prices less than
500, and authors with name starting with letter "G". As you can see,
you can use any condition to join collection entities.
Join operator
LINQ enables you to use thw ''<<
collection>>
join <<
collection>>
on <<
expression>>'' operator
to join two collections on join condition. It is similar to the previous example but you can read queries easily.
The following example shows how you can join publishers with
their books using a
Book.Publisher reference as a join condition.
Collapse |
Copy Code
var book_pub = from p in SampleData.Publishers
join b in SampleData.Books on p equals b.Publisher
into publishers_books
where p.Name.Contains("Press")
select new { Publisher = p, Books = publishers_books};
foreach (var bp in book_pub){
Console.WriteLine("Publisher - {0}", bp.Publisher.Name);
foreach (Book book in bp.Books)
Console.WriteLine("\t Book - {0}", book.Title);
}
A collection of books is attached to each publisher record as a
publishers_books property. In the
where clause, you can filter publishers by a condition.
Note that if you are joining objects by references (in the example above, you can see that
the join condition is
p equals b.Publisher) there is a possibility
that you might get an "Object reference not set to the instance objects" exception if
the referenced objects are not loaded. Make sure that you have loaded all related
objects before you start the query, make sure that you handled null values in
the query, or use join conditions by IDs instead of references where possible.
Grouping operator
LINQ enables you to use group by functionality on a collection of objects.
The following example shows how you can group books by year when they are published.
As a result of the query is returned an enumeration of anonymous classes containing
a property (
Year) that represents a key used in the grouping, and another property (
Books)
representing a collection of books published in that year.
Collapse |
Copy Code
var booksByYear = from book in SampleData.Books
group book by book.PublicationDate.Year
into groupedByYear
orderby groupedByYear.Key descending
select new {
Value = groupedByYear.Key,
Books = groupedByYear
};
foreach (var year in booksByYear){
Console.WriteLine("Books in year - {0}", year.Value);
foreach (var b in year.Books)
Console.WriteLine("Book - {0}", b.Title);
}
Aggregation example
Using LINQ and group by, you can simulate a "
select title, count(*) from
table" SQL query. The following LINQ query shows how to use LINQ to aggregate data:
Collapse |
Copy Code
var raw = new[] { new { Title = "first", Stat = 20, Type = "view" },
new { Title = "first", Stat = 12, Type = "enquiry" },
new { Title = "first", Stat = 0, Type = "click" },
new { Title = "second", Stat = 31, Type = "view" },
new { Title = "second", Stat = 17, Type = "enquiry" },
new { Title = "third", Stat = 23, Type = "view" },
new { Title = "third", Stat = 14, Type = "click" }
};
var groupeddata = from data in raw
group data by data.Title
into grouped
select new { Title = grouped.Key,
Count = grouped.Count()
};
foreach (var data in groupeddata){
Console.WriteLine("Title = {0}\t Count={1}", data.Title, data.Count);
}
Nested queries
LINQ enables you to use nested queries. Once you select entities from a collection, you can use them as part of
an inner query that can be executed on the other collection.
As an example, you can see the class diagram above that has class
Book
that has a reference to the class
Publisher, but there is no reverse relationship. Using nested LINQ queries, you
can select all publishers in a collection and for each publisher entity, call other LINQ queries that find all books that have
a reference to a publisher. An example of such
a query is shown below:
Collapse |
Copy Code
var publisherWithBooks = from publisher in SampleData.Publishers
select new { Publisher = publisher.Name,
Books = from book in SampleData.Books
where book.Publisher == publisher
select book
};
foreach (var publisher in publisherWithBooks){
Console.WriteLine("Publisher - {0}", publisher.Name);
foreach (Book book in publisher.Books)
Console.WriteLine("\t Title \t{0}", book.Title);
}
When a new instance is created in a query, for each publisher entity is taken
a collection of
Books set in the LINQ query and shown on console.
Using local variables you can have a better format for the query as shown in
the following example:
Collapse |
Copy Code
var publisherWithBooks = from publisher in SampleData.Publishers
let publisherBooks = from book in SampleData.Books
where book.Publisher == publisher
select book
select new { Publisher = publisher.Name,
Books = publisherBooks
};
foreach (var publisher in publisherWithBooks){
Console.WriteLine("Publisher - {0}", publisher.Name);
foreach (Book book in publisher.Books)
Console.WriteLine("\t Title \t{0}", book.Title);
}
In this query, books for the current publisher are placed in the
publisherBooks variable, and then is returned
an object containing the name of the publisher and his books.
This way you can dynamically create new relationships between entities that do not exist in your original class model.
Conclusion
Usage of LINQ on collections of entities may significantly improve
your code. Some common operations on collections like filtering,
sorting, finding minimum or maximum,
can be done using a single function call or query. Also, a lot of LINQ
features enable you to use collections in
a SQL-like manner enabling you to join collections, and group
them like in standard SQL. Without LINQ, for that kind functionality you
might need to create several complex functions, but now with
the LINQ library, you can do it in a single statement.
If you have any suggestions for improving this article or some
interesting usage of LINQ queries, let me know and I will add them here.
