Book "LINQ in Action" by Fabrice Marguerie

LINQ, Language INtegrated Query, is a new extension to the Visual Basic and C# programming languages designed to simplify data queries and database interaction. It addreses O/R mapping issues by making query operations like SQL statements part of the programming language. It also offers built-in support for querying in-memory collections like arrays or lists, XML, DataSets, and relational databases. LINQ in Action is a fast-paced, comprehensive tutorial for professional developers. This book explores what can be done with LINQ, shows how it works in an application, and addresses the emerging best practices. It presents the general purpose query facilities offered by LINQ in the upcoming C# 3.0 and VB.NET 9.0 languages. A running example introduces basic LINQ concepts. You'll then learn to query unstructured data using LINQ to XML and relational data with LINQ to SQL. Finally, you'll see how to extend LINQ for custom applications. LINQ in Action will guide you along as you explore this new world of lambda expressions, query operators, and expression trees. As well, you'll explore the new features of C# 3.0, VB.NET 9.0. The book is very practical, anchoring each new idea with running code. Whether you want to use LINQ to query objects, XML documents, or relational databases, you will find all the information you need to get started But LINQ in Action does not stop at the basic code. This book also shows you how LINQ can be used for advanced processing of data, including coverage of LINQ's extensibility, which allows querying more data sources than those supported by default. All code samples are built on a concrete business case. The running example, LinqBooks, is a personal book cataloging system that shows you how to create LINQ applications with Visual Studio 2008 and above.

LINQ Aggregate algorithm explained

Question

This might sound lame, but I have not been able to find a really good explanation of Aggregate. Good means short, descriptive, comprehensive with a small and clear example.

Answer

The easiest to understand definition of Aggregate is that it performs an operation on each element of the list taking into account the operations that have gone before. That is to say it performs the action on the first and second element and carries the result forward. Then it operates on the previous result and the third element and carries forward. etc. Example 1. Summing numbers

var nums = new[]{1,2,3,4};
var sum = nums.Aggregate( (a,b) => a + b);
Console.WriteLine(sum); // output: 10 (1+2+3+4)

This adds 1 and 2 to make 3. Then adds 3 (result of previous) and 3 (next element in sequence) to make 6. Then adds 6 and 4 to make 10. Example 2. create a csv from an array of strings

var chars = new []{"a","b","c", "d"};
var csv = chars.Aggregate( (a,b) => a + ',' + b);
Console.WriteLine(csv); // Output a,b,c,d

This works in much the same way. Concatenate a a comma and b to make a,b. Then concatenates a,b with a comma and c to make a,b,c. and so on. Example 3. Multiplying numbers using a seed For completeness, there is an overload of Aggregate which takes a seed value.

var multipliers = new []{10,20,30,40};
var multiplied = multipliers.Aggregate(5, (a,b) => a * b);
Console.WriteLine(multiplied); //Output 1200000 ((((5*10)*20)*30)*40)

Much like the above examples, this starts with a value of 5 and multiplies it by the first element of the sequence 10 giving a result of 50. This result is carried forward and multiplied by the next number in the sequence 20 to give a result of 1000. This continues through the remaining 2 element of the sequence. Live examples: http://rextester.com/ZXZ64749 Docs: http://msdn.microsoft.com/en-us/library/bb548651.aspx Addendum Example 2, above, uses string concatenation to create a list of values separated by a comma. This is a simplistic way to explain the use of Aggregate which was the intention of this answer. However, if using this technique to actually create a large amount of comma separated data, it would be more appropriate to use a StringBuilder, and this is entirely compatible with Aggregate using the seeded overload to initiate the StringBuilder.

var chars = new []{"a","b","c", "d"};
var csv = chars.Aggregate(new StringBuilder(), (a,b) => {
    if(a.Length>0)
        a.Append(",");
    a.Append(b);
    return a;
});
Console.WriteLine(csv);

Updated example: http://rextester.com/YZCVXV6464 Source

