Paulo Morgado : LINQ

LINQ To SQL Tips & Tricks: String Operations

Paulo Morgado — Tue, 13 Oct 2009 00:51:43 GMT

LINQ brought developers a very user friendly and domain independent style of writing queries.

The fact that the way queries are written is domain independent doesn’t mean that any query will compile the same way or even run the same way. You’ll always need to know how the provider will behave.

LINQ To Objects, for example, will compile queries as a Func<> delegate and the query methods will return IEnumerable(T) implementations.

On the other hand, LINQ To SQL will compile queries as an Expression<Func<>> (which is, in fact, an expression tree) instance and the query methods will return IQueryable(T) implementations.

Because LINQ To SQL queries are compiled to an expression tree, that allows the provider to treat the query elements as it sees fit.

In this case, this means that all operations that can be done on the database will be done on the database and the developer must be aware of this when she/he is writing the queries.

Lets take an example using the AdventureWorks database (if you don’t have it, you can download it from here).

I want to build a list of salutation for every employee that has the SalariedFlag set, in the form of:

[Mr.|Mrs.|Miss] <first name> <middle name> <last name>

But there’s also one detail about the data in the database: FirstName, MiddleName and LastName may have trailing spaces and I don’t want them.

This is a simple query like this:

var q1 = from e in context.Employees
         where e.SalariedFlag
         select
            ((e.Gender == 'F') ? ((e.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " +
            e.Person.FirstName.Trim() +
            (e.Person.MiddleName == null || e.Person.MiddleName.Trim().Length == 0 ? " " : " " + e.Person.MiddleName.Trim() + " ") +
            e.Person.LastName.Trim();

and it will be executed as:

SELECT ((((
    (CASE
        WHEN UNICODE([t0].[Gender]) = @p0 THEN
            (CASE
                WHEN UNICODE([t0].[MaritalStatus]) = @p1 THEN @p2
                ELSE @p3
             END)
        ELSE CONVERT(NVarChar(4),@p4)
     END)) + @p5) + LTRIM(RTRIM([t1].[FirstName]))) + (
    (CASE
        WHEN ([t1].[MiddleName] IS NULL) OR (LEN(LTRIM(RTRIM([t1].[MiddleName]))) = @p6) THEN CONVERT(NVarChar(MAX),@p7)
        ELSE (@p8 + LTRIM(RTRIM([t1].[MiddleName]))) + @p9
     END))) + LTRIM(RTRIM([t1].[LastName])) AS [value]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [70]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [83]
-- @p2: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Miss]
-- @p3: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Mrs.]
-- @p4: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Mr.]
-- @p5: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p7: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p8: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p9: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

If you notice the query, there are a lot of text operations going on for each row.

Depending on the number of rows or database load this can prove to be very bad. The result might even be just a timeout.

So, how do we force the string operations to occur on the client instead of the database?

Only IQueryable<T> will be translated to T-SQL. So, all we need to do is change the type of the enumerator being iterated.

One way to do this is using the the AsEnumerable method of the Enumerable class.

The query would now be written as:

