Skip to main content

3 posts tagged with "linq"

View All Tags

IQueryable... IEnumerable... Hmmm...

So there I was, tip-tapping away at my keyboard when I became aware of the slowly loudening noise of a debate. It wasn't about poverty, war, civil rights or anything like that. No; this was far more contentious. It was about the behaviour of <a href="https://msdn.microsoft.com/en-gb/library/bb351562(v=vs.100).aspx">IQueryable&lt;T&gt;</a> when mixed with <a href="https://msdn.microsoft.com/en-gb/library/9eekhta0(v=vs.100).aspx">IEnumerable&lt;T&gt;</a>. I know, right, how could I not get involved?

The code that was being debated was a database query that was being facilitated by Entity Framework. Now let me ask you a question: what is the problem with the methods below?

private IEnumerable<Sage> GetSagesWithSayings()
{
IQueryable<Sage> sageWithSayings =
from s in DbContext.Sages.Include(x => x.Sayings)
select s;
return sageWithSayings;
}
public IEnumerable<Sage> GetSagesWithSayingsBornWithinTheLast100Years()
{
var aHundredYearsAgo = DateTime.Now.AddYears(-100);
var sageWithSayings = GetSagesWithSayings().Where(x => x.DateOfBirth > aHundredYearsAgo);
return sageWithSayings;
}

I've rather emphasised the problem by expressly declaring types in the GetSagesWithSayings method. More typically the IQueryable&lt;Sage&gt; would be hiding itself beneath a var making the problem less obvious. But you get the point; it's something to do with an IQueryable&lt;Sage&gt; being passed back as an IEnumerable&lt;Sage&gt;.

The debate was raging around what this piece of code (or one much like it) actually did. One side positing "it'll get every record from the database and then throw away what it doesn't need in C#-land..." The opposing view being "are you sure about that? Doesn't it just get the records from the last hundred years from the database?"

So it comes down the SQL that ends up being generated. On the one hand it's going to get everything from the Sages table...

select ...
from Sages ...

Or does it include a filter clause as well?

select ...
from Sages ...
where DateOfBirth > '1915-11-30'

You probably know the answer... It gets everything. Every record is brought back from the database and those that are older than 100 years are then casually thrown away. So kinda wasteful. That's the problem. But why? And what does that tell us?

LINQ to Objects vs LINQ to ... ?#

The term "LINQ to Objects" refers to the use of LINQ queries with any IEnumerable or IEnumerable&lt;T&gt; collection directly, without the use of an intermediate LINQ provider or API such as LINQ to SQL or LINQ to XML.

The <a href="https://msdn.microsoft.com/en-gb/library/bb351562(v=vs.100).aspx">IQueryable&lt;T&gt;</a> interface is intended for implementation by query providers.

This interface inherits the <a href="https://msdn.microsoft.com/en-gb/library/9eekhta0(v=vs.100).aspx">IEnumerable&lt;T&gt;</a> interface so that if it represents a query, the results of that query can be enumerated. Enumeration forces the expression tree associated with an <a href="https://msdn.microsoft.com/en-gb/library/bb351562(v=vs.100).aspx">IQueryable&lt;T&gt;</a> object to be executed. Queries that do not return enumerable results are executed when the <a href="https://msdn.microsoft.com/en-gb/library/bb549414(v=vs.100).aspx">Execute&lt;TResult&gt;(Expression)</a> method is called.

The definition of "executing an expression tree" is specific to a query provider. For example, it may involve translating the expression tree to a query language appropriate for an underlying data source.

I know - check me out with my "quotes".

Now, IEnumerable and IQueryable are similar; for instance they are both considered "lazy" as they offer deferred execution. But there is an important difference between IEnumerable and IQueryable; namely that IQueryable hands off information about a query to another provider in order that they may decide how to do the necessary work. IEnumerable does not; its work is done in memory by operating on the data it has.