Using LINQ to remove elements from a List

Question

Say that I have LINQ query such as:

var authors = from x in authorsList
              where x.firstname == "Bob"
              select x;

Given that authorsList is of type List, how can I delete the Author elements from authorsList that are returned by the query into authors? Or, put another way, how can I delete all of the Bob's from authorsList? Note: This is a simplified example for the purposes of the question.

Answer

Well, it would be easier to exclude them in the first place:

authorsList = authorsList.Where(x => x.FirstName != "Bob").ToList();

However, that would just change the value of authorsList instead of removing the authors from the previous collection. Alternatively, you can use RemoveAll:

authorsList.RemoveAll(x => x.FirstName == "Bob");

If you really need to do it based on another collection, I'd use a HashSet, RemoveAll and Contains:

var setToRemove = new HashSet(authors);
authorsList.RemoveAll(x => setToRemove.Contains(x));

Source

When to use .First and when to use .FirstOrDefault

Question

I've searched around and haven't really found a clear answer as to when you'd want to use .First and when you'd want to use .FirstOrDefault with LINQ. When would you want to use .First? Only when you'd want to catch the exception if no results where returned?

var result = List.Where(x => x == "foo").First();

And when would you want to use .FirstOrDefault? When you'd always want the default type if no result?

var result = List.Where(x => x == "foo").FirstOrDefault();

And for that matter, what about Take?

var result = List.Where(x => x == "foo").Take(1);

Answer

I would use First() when I know or expect the sequence to have at least one element. In other words, when it is an exceptional occurrence when the sequence is empty. Use FirstOrDefault() when you know that you will need to check whether there was an element or not. In other words, when it is legal for the sequence to be empty. You should not rely on exception handling for the check. (It is bad practice and might hurt performance). Finally, the difference between First() and Take() is that First() returns the element itself, while Take() returns a sequence of elements that contains exactly one element. (If you pass 1 as the parameter). Source

What is the Java equivalent for LINQ?

Question

see subj

Answer

LINQ to Objects - JAVA 8 has added the Stream API which adds support for functional-style operations on streams of values: Package java.util.stream Java 8 Explained: Applying Lambdas to Java Collections Source

Multiple Order By

Question

I have two tables, movies and categories, and I get an ordered list by categoryID first and then by Name. The movie table has three columns, ID, Name, and CategoryID. The category table two has columns, ID, and Name. I tried something like the following, but it didn't work.

var movies = _db.Movies.OrderBy( m => { m.CategoryID, m.Name })

Answer

This should work for you:

Var movies = _db.Movies.OrderBy(c => c.Category).ThenBy(n => n.Name)

Source

LINQ query on a DataTable

Question

I'm trying to perform a LINQ query on a DataTable object and bizarrely I am finding that performing such queries on DataTables is not straightforward. For example:

var results = from myRow in myDataTable
where results.Field("RowNo") == 1
select results;

This is not allowed. How do I get something like this working?

Answer

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable. You need to use the AsEnumerable() extension for DataTable. Like so:

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field("RowNo") == 1
select myRow;

If you need to convert IEnumerable to a DataTable, use the CopyToDataTable() extension. Source

Group By Multiple Columns and Sum

Question

How can I do GroupBy Multiple Columns in LINQ Something similar to this in SQL:

SELECT * FROM  GROUP BY ,

How can I convert this to LINQ:

QuantityBreakdown
(
    MaterialID int,
    ProductID int,
    Quantity float
)

INSERT INTO @QuantityBreakdown (MaterialID, ProductID, Quantity)
SELECT MaterialID, ProductID, SUM(Quantity)
FROM @Transactions
GROUP BY MaterialID, ProductID

Answer

Use an anonymous type like group x by new { x.Column1, x.Column2 }

var query = (from t in Transactions
             group t by new {t.MaterialID, t.ProductID}
             into grp
                    select new
                    {
                        grp.Key.MaterialID,
                        grp.Key.ProductID,
                        Quantity = grp.Sum(t => t.Quantity)
                    }).ToList();