var q2 = from e in context.Employees.Where(e => e.SalariedFlag).AsEnumerable()
         select
            ((e.Gender == 'F') ? ((e.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " + e.Person.FirstName.Trim() +
            (e.Person.MiddleName == null || e.Person.MiddleName.Trim().Length == 0 ? " " : " " + e.Person.MiddleName.Trim() + " ") +
            e.Person.LastName.Trim();

and it will be executed as:

SELECT
    [t0].[BusinessEntityID],
    [t0].[LoginID],
    [t0].[NationalIDNumber],
    [t0].[JobTitle],
    [t0].[MaritalStatus],
    [t0].[BirthDate],
    [t0].[Gender],
    [t0].[HireDate],
    [t0].[SalariedFlag],
    [t0].[VacationHours],
    [t0].[SickLeaveHours],
    [t0].[CurrentFlag],
    [t0].[rowguid],
    [t0].[ModifiedDate],
    [t1].[BusinessEntityID] AS [BusinessEntityID2],
    [t1].[PersonType],
    [t1].[NameStyle],
    [t1].[Title],
    [t1].[FirstName],
    [t1].[MiddleName],
    [t1].[LastName],
    [t1].[Suffix],
    [t1].[EmailPromotion],
    [t1].[AdditionalContactInfo],
    [t1].[Demographics],
    [t1].[rowguid] AS [rowguid2],
    [t1].[ModifiedDate] AS [ModifiedDate2]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

As you can notice, text operations are no longer done on the database, but all the columns of both tables are being returned. And this is still a bad thing because we are using network bandwidth with data that won’t be used.

The way to choose the columns that will be retrieved from the database is by selecting only the ones wanted in the select statement. But because we still want string operations the be done on the client, we’ll need to project the desired columns into an intermediary object. Since we won’t need this object outside the query, we’ll use an anonymous type.

The query would now be written as:

var q3 = from n in
             (
                 from e in context.Employees
                 where e.SalariedFlag
                 select new
                 {
                     Gender = e.Gender,
                     MaritalStatus = e.MaritalStatus,
                     FirstName = e.Person.FirstName,
                     MiddleName = e.Person.MiddleName,
                     LastName = e.Person.LastName
                 }
             ).AsEnumerable()
         select ((n.Gender == 'F') ? ((n.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " + n.FirstName.Trim()
         + (n.MiddleName == null || n.MiddleName.Trim().Length == 0 ? " " : " " + n.MiddleName.Trim() + " ")
         + n.LastName.Trim();

and it will be executed as:

SELECT
    [t0].[Gender],
    [t0].[MaritalStatus],
    [t1].[FirstName],
    [t1].[MiddleName],
    [t1].[LastName]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

Notice the call to Enumerable.As Enumerable to translate the LINQ To SQL query into a LINQ To Objects query.

And, to end this long blog post, if you don’t use any string operations on the query, they, obviously, won’t be translated to T-SQL:

var q4 = from e in context.Employees
         where e.SalariedFlag
         select BuildSalutation(e.Gender, e.MaritalStatus, e.Person.FirstName, e.Person.MiddleName, e.Person.LastName);

where BuildSalutation is implemented as:

private static object BuildSalutation(char gender, char maritalStatus, string firstName, string middleName, string lastName)
{
    return ((gender == 'F') ? ((maritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " "
        + firstName.Trim()
        + (middleName == null || middleName.Trim().Length == 0 ? " " : " " + middleName.Trim() + " ")
        + lastName.Trim();
}

and it will be executed as:

SELECT
    [t0].[Gender] AS [gender],
    [t0].[MaritalStatus] AS [maritalStatus],
    [t1].[FirstName] AS [firstName],
    [t1].[MiddleName] AS [middleName],
    [t1].[LastName] AS [lastName]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

Have you noticed that this T-SQL query is pretty much the same in the previous example?

If you are still reading this, I hope you now aware of how you write your LINQ To SQL queries affect the generated T-SQL.

Playing With SQL Server CLR Integration – Part III

Paulo Morgado — Fri, 12 Jun 2009 00:11:00 GMT

You might have noticed that I used LINQ in my last Playing With SQL Server CLR Integration posts (Part I, Part II).

I couldn’t make it work with the standard Visual Studio 2008 SQL CLR project template. Changing the Target Framework to .NET Framework 3.5 wasn’t enough. I had to edit the .csproj file by hand:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <!— ... -->
  <ItemGroup>
    <!— ... -->
    <Reference Include="System.Core">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
    <Reference Include="System.Xml.Linq">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
  </ItemGroup>
  <!— ... -->
</Project>

LINQ With C# Book Is Finally Out

Paulo Morgado — Sun, 17 May 2009 23:39:51 GMT

It’s finally out!

The LINQ Com C# (LINQ With C#) book that Luís and I wrote is out. Well, mostly Luís than I.

This book, published by FCA, is targeted at anyone that already knows C# 2.0 and wants to know learn the new features introduced with C# 3.0 that made possible LINQ (Language INtegrated Query). The examples in the book are written in C#, but Visual Basic get be get from the book’s site.

	Title:	LINQ Com C#
	Authors:	Luís Abreu / Paulo Morgado
	ISBN:	978-972-722-547-7
	Number of Pages:	216
	Format:	17,0 x 24,0 x 1,3 cm (6.7 x 9.4 x 0.5 in)

The book can be found here:

LINQ With C# Book

Paulo Morgado — Sun, 21 Sep 2008 23:42:00 GMT

Luís just broke the news on our LINQ with C# book.

I was honored with the invitation from Luís to write this book with him for FCA, for which he has already published a few books [^] [^] [^] before.

This will be an entry level book in Portuguese targeted to anyone wanting to learn LINQ with C#.

It has been a fun project with great discussions (only possible because we have half an ocean between us :) ).

More On Another Way For Using The “using” Keyword

Paulo Morgado — Mon, 11 Aug 2008 00:47:50 GMT

In the past I presented another possible use for the using keyword: as hints on LINQ.

I’ve been giving some thought about this lately and refined my proposal.

var q = from person in personCollection using MyEnumerableExtensions
        group person by person.LastName into g using new MyOtherComparer()
        orderby g.Key using new MyComparer()
        select person;

The above query would be converted to:

var q = MyEnumerableExtensions.OrderBy<string, Person>(
    MyEnumerableExtensions.GroupBy<string, Person>(
        personCollection,
        person => person.LastName,
        new MyComparer(),
    ),
    g => g.Key,
    mew MyOtherComparer()
);

What do you think of this?

C# And Visual Basic Generate Different Expression Trees

Paulo Morgado — Mon, 04 Aug 2008 00:23:00 GMT

(This was pointed out to me by Frans Bouma and explained by Jon Skeet)

Imagine you have this set of classes:

public class A
{
    public virtual string P
    {
        get { return "A"; }
    }
}

public class B : A
{
}

public class C : B
{
    public override string P
    {
        get { return "C"; }
    }
}

And this class:

public static class Reporter
{
    public static void Report<T>(T target, Expression<Func<T, string>> expression)
    {
        Console.WriteLine("Expression: {0}", expression);
        Console.WriteLine("\tDeclaring Type: {0}", ((expression as LambdaExpression).Body as MemberExpression).Member.DeclaringType);
        Console.WriteLine("\tInvocation Result: {0} for {1}", expression.Compile().Invoke(target), target.GetType());
        Console.WriteLine();
    }
}

The above class writes to the console the lambda expression, the declaring type of the property and the value of the property for the target and the target’s type.

Now, let’s look at what happens when used from this C# code:

Reporter.Report(new C(), (A a) => a.P);
Reporter.Report(new C(), (B b) => b.P);
Reporter.Report(new C(), (C c) => c.P);

Reporter.Report(new B(), (A a) => a.P);
Reporter.Report(new B(), (B b) => b.P);

Reporter.Report(new A(), (A a) => a.P);

The output will be:

Expression: a => a.P
        Declaring Type: A
        Invocation Result: C for C

Expression: b => b.P
        Declaring Type: A
        Invocation Result: C for C

Expression: c => c.P
        Declaring Type: A
        Invocation Result: C for C

Expression: a => a.P
        Declaring Type: A
        Invocation Result: A for B

Expression: b => b.P
        Declaring Type: A
        Invocation Result: A for B

Expression: a => a.P
        Declaring Type: A
        Invocation Result: A for A

On the other hand, if used from the equivalent Visual Basic code:

Reporter.Report(New C(), Function(a As A) a.P)
Reporter.Report(New C(), Function(b As B) b.P)
Reporter.Report(New C(), Function(c As C) c.P)

Reporter.Report(New B(), Function(a As A) a.P)
Reporter.Report(New B(), Function(b As B) b.P)

Reporter.Report(New A(), Function(a As A) a.P)

The output will be:

Expression: a => a.P
        Declaring Type: A
        Invocation Result: C for C

Expression: b => b.P
        Declaring Type: A
        Invocation Result: C for C

Expression: c => c.P
        Declaring Type: C
        Invocation Result: C for C

Expression: a => a.P
        Declaring Type: A
        Invocation Result: A for B

Expression: b => b.P
        Declaring Type: A
        Invocation Result: A for B

Expression: a => a.P
        Declaring Type: A
        Invocation Result: A for A

Why the differences? It’s because of each language’s specification and compiler:

The C# compiler will issue a virtual call to the virtual member in the class where it’s declared as virtual.
The Visual Basic compiler will issue a virtual call to the overriding member on the class that overrides it.

Build Your Own LINQ Provider

Paulo Morgado — Wed, 07 May 2008 22:16:54 GMT

Mehfuz has been building a toolkit for creating LINQ providers and it's available at CodePlex: LINQExtender

He has even built a LINQ provider for flickr with it: LINQ.Flickr

TypeMock: How to Make Reflective Mocks More Natural

Paulo Morgado — Thu, 31 Jan 2008 23:56:00 GMT

Like I said before, this as been on the back of my mind for a while.

A while back I introduced a way to get the MethodInfo of a method in a strongly typed way using LINQ, and that's how I'm going to make Reflective Mocks more Natural.

Well, it's as easy as this:

public static class MockExtender
{
    public static IParameters ExpectAndReturn<T1, T2, TResult>(this IMockControl mock, Expression<Func<T1, T2, TResult>> expression, object ret, params Type[] genericTypes)
    {
        return mock.ExpectAndReturn((expression.Body as MethodCallExpression).Method.Name, ret, genericTypes);
    }
}

(For now, I'll leave to someone else the implementation of the rest of the overloads)

With this implementation it's possible to handle static classes (a limitation of Fredrik's implementation).

As for private methods, just let Visual Studio (2008, in this sample) and TypeMock do their magic.

So, to test this class:

public static class Class1
{
    public static string PublicMethod(string param1, int param2)
    {
        return PrivateMethod(param2, param1);
    }

    private static string PrivateMethod(int param2, string param1)
    {
        throw new NotImplementedException();
    }
}

We just write this test:

[TestMethod()]
public void PublicMethodTest()
{
    string param1 = "param";
    int param2 = 5;
    string expected = "return";
    string actual;

    Mock targetMock = MockManager.Mock(typeof(Class1));

    targetMock.ExpectAndReturn((int i, string s) => ClassLibrary1.Class1_Accessor.PrivateMethod(i, s), expected).Args(param2, param1);

    actual = Class1.PublicMethod(param1, param2);

    Assert.AreEqual(expected, actual);
}

How about this for clean and simple?

Free e-books on the Visual Studio 2008 Learning Portal

Paulo Morgado — Thu, 03 Jan 2008 00:23:58 GMT

Microsoft is offering free e-books (or, at least some chapters) in the Visual Studio 2008 Learning Portal.

Visual Studio 2008 and .NET Framework 3.5 shipped!

Paulo Morgado — Wed, 21 Nov 2007 00:21:36 GMT

Visual Studio 2008 and .NET Framework 3.5 has finally shipped.

MSDN subscribers can download the final version of Visual Studio 2008 from MSDN Subscription Downloads, but anyone can get a trial version or an Express Edition.

The .NET Framework 3.5 contains many new features building incrementally upon .NET Framework 2.0 and 3.0, and includes .NET Framework 2.0 service pack 1 and .NET Framework 3.0 service pack 1. and is also available for download.

You can read all about it from various teams and team member's blogs:

Soma S. Somasegar

Visual Studio 2008 and .NET Framework 3.5 shipped!

Scott Guthrie

Visual Studio 2008 and .NET 3.5 Released

BCL Team

.NET Framework 3.5 Now Available! [Justin Van Patten]

Jack Gudenkauf

What’s new in the .Net Framework 3.5 (Orcas)

Charlie Calvert

Getting MethodInfo using LINQ

Paulo Morgado — Fri, 23 Mar 2007 12:06:42 GMT

At the last MVP Global Summit, Mads showed me and Rich a way to get the MethodInfo of a method in a strongly typed way using LINQ. Here is how you can do it:

public static class TypeHelper
{
    public delegate void Method();
 
    public delegate void Method<TArg0>(TArg0 arg0);
 
    public delegate void Method<TArg0, TArg1>(TArg0 arg0, TArg1 arg1);
 
    public delegate void Method<TArg0, TArg1, TArg2>(TArg0 arg0, TArg1 arg1, TArg2 arg2);
 
    public delegate void Method<TArg0, TArg1, TArg2, TArg3>(TArg0 arg0, TArg1 arg1, TArg2 arg2, TArg3 arg3);
 
    public static System.Reflection.MethodInfo MethodInfoOf(System.Linq.Expressions.Expression<Method> expression)
    {
        return MethodInfoOfInternal(expression.Body as System.Linq.Expressions.MethodCallExpression);
    }
 
    public static System.Reflection.MethodInfo MethodInfoOf<TArg0>(System.Linq.Expressions.Expression<Method<TArg0>> expression)
    {
        return MethodInfoOfInternal(expression.Body as System.Linq.Expressions.MethodCallExpression);
    }
 
    public static System.Reflection.MethodInfo MethodInfoOf<TArg0, TArg1>(System.Linq.Expressions.Expression<Method<TArg0, TArg1>> expression)
    {
        return MethodInfoOfInternal(expression.Body as System.Linq.Expressions.MethodCallExpression);
    }
 
    public static System.Reflection.MethodInfo MethodInfoOf<TArg0, TArg1, TArg2>(System.Linq.Expressions.Expression<Method<TArg0, TArg1, TArg2>> expression)
    {
        return MethodInfoOfInternal(expression.Body as System.Linq.Expressions.MethodCallExpression);
    }
 
    public static System.Reflection.MethodInfo MethodInfoOf<TArg0, TArg1, TArg2, TArg3>(System.Linq.Expressions.Expression<Method<TArg0, TArg1, TArg2, TArg3>> expression)
    {
        return MethodInfoOfInternal(expression.Body as System.Linq.Expressions.MethodCallExpression);
    }
 
    private static System.Reflection.MethodInfo MethodInfoOfInternal(System.Linq.Expressions.MethodCallExpression methodCallExpression)
    {
        return (methodCallExpression == null) ? null : methodCallExpression.Method;
    }
}

Usage is also very simple:

static bool F(string s)
{
    return !string.IsNullOrEmpty(s);
}
 
static void M1()
{
}
 
static void M2(string s)
{
}
 
static void M3(string s1, string s2)
{
}
 
static void Main(string[] args)
{
    MethodInfo fi = TypeHelper.MethodInfoOf((string s) => F(s));
    MethodInfo mi1 = TypeHelper.MethodInfoOf((string s) => M1());
    MethodInfo mi2 = TypeHelper.MethodInfoOf((string s) => M2(s));
    MethodInfo mi3 = TypeHelper.MethodInfoOf((string s1, string s2) => M3(s1, s2));
}