So let's apply this to our issue. We have an IQueryable&lt;Sage&gt; and we return it as an IEnumerable&lt;Sage&gt;. By doing this we haven't changed the underlying type; it's still an IQueryable&lt;Sage&gt;. But by upcasting to IEnumerable&lt;Sage&gt; we have told the compiler that we don't have an IQueryable&lt;Sage&gt;. We've lied. I trust you're feeling guilty.

No doubt whoever raised you told you not to tell lies. This was probably the very situation they had in mind. The implications of our dirty little fib come back to haunt us when we start to chain on subsequent filters. So when we perform our filter of .Where(x =&gt; x.DateOfBirth &gt; aHundredYearsAgo) the compiler isn't going to get LINQ to Entities's extension methods in on this. No, it's going to get the LINQ to object extension methods instead.

This is the cause of our problem. When it comes to execution we're not getting the database to do the heavy lifting because we've moved away from using IQueryable.

Fixing the Problem#

There are 2 courses of action open to you. The obvious course of action (and 99% of the time what you'd look to do) is change the signature of the `` method to return an IQueryable like so:

private IQueryable<Sage> GetSagesWithSayings()
var sageWithSayings = // I prefer 'var', don't you?
from s in DbContext.Sages.Include(x => x.Sayings)
select s;
return sageWithSayings;
}

The other alternative is what I like to think of as "the escape hatch": <a href="https://msdn.microsoft.com/en-gb/library/bb353734(v=vs.100).aspx">AsQueryable</a>. This takes an IEnumerable, checks if it's actually an IQueryable slumming it and casts back to that if it is. You might use this in a situation where you didn't have control over the data access code. Using it looks like this: (and would work whether GetSagesWithSayings was returning IEnumerableorIQueryable)

public IEnumerable<Sage> GetSagesWithSayingsBornWithinTheLast100Years()
{
var aHundredYearsAgo = DateTime.Now.AddYears(-100);
var sageWithSayings =GetSagesWithSayings().AsQueryable().Where(x => x.DateOfBirth > aHundredYearsAgo);
return sageWithSayings;
}

(Top One, Nice One) Get Sorted

I was recently reading a post by Jaime González García which featured the following mind-bending proposition:

What if I told you that JavaScript has LINQ??

It got me thinking about one of favourite features of LINQ: ordering using OrderBy, ThenBy... The ability to simply expose a collection of objects in a given order with a relatively terse and descriptive syntax. It is fantastically convenient, expressive and something I've been missing in JavaScript. But if Jaime is right... Well, let's see what we can do.

Sort#

JavaScript arrays have a sort method. To quote MDN:

arr.sort([compareFunction])### compareFunction

Optional. Specifies a function that defines the sort order. If omitted, the array is sorted according to each character's Unicode code point value, according to the string conversion of each element.

We want to use the sort function to introduce some LINQ-ish ordering goodness. Sort of. See what I did there?

Before we get going it's worth saying that LINQ's OrderBy and JavaScript's sort are not the same thing. sort actually changes the order of the array. However, OrderBy returns an IOrderedEnumerable which when iterated returns the items of the collection in a particular order. An important difference. If preserving the original order of my array was important to me (spoiler: mostly it isn't) then I could make a call to <a href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/slice">slice</a> prior to calling sort.

sort also returns the array to the caller which is nice for chaining and means we can use it in a similar fashion to the way we use OrderBy. With that in mind, we're going to create comparer functions which will take a lambda / arrow function (ES6 alert!) and return a function which will compare based on the supplied lambda.

String Comparer#

Let's start with ordering by string properties:

function stringComparer(propLambda) {
return (obj1, obj2) => {
const obj1Val = propLambda(obj1) || '';
const obj2Val = propLambda(obj2) || '';
return obj1Val.localeCompare(obj2Val);
};
}

We need some example data to sort: (I can only apologise for my lack of inspiration here)