Source

Sublists with Group by

Question

Let's suppose if we have a class like

class Person { 
    internal int PersonID; 
    internal string car  ; 
}

Now I have a list of this class: List persons; Now this list can have instances multiple same PersonIDs, for ex.

persons[0] = new Person { PersonID = 1, car = "Ferrari" }; 
persons[1] = new Person { PersonID = 1, car = "BMW"     }; 
persons[2] = new Person { PersonID = 2, car = "Audi"    }; 

Is there a way I can group by personID and get the list of all the cars he has? For ex. expected result would be

class Result { 
   int PersonID;
   List cars; 
}

Could someone please point me in the right direction?

Answer

Absolutely - you basically want:

var results = from p in persons
              group p.car by p.PersonId into g
              select new { PersonID = g.Key, Cars = g.ToList() };

Or as a non-query expression:

var results = persons.GroupBy( p => p.PersonId, 
                               p => p.car,
                               (key, g) => new { 
                                                 PersonId = key, 
                                                 Cars = g.ToList() 
                                                }
                              );

Basically the contents of the group (when view as an IEnumerable) is a sequence of whatever values were in the projection (p.car in this case) present for the given key Source

Returning IEnumerable vs IQueryable

Question

What is the difference between returning IQueryable vs IEnumerable? Will both be deferred execution and when should one be preferred over the other?

IQueryable custs = from c in db.Customers
where c.City == ""
select c;

IEnumerable custs = from c in db.Customers
where c.City == ""
select c;

Answer

Yes, both will give you deferred execution. The difference is that IQueryable is the interface that allows LINQ-to-SQL (LINQ.-to-anything really) to work. So if you further refine your query on an IQueryable, that query will be executed in the database, if possible. For the IEnumerable case, it will be LINQ-to-object, meaning that all objects matching the original query will have to be loaded into memory from the database. Code:

IQueryable custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

That code will execute SQL to only select gold customers. The following code, on the other hand, will execute the original query in the database, then filtering out the non-gold customers in the memory:

IEnumerable custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

This is quite an important difference, and working on IQueryable can in many cases save you from returning too many rows from the database. Another prime example is doing paging: If you use Take and Skip on IQueryable, you will only get the number of rows requested; doing that on an IEnumerable will cause all of your rows to be loaded in memory. Source

Linq Distinct on a particular Property

Question

I am playing with Linq to learn about it but I can't figure out how to use Distinct when I do not have a simple list (a simple list of integers is pretty easy to do, this is not the question). What if want to use Distinct on a list of an Object on ONE or MORE Properties of the object? Example: If an object is Person, with Property Id. How can I get all Person and use Distinct on them with the property Id of the object?

Person1: Id=1, Name="Test1"
Person2: Id=1, Name="Test1"
Person3: Id=2, Name="Test2"

How can I get just Person1 and Person3? Is that possible? If it's not possible with Linq, what would be the best way to have a list of Person depending on some of its Properties in .Net 3.5?

Answer

What you need is a "distinct-by" effectively. I don't believe it's part of LINQ as it stands, although it's fairly easy to write:

public static IEnumerable DistinctBy
    (this IEnumerable source, Func keySelector)
{
    HashSet seenKeys = new HashSet();
    foreach (TSource element in source)
    {
        if (seenKeys.Add(keySelector(element)))
        {
            yield return element;
        }
    }
}

So to find the distinct values using just the Id property, you could use:

var query = people.DistinctBy(p => p.Id);

And to use multiple properties, you can use anonymous types, which implement equality appropriately:

var query = people.DistinctBy(p => new { p.Id, p.Name });

Source

MoreLINQ - enhances LINQ with a lot of methods