const foodInTheHouse = [
{ what: 'cake', daysSincePurchase: 2 },
{ what: 'apple', daysSincePurchase: 8 },
{ what: 'orange', daysSincePurchase: 6 },
{ what: 'apple', daysSincePurchase: 2 },
];

If we were doing a sort by strings in LINQ we wouldn't need to implement our own comparer. And the code we'd write would look something like this:

var foodInTheHouseSorted = foodInTheHouse.OrderBy(x => x.what);

With that in mind, here's how it would look to use our shiny and new stringComparer:

const foodInTheHouseSorted = foodInTheHouse.sort(stringComparer(x => x.what));
// foodInTheHouseSorted: [
// { what: 'apple', daysSincePurchase: 8 },
// { what: 'apple', daysSincePurchase: 2 },
// { what: 'cake', daysSincePurchase: 2 },
// { what: 'orange', daysSincePurchase: 6 }
// ]
// PS Don't forget, for our JavaScript: foodInTheHouse === foodInTheHouseSorted
// But for the LINQ: foodInTheHouse != foodInTheHouseSorted
//
// However, if I'd done this:
const foodInTheHouseSlicedAndSorted = foodInTheHouse.slice().sort(stringComparer(x => x.what));
// then: foodInTheHouse !== foodInTheHouseSlicedAndSorted
//
// I shan't mention this again.

Number Comparer#

Well that's strings sorted (quite literally). Now, what about numbers?

function numberComparer(propLambda) {
return (obj1, obj2) => {
const obj1Val = propLambda(obj1);
const obj2Val = propLambda(obj2);
if (obj1Val > obj2Val) {
return 1;
}
else if (obj1Val < obj2Val) {
return -1;
}
return 0;
};
}

If we use the numberComparer on our original array it looks like this:

const foodInTheHouseSorted = foodInTheHouse.sort(numberComparer(x => x.daysSincePurchase));
// foodInTheHouseSorted: [
// { what: 'cake', daysSincePurchase: 2 },
// { what: 'apple', daysSincePurchase: 2 },
// { what: 'orange', daysSincePurchase: 6 },
// { what: 'apple', daysSincePurchase: 8 }
// ]

Descending Into the Pit of Success#

Well this is all kinds of fabulous. But something's probably nagging at you... What about OrderByDescending? What about when I want to sort in the reverse order? May I present the reverse function:

function reverse(comparer) {
return (obj1, obj2) => comparer(obj1, obj2) * -1;
}

As the name suggests, this function takes a given comparer that's handed to it and returns a function that inverts the results of executing that comparer. Clear as mud? A comparer can return 3 types of return values:

  • 0 - implies equality for obj1 and obj2
  • positive - implies obj1 is greater than obj2 by the ordering criterion
  • negative - implies obj1 is less than obj2 by the ordering criterion

Our reverse function takes the comparer it is given and returns a new comparer that will return a positive value where the old one would have returned a negative and vica versa. (Equality is unaffected.) An alternative implementation would have been this:

function reverse(comparer) {
return (obj1, obj2) => comparer(obj2, obj1);
}

Which is more optimal and even simpler as it just swaps the values supplied to the comparer. Whatever tickles your fancy. Either way, when used it looks like this:

const foodInTheHouseSorted = foodInTheHouse.sort(reverse(stringComparer(x => x.what)));
// foodInTheHouseSorted: [
// { what: 'orange', daysSincePurchase: 6 },
// { what: 'cake', daysSincePurchase: 2 },
// { what: 'apple', daysSincePurchase: 8 },
// { what: 'apple', daysSincePurchase: 2 }
// ]

If you'd rather not have a function wrapping a function inline then you could create stringComparerDescending, a numberComparerDescending etc implementations. Arguably it might make for a nicer API. I'm not unhappy with the present approach myself and so I'll leave it as is. But it's an option.

ThenBy#