This project enhances LINQ to Objects with the following methods: Acquire, AssertCount, Batch, Concat, Consume, DistinctBy, EquiZip, ExceptBy, Fold, ForEach, Generate, GenerateByIndex, GroupAdjacent, Index, MaxBy, MinBy, OrderedMerge, Pad, Pairwise, Pipe, Prepend, PreScan, Scan, SingleOrFallback, SkipUntil, Split, TakeEvery, TakeLast, TakeUntil, ToDataTable, ToDelimitedString, ToHashSet, Trace, Zip, ZipLongest. To install MoreLINQ, run the following command in the Package Manager Console PM> Install-Package morelinq source

LEFT OUTER JOIN in LINQ

Question

How to perform left outer join in C# LINQ to objects without using join-on-equals-into clauses? Is there any way to do that with where clause? Correct problem: For inner join is easy and I have a solution like this

List innerFinal = (from l in lefts from r in rights where l.Key == r.Key
                             select new JoinPair { LeftId = l.Id, RightId = r.Id})

but for left outer join I need a solution. Mine is something like this but it's not working

List< JoinPair> leftFinal = (from l in lefts from r in rights
                             select new JoinPair { 
                                            LeftId = l.Id, 
                                            RightId = ((l.Key==r.Key) ? r.Id : 0
                                        })

where JoinPair is a class:

public class JoinPair { long leftId; long rightId; }

Answer

As stated on: 101 LINQ Samples - Left outer join

var q =
    from c in categories
    join p in products on c equals p.Category into ps
    from p in ps.DefaultIfEmpty()
    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

Source

LINQ equivalent of foreach for IEnumerable

Question

I'd like to do the equivalent of the following in LINQ, but I can't figure out how:

IEnumerable items = GetItems();
items.ForEach(i => i.DoStuff());

What is the real syntax?

Answer

1. LINQ is all about query data, while ForEach method is about manipulation (cause side effects)! 2. There is no ForEach extension for IEnumerable; only for List. So you could do

items.ToList().ForEach(i => i.DoStuff());

Alternatively, write your own ForEach extension method:

public static void ForEach(this IEnumerable enumeration, Action action)
{
    foreach(T item in enumeration)
    {
        action(item);
    }
}

Source

Retrieving Property name from lambda expression

Question

Is there a better way to get the Property name when passed in via a lambda expression?

Answer

Here's an update to method proposed by Cameron. The first parameter is not required.

public PropertyInfo GetPropertyInfo(
    Expression> propertyLambda)
{
    Type type = typeof(TSource);

    MemberExpression member = propertyLambda.Body as MemberExpression;
    if (member == null)
        throw new ArgumentException(string.Format(
            "Expression '{0}' refers to a method, not a property.",
            propertyLambda.ToString()));

    PropertyInfo propInfo = member.Member as PropertyInfo;
    if (propInfo == null)
        throw new ArgumentException(string.Format(
            "Expression '{0}' refers to a field, not a property.",
            propertyLambda.ToString()));

    if (type != propInfo.ReflectedType &&
        !type.IsSubclassOf(propInfo.ReflectedType))
        throw new ArgumentException(string.Format(
            "Expresion '{0}' refers to a property that is not from type {1}.",
            propertyLambda.ToString(),
            type));

    return propInfo;
}

Usage

var propertyInfo = GetPropertyInfo(u => u.UserID);

Source

Split List into Sublists

Question

: I believe this is another easy one for you LINQ masters out there. Is there any way I can separe a List into several separate lists of SomeObject, using the item index as the delimiter of each split? Let me exemplify: I have a List and I need a List<List> or List[], so that each of these resulting lists will contain a group of 3 items of the original list (sequentially). eg.: Original List: [a, g, e, w, p, s, q, f, x, y, i, m, c] Resulting lists: [a, g, e], [w, p, s], [q, f, x], [y, i, m], [c] I'd also need the resulting lists size to be a parameter of this function. Is it possible??

Answer

: completely lazy: works on infinite enumerables no intermediate copying/buffering O(n) execution time works also when child sequences are only partially consumed

public static IEnumerable Chunks(this IEnumerable enumerable,
                                                    int chunkSize)
{
    if (chunkSize < 1) throw new ArgumentException("chunkSize must be positive");

    using (var e = enumerable.GetEnumerator())
    while (e.MoveNext())
    {
        var remaining = chunkSize;    // elements remaining in the current chunk
        var innerMoveNext = new Func(() => --remaining > 0 && e.MoveNext());

        yield return e.GetChunk(innerMoveNext);
        while (innerMoveNext()) {/* discard elements skipped by inner iterator */}
    }
}

private static IEnumerable GetChunk(this IEnumerator e,
                                          Func innerMoveNext)
{
    do yield return e.Current;
    while (innerMoveNext());
}

Example Usage

var src = new [] {1, 2, 3, 4, 5, 6}; 

var c3 = src.Chunks(3);      // {{1, 2, 3}, {4, 5, 6}}; 
var c4 = src.Chunks(4);      // {{1, 2, 3, 4}, {5, 6}}; 

var sum   = c3.Select(c => c.Sum());    // {6, 15}
var count = c3.Count();                 // 2
var take2 = c3.Select(c => c.Take(2));  // {{1, 2}, {4, 5}}

Explanations The code works by nesting two yield based iterators. The outer iterator must keep track of how many elements have been effectively consumed by the inner (chunk) iterator. This is done by a closing over remaining with innerMoveNext(). Unconsumed elements of a chunk are discarded before the next chunk is yielded by the outer iterator. This is necessary because otherwise you get inconsistent results, when the inner enumerables are not (completely) consumed (e.g. c3.Count() would return 6). stackoverflow.com/q/419019#20953521

ForEach LINQ Extension

Crosspost from codeproject.com

Introduction

There are many "missing" extensions of LINQ to Objects. You can look them up with LINQ Extensions Library or MoreLinq library, but with this article I would like to focus solely on ForEach extension. I believe that ForEach extension is the most missed feature from LINQ to Objects. While other extensions are nice-to-have and sometimes you use them and most of the time you don't, the ForEach extension is essential because foreach statements are intrinsic to C# code.

ForEach extension consists of just a simple foreach statement. The extension iterates over the elements of the collection and perform an action on each one of them. However, the benefit of this code being wrapped in an extension is that it can be written in a Lambda notation and can be chained with other LINQ expressions. If you just need the straightforward ForEach extension, you can NuGet it from the libraries I mentioned before. What I want to do in this article is to extend it a little bit further to accommodate more complex user-case scenarios.

ForEach

Our starting point is the basic and most simple version of ForEach extension. You can also take a look at the code in LINQ Extensions Library's ForEach.

// ForEach
public static IEnumerable ForEach(this IEnumerable source, 
    Action action)
{
    foreach (TSource item in source)
        action(item);

    return source;
}

// Foreach with index
public static IEnumerable ForEach(this IEnumerable source, 
    Action action)
{
    int index = 0;
    foreach (TSource item in source)
        action(item, index++);

    return source;
}

These are the obvious extensions. The first extension performs an action on each item. The second extension also passes the item's index in the source collection.

int[] arr = new int[] { 0, 1, 2, 3, 4, 5 };

arr.ForEach(x => { Console.WriteLine("item " + x); });

arr.ForEach((x, index) => { Console.WriteLine("item " + x + ", index " + index); });

ForEach with previous items

Consider a situation in which you would like to iterate over the collection but would also like to have a reference to the previous item or the 2 previous items. Of course you can generalize this situation to any number of previous items but I think a real-life scenarios would beg no more than 2 items at most.

// Foreach with index and previous item
public static IEnumerable ForEach(this IEnumerable source, 
    Action action)
{
    int index = 0;
    TSource previousItem = default(TSource);
    foreach (TSource item in source)
    {
        action(item, index++, previousItem);
        previousItem = item;
    }

    return source;
}

// Foreach with index and 2 previous items
public static IEnumerable ForEach(this IEnumerable source, 
    Action action)
{
    int index = 0;
    TSource previousItem1 = default(TSource);
    TSource previousItem2 = default(TSource);
    foreach (TSource item in source)
    {
        action(item, index++, previousItem1, previousItem2);
        previousItem2 = previousItem1;
        previousItem1 = item;
    }

    return source;
}

When there are no previous items (start of the collection), the extension method pass along the default value of the source type.

int[] arr = new int[] { 0, 1, 2, 3, 4, 5 };

// ForEach with index and previous item
arr.ForEach((x, index, previousItem) =>
{
    Console.WriteLine("previous item " + previousItem);
});

// ForEach with index and 2 previous items
arr.ForEach((x, index, previousItem1, previousItem2) =>
{
    Console.WriteLine(
        "previous item #1 " + previousItem1 + 
        ", previous item #2 " + previousItem2
    );
});

Breakable ForEach

A foreach statement is a loop which can be break out of by using the break statement. I wanted to mimic this behavior by letting the extension method to know when to continue and when to stop applying the action on the remaining items in the collection. The implementation is done by returning a boolean from the action. true means continue, false means break from the loop.

// Breakable ForEach
public static IEnumerable ForEach(this IEnumerable source, 
    Func action)
{
    foreach (TSource item in source)
    {
        if (!action(item))
            break;
    }

    return source;
}

This time the action argument is not an Action delegate but rather a Func delegate with boolean as its returning type. Breaking from the foreach loop doesn't impact the LINQ chaining because the source collection is returned as is. There are more overload versions of this Breakable ForEach, one with an index and 2 more with previous items.

int[] arr = new int[] { 0, 1, 2, 3, 4, 5 };

arr.ForEach(x => 
{ 
    Console.WriteLine("item " + x); 

    if (x == 2)
        return false; // break
    return true; // continue
});

arr.ForEach((x, index) => 
{ 
    Console.WriteLine("item " + x + ", index " + index); 

    if (index == 2)
        return false; // break
    return true; // continue
});

ForEach with previous results

We already saw that we can pass to the action one or two previous items from the collection. What if wanted to pass not the item but a value that is returned from the previous action? Consider our array of integers. A running sum for a given item in the array would be the current item + the result calculation of the running sum for the previous item.

// ForEach with index and previous result
public static IEnumerable ForEach(this IEnumerable source, 
    Func action)
{
    int index = 0;
    TResult previousResult = default(TResult);
    foreach (TSource item in source)
        previousResult = action(item, index++, previousResult);

    return source;
}

The extension method requires both the type of the source collection and the type of the return value of the action.

int[] arr = new int[] { 0, 1, 2, 3, 4, 5 };

// ForEach with index and previous result
arr.ForEach((x, index, previousResult) =>
{
    int result = 10 * x;
    Console.WriteLine("result " + result + ", previous result " + previousResult);
    return result;
});

// Running sum
arr.ForEach((x, index, previousResult) =>
{
    int runningSum = previousResult + x;
    Console.Write(runningSum + " ");
    return runningSum;
});

Conclusion

I integrated this code with all my work projects and it has been helping me immensely. It's working seamlessly with any LINQ expression that I write. I hope it will help you too.

Programming Microsoft® LINQ

I can recommend to learn Programming LINQ the following book [caption id="" align="aligncenter" width="160"] ISBN 978-0735624009[/caption] Get comprehensive guidance for using the Microsoft Language Integrated Query (LINQ) Project with in-depth insights from two experienced developers. Data-rich applications can be difficult to create because of the tremendous differences between query languages used to access data and programming languages commonly used to write applications. This practical guide covers the intricacies of LINQ, a set of extensions to the Visual C# and Visual Basic programming languages. Instead of traversing different language syntaxes required for accessing data from relational and hierarchical data sources, developers will learn how to write queries natively in Visual C# or Visual Basic helping reduce complexity and boost productivity. Written by two experienced developers with strong ties to developer teams at Microsoft, this book describes the LINQ architecture and classes, details the new language features in both C# and Visual Basic, and provides code samples in both languages.
Key Book Benefits:
Delivers an in-depth guidance for using LINQ
Covers architecture, syntax, and classes, illustrating how developers can integrate LINQ into their toolkits
Features code samples in Visual C# (plus Visual Basic in some chapters)

TABLE OF CONTENTS

PART I - LINQ Foundations
1 LINQ Introduction 2 LINQ Syntax Fundamentals 3 LINQ to Objects PART II - LINQ to Relational Data
4 LINQ to SQL: Querying Data 5 LINQ to SQL: Managing Data 6 Tools for LINQ to SQL 7 LINQ to DataSet 8 LINQ to Entities PART III - LINQ and XML
9 LINQ to XML: Managing the XML Infoset 10 LINQ to XML: Querying Nodes PART IV - Advanced LINQ
11 Inside Expression Trees 12 Extending LINQ 13 Parallel LINQ 14 Other LINQ Implementations PART V - AppliedLINQ
15 LINQ in a Multitier Solution 16 LINQ and ASP.NET 17 LINQ and WPF/Silverlight 18 LINQ and the Windows Communication Foundation PART VI - Appendixes
A ADO.NET Entity Framework B C# 3.0: New Language Features C Visual Basic 2008: New Language Features

All Extension Methods

Sometimes it's better to see how linq method is implemeted to better understand functionality. Microsoft implementation of System.Linq.Enumerable.cs Mono implementation of System.Linq.Enumerable.cs and full list of linq methods (from MSDN)

LINQ query syntax vs method syntax

LINQ query syntax is close to native language and it's simple and easy to read. The method syntax is powerfull but definitively more complicated tool. For example take one previous listing

MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var lastSalaries = (from s in emp.Salaries
                    orderby s.EmpNo ascending, s.ToDate descending
                    group s by s.EmpNo into grp
                    let o = grp.First()
                    let averageSalary = (from s in emp.Salaries select s.Salary1).Average()
                    select new { o.EmpNo, o.Salary1, MoreOrLess = (averageSalary > o.Salary1) }).Take(10);

foreach (object b in lastSalaries)
    Console.WriteLine(b);

Listing 12.1

and convert it into new code using corresponding method syntax

MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var lastSalaries = emp.Salaries.OrderBy(s => s.EmpNo)
    .ThenByDescending(s => s.ToDate)
    .GroupBy(s => s.EmpNo)
    .Select(grp => new { o = grp.First(), averageSalary = emp.Salaries.Average(s => s.Salary1) })
    .Select(e => new { e.o.EmpNo, e.o.Salary1, MoreOrLess = (e.averageSalary > e.o.Salary1) })
    .Take(10);


foreach (object b in lastSalaries)
    Console.WriteLine(b);

Listing 12.2

now let's open executable file with Reflector and have look at it. Can you imagine what kind of code was generated?!

    MySqlConnection connection = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
    EmployeeDataContext emp = new EmployeeDataContext(connection);
    
    var lastSalaries = emp.Salaries.OrderBy(
        Expression.Lambda>(Expression.Property(expression = Expression.Parameter(typeof(Salary), "s"), 
        (MethodInfo) methodof(Salary.get_EmpNo)), new ParameterExpression[] { expression }))
        .ThenByDescending(Expression.Lambda>(Expression.Property(expression2 = Expression.Parameter(typeof(Salary), "s"), 
        (MethodInfo) methodof(Salary.get_ToDate)), new ParameterExpression[] { expression2 }))
        .GroupBy(Expression.Lambda>(Expression.Property(expression3 = Expression.Parameter(typeof(Salary), "s"), 
        (MethodInfo) methodof(Salary.get_EmpNo)), new ParameterExpression[] { expression3 }))
        .Select(Expression.Lambda(Expression.New((ConstructorInfo) methodof(<>f__AnonymousType0, Salary>..ctor, 
        <>f__AnonymousType0, Salary>), new Expression[] { 
            expression4 = Expression.Parameter(typeof(IGrouping), "grp"), 
            Expression.Call(null, (MethodInfo) methodof(Enumerable.First), new Expression[] { expression4 }) }, 
            new MethodInfo[] { (MethodInfo) methodof(<>f__AnonymousType0, Salary>.get_grp, 
                <>f__AnonymousType0, Salary>), 
                (MethodInfo) methodof(<>f__AnonymousType0, Salary>.get_o, 
                <>f__AnonymousType0, Salary>) }), new ParameterExpression[] { expression4 }))
                .Select(Expression.Lambda(Expression.New((ConstructorInfo) methodof(<>f__AnonymousType1<<>f__AnonymousType0,
                    Salary>, double>..ctor, <>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>),
     new Expression[] { expression5 = Expression.Parameter(typeof(<>f__AnonymousType0, Salary>), "<>h__TransparentIdentifier0"), 
     Expression.Call(null, (MethodInfo) methodof(Queryable.Average), 
     new Expression[] { Expression.Call(null, (MethodInfo) methodof(Queryable.Select), 
     new Expression[] { Expression.Property(Expression.Constant(emp), (MethodInfo) methodof(EmployeeDataContext.get_Salaries)), 
         Expression.Quote(Expression.Lambda>(Expression.Property(expression6 = Expression.Parameter(typeof(Salary), "s"),
         (MethodInfo) methodof(Salary.get_Salary1)), new ParameterExpression[] { expression6 })) }) }) }, 
     new MethodInfo[] { (MethodInfo) methodof(<>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>.get_<>h__TransparentIdentifier0, <>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>),
         (MethodInfo) methodof(<>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>.get_averageSalary, <>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>) }),
     new ParameterExpression[] { expression5 })).Select(Expression.Lambda(Expression.New((ConstructorInfo) methodof(<>f__AnonymousType2..ctor, <>f__AnonymousType2), 
         new Expression[] { Expression.Property(Expression.Property(Expression.Property(expression7 = Expression.Parameter(typeof(<>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>),
     "<>h__TransparentIdentifier1"), (MethodInfo) methodof(<>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>.get_<>h__TransparentIdentifier0, <>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>)), 
         (MethodInfo) methodof(<>f__AnonymousType0, Salary>.get_o, <>f__AnonymousType0, Salary>)), (MethodInfo) methodof(Salary.get_EmpNo)),
     Expression.Property(Expression.Property(Expression.Property(expression7, (MethodInfo) methodof(<>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>.get_<>h__TransparentIdentifier0, <>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>)), 
         (MethodInfo) methodof(<>f__AnonymousType0, Salary>.get_o, <>f__AnonymousType0, Salary>)), (MethodInfo) methodof(Salary.get_Salary1)), Expression.GreaterThan(Expression.Property(expression7, 
             (MethodInfo) methodof(<>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>.get_averageSalary, <>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>)),
     Expression.Convert(Expression.Property(Expression.Property(Expression.Property(expression7, (MethodInfo) methodof(<>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>.get_<>h__TransparentIdentifier0, <>f__AnonymousType1<<>f__AnonymousType0, Salary>, double>)), 
         (MethodInfo) methodof(<>f__AnonymousType0, Salary>.get_o, <>f__AnonymousType0, Salary>)),
        (MethodInfo) methodof(Salary.get_Salary1)), typeof(double))) }, new MethodInfo[] { (MethodInfo) methodof(<>f__AnonymousType2.get_EmpNo, <>f__AnonymousType2),
        (MethodInfo) methodof(<>f__AnonymousType2.get_Salary1, <>f__AnonymousType2), 
        (MethodInfo) methodof(<>f__AnonymousType2.get_MoreOrLess, <>f__AnonymousType2) }), 
        new ParameterExpression[] { expression7 })).Take(10);
    
    foreach (object obj2 in lastSalaries)
    {
        Console.WriteLine(obj2);
    }

Listing 12.3

I like it ;)