So far we can sort arrays by strings, we can sort arrays by numbers and we can do either in descending order. It's time to take it to the next level people. That's right ThenBy; I want to be able to sort by one criteria and then by a subcriteria. So perhaps I want to eat the food in the house in alphabetical order, but if I have multiple apples I want to eat the ones I bought most recently first (because the other ones look old, brown and yukky). This may also be a sign I haven't thought my life through, but it's a choice that people make. People that I know. People I may have married.

It's time to compose our comparers together. May I present... drum roll.... the composeComparers function:

function composeComparers(...comparers) {
return (obj1, obj2) => {
const comparer = comparers.find(c => c(obj1, obj2) !== 0);
return (comparer) ? comparer(obj1, obj2) : 0;
};
}

This fine function takes any number of comparers that have been supplied to it. It then returns a comparer function which, when called, iterates through each of the original comparers and executes them until it finds one that returns a value that is not 0 (ie represents that the 2 items are not equal). It then sends that non-zero value back or if all was equal then sends back 0.

const foodInTheHouseSorted = foodInTheHouse.sort(composeComparers(
stringComparer(x => x.what),
numberComparer(x => x.daysSincePurchase),
));
// foodInTheHouseSorted: [
// { what: 'apple', daysSincePurchase: 2 },
// { what: 'apple', daysSincePurchase: 8 },
// { what: 'cake', daysSincePurchase: 2 },
// { what: 'orange', daysSincePurchase: 6 }
// ]

composeComparers: The Sequel#

I'm not gonna lie - I was feeling quite pleased with this approach. I shared it with my friend (and repeated colleague) Peter Foldi. The next day I found this in my inbox:

function composeComparers(...comparers) {
return (obj1, obj2) => comparers.reduce((prev, curr) => prev || curr(obj1, obj2), 0);
}

Dammit he's improved it. It's down to 1 line of code, it doesn't execute a non-zero returning comparer twice and it doesn't rely on <a href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/find">find</a> which only arrives with ES6. So if you wanted to backport to ES5 then this is a better choice.

The only criticism I can make of it is that it iterates through each of the comparers even when it doesn't need to execute them. But that's just carping really.

composeComparers: The Ultimate#

So naturally I thought I was done. Showing Peter's improvements to the estimable Matthew Horsley I learned that this was not so. Because he reached for the keyboard and entered this:

function composeComparers(...comparers) {
// README: <a href="https://wiki.haskell.org/Function_composition">https://wiki.haskell.org/Function_composition</a>
return comparers.reduce((prev, curr) => (a, b) => prev(a, b) || curr(a, b));
}

That's right, he's created a function which takes a number of comparers and reduced them up front into a single comparer function. This means that when the sort takes place there is no longer a need to iterate through the comparers, just execute them.

I know.

I'll get my coat...

Update 08/10/2018: Now TypeScript#

You want to do this with TypeScript? Use this:

type Comparer<TObject> = (obj1: TObject, obj2: TObject) => number;
export function stringComparer<TObject>(propLambda: (obj: TObject) => string): Comparer<TObject> {
return (obj1: TObject, obj2: TObject) => {
const obj1Val = propLambda(obj1) || '';
const obj2Val = propLambda(obj2) || '';
return obj1Val.localeCompare(obj2Val);
};
}
export function numberComparer<TObject>(propLambda: (obj: TObject) => number): Comparer<TObject> {
return (obj1: TObject, obj2: TObject) => {
const obj1Val = propLambda(obj1);
const obj2Val = propLambda(obj2);
if (obj1Val > obj2Val) {
return 1;
} else if (obj1Val < obj2Val) {
return -1;
}
return 0;
};
}
export function reverse<TObject>(comparer: Comparer<TObject>) {
return (obj1: TObject, obj2: TObject) => comparer(obj2, obj1);
}
export function composeComparers<TObject>(...comparers: Comparer<TObject>[]) {
return comparers.reduce((prev, curr) => (a, b) => prev(a, b) || curr(a, b));
}

Giving OData to CRM 4.0

Just recently I was tasked with seeing if we could provide a way to access our Dynamics CRM instance via OData. My initial investigations made it seem like there was nothing for me to do; CRM 2011 provides OData support out of the box. Small problem. We were running CRM 4.0.

It could well have ended there apart from the fact that Microsoft makes it astonishingly easy to to create your own OData service using WCF Data Services. Because it's so straightforward I was able to get an OData solution for CRM 4.0 up and running with very little heavy lifting at all. Want to know how it's done?

LINQ to CRM#

To start with you're going to need the CRM SDK 4.0. This contains a "vanilla" LINQ to CRM client which is used in each of the example applications that can be found in microsoft.xrm\samples. We want this client (or something very like it) to use as the basis for our OData service.

In order to get a LINQ to CRM provider that caters for your own customised CRM instance you need to use the crmsvcutil utility from the CRM SDK (found in the microsoft.xrm\tools\ directory). Detailed instructions on how to use this can be found in this Word document: microsoft.xrm\advanced_developer_extensions_-_developers_guide.docx. Extra information around the topic can be found using these links:

You should end up with custom generated data context classes which look not dissimilar to similar classes that you may already have in place for Entity Framework etc. With your Xrm.DataContext in hand (a subclass of Microsoft.Xrm.Client.Data.Services.CrmDataContext) you'll be ready to move forwards.

Make me an OData Service#

As I said, Microsoft makes it fantastically easy to get an OData service up and running. In this example an entity context model is created from the Northwind database and then exposed as an OData service. To create my CRM OData service I followed a similar process. But rather than creating an entity context model using a database I plugged in the Xrm.DataContext instance of CRM that we created a moment ago. These are the steps I followed to make my service:

  1. Create a new ASP.NET Web Application called "CrmOData" (in case it's relevant I was using Visual Studio 2010 to do this).

  2. Remove all ASPXs / JavaScript / CSS files etc leaving you with an essentially empty project.

  3. Add references to the following DLLs that come with the SDK: - microsoft.crm.sdk.dll

    • microsoft.crm.sdktypeproxy.dll
    • microsoft.crm.sdktypeproxy.xmlserializers.dll
    • microsoft.xrm.client.dll
    • microsoft.xrm.portal.dll
    • microsoft.xrm.portal.files.dll
  4. Add the &lt;microsoft.xrm.client&gt; config section to your web.config (not forgetting the associated Xrm connection string)

  5. Add this new file below to the root of the project:

And that's it - done. When you run this web application you will find an OData service exposed at http://localhost:12345/Crm.svc. You could have it even simpler if you wanted - you could pull out the logging that's in place and leave only the InitializeService there. That's all you need. (The GetEntityById method is a helper method of my own for identifying the GUIDs of CRM.)

You may have noticed that I have made use of caching for my OData service following the steps I found here. Again you may or may not want to use this.

Now, a warning...#

Okay - not so much a warning as a limitation. Whilst most aspects of the OData service work as you would hope there is no support for the $select operator. I had a frustrating time trying to discover why and then came upon this explanation:

"$select statements are not supported. This problem is being discussed here http://social.msdn.microsoft.com/Forums/en/adodotnetdataservices/thread/366086ee-dcef-496a-ad15-f461788ae678 and is caused by the fact that CrmDataContext implements the IExpandProvider interface which in turn causes the DataService to lose support for $select projections"

You can also see here for the original post discussing this.

Finishing off#

In the example I set out here I used the version of WCF Data Services that shipped with Visual Studio 2010. WCF Data Services now ships separately from the .NET Framework and you can pick up the latest and greatest from Nuget. I understand that you could easily switch over to using the latest versions but since I didn't see any feature that I needed on this occasion I haven't.

I hope you find this useful.