Pixata Custom Controls
For Lightswitch

Recent Posts

Popular tags (# posts in brackets)

Anonymous types (3) ASP.NET (5) C# tricks and tips (2) Computers (4) Design patterns (3) DomainDataSource (3) Dynamic data (4) Entity model framework (7) LightSwitch (12) Linq (6) Microsoft (2) MVP (2) MVVM (2) RIA services (5) Silverlight (2) SQL Server (1) Unit testing (4) Visual Studio (5) WCF (3) WPF (2)

Archives

Categories

Blogroll - Fav Blogs

Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Actually, as I'm self-employed, I guess that means that any views I expressed here aren't my own. That's confusing!

About me

Acknowledgments

Theme modified from one by Tom Watts
C#/HTML code styling by Manoli

My rambling thoughts on exploring the .NET framework and related technologies
Home of the surprisingly popular Pixata Custom Controls For Lightswitch (well, it was a surprise to me!)

I previously blogged about a seemingly innocent LINQ problem that had us baffled for ages, which was how you sort or filter an entity’s child collection in Linq. For example, if you want to pull a collection of customers, and include all of their orders from this year, but none from earlier, then there doesn’t seem to be a simple way to do this in Linq. You need to do the query in two stages, the first which builds an anonymous type, and the second which links the to parts of it together. See that post for more details.

I also blogged about the problem of Linq not including child entities when doing joins, which requires you to cast the query to an ObjectQuery<> so you can use the Include() method on it.

The problem comes when you want to combine the two methods, meaning that your query needs to be constructed in two stages to ensure that the sorting or filtering of the child collection is done correctly, but you also need to cast the final result to an ObjectQuery<> before you send it out over WCF. The problem arises because you need to enumerate the query before doing the Include(), as that is the only way to ensure that the sorting is done, but calling AsEnumerable() gives you an IEnumerable<> (reasonably enough), which can't be cast to an ObjectQuery! So what's a fellow to do? Good question, and one that had me going for ages.

The only way I have found top do this is to enumerate the collection manually. I added a foreach loop that went through the parent collection and enumerated the children as it went along. I used Debug.Writeline() to dump the results to the Output window, which is one of my favourite debugging techniques. Anyone looking at the code would logically assume that this loop could be removed for the production code (which is I think what happened when I first wrote it), but this would cause the sorting to fail.

I ended up with code like that shown below (read to the end of this blog post to see an improved version of this code). You don’t need to know what the entities represent, just that I wanted a collection of DhrTemplates, each of which has a number of DhrTemplatesPart entities, and these had to be sorted on the DisplayPosition property.

   1:  var dhrTemplatesVar = from dt in getContext().DhrTemplates
   2:                        where dt.CurrentTemplate
   3:                        select new {
   4:                          currentTemplate = dt,
   5:                          templateParts = dt.DhrTemplatesParts.OrderBy(dtp => dtp.DisplayPosition)
   6:                        };
   7:  Debug.WriteLine("Enumerating the collections...");
   8:  foreach (var dtmpl in dhrTemplatesVar) {
   9:    DhrTemplate dt = dtmpl.currentTemplate;
  10:    Debug.WriteLine("PartDefinitionID: " + dt.PartDefinitionID);
  11:    IOrderedEnumerable<DhrTemplatesPart> parts = dtmpl.templateParts;
  12:    foreach (DhrTemplatesPart dtp in parts) {
  13:      Debug.WriteLine("  " + dtp.Description);
  14:    }
  15:  }
  16:  Debug.WriteLine(" ");
  17:  ObjectQuery<DhrTemplate> dhrTemplatesQry = (from dt in dhrTemplatesVar
  18:                                              select dt.currentTemplate) as ObjectQuery<DhrTemplate>;
  19:  if (dhrTemplatesQry != null) {
  20:    ObjectQuery<DhrTemplate> dhrTemplates = dhrTemplatesQry
  21:      .Include("User")
  22:      .Include("PartDefinition")
  23:      .Include("DhrTemplatesParts.PartDefinition.PartInformationTypes");
  24:    List<DhrTemplate> dhrTemplatesCurrent = dhrTemplates.ToList();
  25:    return dhrTemplatesCurrent;
  26:  }
  27:  return null;

The OrderBy clause on line 5 orders the parts correctly, but isn’t in effect until the query is enumerated, which is what happens between lines 8 and 15. I don’t actually need the Debug.WriteLine statements any more, but I left them in case I need to come back to this again. By the time you get to line 17, you still have the anonymous type for the query, but now it has been enumerated, so the sorting is done. Now we can cast it to an ObjectQuery<> and use Include() to include the child entities.

Quite an insidious problem, but obvious when you see the solution. I still have this feeling that there should be a better way to do it though.

Update some time later...

Well of course, there was a much better way to do it! Sadly, I was so stuck in the problem that I missed the blindingly obvious answer. As mentioned, calling AsEnumerable() enumerates the query, but gives you an IEnumerable<>, which can't be cast to an ObjectQuery. However, you don’t have to take the returned value from AsEnumerable(), you can just call it, and carry on using your original query, which has now been enumerated.

This makes the resulting code much simpler...

   1:  var dhrTemplatesVar = from dt in getContext().DhrTemplates
   2:                        where dt.CurrentTemplate
   3:                        select new {
   4:                          currentTemplate = dt,
   5:                          templateParts = dt.DhrTemplatesParts.OrderBy(dtp => dtp.DisplayPosition)
   6:                        };
   7:  dhrTemplatesVar.AsEnumerable();
   8:  ObjectQuery<DhrTemplate> dhrTemplatesQry = (from dt in dhrTemplatesVar
   9:                                              select dt.currentTemplate) as ObjectQuery<DhrTemplate>;
  10:  // rest of the code omitted as it's identical

Notice that lines 7 to 16 in the previous listing have been replaced with the single line 7 in this listing. I’m calling AsEnumerable(), but ignoring the return value. This enumerates the query, but doesn’t leave me with an ObjectQuery.

Pretty obvious really!

Geographical searches on postcode

I am currently working on an application where I want to have a search feature that allows people to search for businesses within a certain distance of their home (or anywhere else they care to choose).

I have some old UK postcode data knocking around, and was going to use that. For those not familiar with them, UK postcodes are made up of two parts, a major (also known as “outward”) part, and a minor (or “inward”) part. The major part is one or two letters followed by one or two digits, and the minor part is a digit, followed by two letters. Examples of valid postcode formats are M25 0LE, NW11 3ER and L2 3WE (no idea if these are genuine postcodes though).

Coupled with the postcodes are northings and eastings. These odd-sounding beasties are simply the number of metres north and east from a designated origin, which is somewhere west of Lands End. See the Ordinance Survey web site for more details. If you have any two postcodes, you can calculate the distance between them (as the crow flies) by a simple application of Pythagoras’ Theorem. My intention was to use all of this in my search code.

Whilst doing some ferreting around the web, looking for more up-to-date data, I found out that you can now get the UK postcode data absolutely free! When I last looked, which was some years ago admittedly, they charged an arm and a leg for this. Now all you need to do is order it from their downloads page, and you get sent a link to download the data. They have all sorts of interesting data sets there (including all sorts of maps, street views and so on), but the one I wanted was the Code-Point Open set. This was far more up-to-date than the data I had, and was a welcome discovery. Now all I had to do was import it, and write some calculation code.

Before I got any further though, I further discovered that SQL Server 2008 has a new data type known as geography data, which is used to represent real-world (ie represented on a round Earth) co-ordinates. It also has the geometry data type, which is a similar idea, but uses a Euclidean (ie flat) co-ordinate system. Given that I am only dealing with the UK, the curvature of the Earth isn’t significant in distance calculations, and so either data type would do.

However, converting northings and eastings to geography data isn’t as simple as you might hope, but I found a post on Adrian Hill’s blog, where he described how to convert OS data into geometry data, provided a C# console program that does just that, and then showed how easy it is to query the data for distances between points. In other words, exactly what I wanted!

I won’t describe the details here, because you can follow that link and read it for yourself, but basically all you need to do is get the Open-Point data, download the converter and away you go. Truth be told, it wasn’t quite that simple as the format of the data has changed slightly since he wrote the code, so I needed to make a small change. I left a comment on the post, and Adrian updated the code, so you shouldn’t need to worry about that. It doesn’t use hard-coded column numbers anymore, so should be future-proof, in case the OS people change the format again.

Testing the distance calculation, and an important point to improve performance

Once I had the data in SQL Server, I wanted to see how to use it. In the blog post, Adrian showed a simple piece of SQL that did a sample query. You can see the full code on his blog, but the main part of it was a call to an SQL Server function named GeoLocation.STDistance() that did the calculation. He commented that when he tested it, he searched for postcodes within five miles of his home, and got 8354 rows returned in around 500ms. I was somewhat surprised when I tried it to discover that it took around 14 seconds to do a similar calculation! Not exactly what you’d call snappy, and certainly too slow for a (hopefully) busy application.

I was a bit disappointed with this, but decided that for my purposes, it would be accurate enough to do the calculation based on the major part of the postcode only. One of the things Adrian’s code did when importing the data to SQL Server was create rows where the minor part was blank, and the geography value was an average for the whole postcode major area. I adjusted my SQL to do this, and I got results quickly enough that the status bar in SQL Server 2008 Management Studio reported them as zero. OK, so the results won’t be quite as accurate, but at least they will be fast.

Whilst I was writing this blog post, I looked back at Adrian’s description again, and noticed one small bullet point that I had missed. When describing what his code did, he mentioned that it created a spatial index on the geography column. Huh? What on earth (pardon the pun) is a spatial index. No, I had no idea either, so off I went to MSDN, and found an article describing how to create a spatial index. I created one, which took a few minutes (presumably because there were over 17 million postcodes in the table), and tried again. This time, I got the performance that Adrian reported. I don’t know if his code really should have created the spatial index, but it didn’t. However, once it was created, the execution speed was fast enough for me to use a full postcode search in my application.

Bringing the code into the Entity Framework model

So, all armed with working code, my next job was to code the search in my application. This seemed simple enough, just update the Entity Framework model with the new format of the postcodes table, write some Linq to do the query and we’re done… or not! Sadly, Entity Framework doesn’t support the geography data type, so it looked like I couldn’t use my new data! This was a let-down to say the least. Still, not to be put off, I went off and did some more research, and realised that it was time to learn how to use stored procedures with Entity Framework. I’d never done this before, simply because when I discovered Entity Framework, I was so excited by it that I gave up writing SQL altogether. All my data access code went into the repository classes, and was written in Linq.

Creating a stored procedure was pretty easy, and was based on Adrian’s sample SQL:

create procedure GetPostcodesWithinDistance
@OutwardCode varchar(4),
@InwardCode varchar(3),
@Miles int
as
begin
  declare @home geography
  select @home = GeoLocation from PostCodeData
      where OutwardCode = @OutwardCode and InwardCode = @InwardCode
  select OutwardCode, InwardCode from dbo.PostCodeData
  where GeoLocation.STDistance(@home) <= (@Miles * 1609)
    and InwardCode <> ''
end

Using stored procedures in Entity Framework

Having coded the stored procedure, I now had to bring it into the Entity Framework model, and work out how to use it. The first part seemed straightforward enough (doesn’t it always?). You just refresh your model, open the “Stored Procedures” node in the tree on the Add tab, select the stored procedure, click OK and you’re done. Or not. The slight problem was that my fantastic new stored procedure wasn’t actually listed in the tree on the Add tab. It was a fairly simple case of non-presence (Vic wasn’t there either).

After some frustration, someone pointed out to me that it was probably due to the database user not having execute permission on the stored procedure. Always gets me that one! Once I had granted execute permission, the stored procedure appeared (although Vic still wasn’t there), and I was able to bring it into the model. Then I right-clicked the design surface, chose Add –> Function Import and added the stored procedure as a function in the model context. Finally I had access to the code in my model, and could begin to write the search.

Just to ensure that I didn’t get too confident, Microsoft threw another curve-ball at me at this point. My first attempt at the query looked like this:

IEnumerable<string> majors = from p in jbd.GetPostcodeWithinDistance("M7", 45)
 
IQueryable<Business> localBusinesses = from b in ObjSet
          where (from p in majors
                  where p.Major == b.PostcodeMajor
                  select p).Count() > 0
          select b;

The first query grabs all the postcodes within 45 miles of the average location of the major postcode M7.Bear in mind that this query was written before I discovered the spatial index, so it only uses the major part of the postcode. A later revision uses the full postcode. The variable jbd is the context, and ObjSet is an ObjectSet<Business> which is created by my base generic repository.

When this query was executed, I got a delightfully descriptive exception, “Unable to create a constant value of type 'JBD.Entities.Postcode'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.” Somewhat baffled, I turned the Ultimate Source Of All Baffling Problems known as Google, and discovered that this wasn’t actually the best way to code the query, even if it had worked. I had forgotten about the Contains() extension method, which was written specifically for cases like this.

The revised code looked like this (first query omitted as it didn’t change):

IQueryable<Business> businesses = from b in ObjSet
                                  where majors.Contains(b.PostcodeMajor)
                                  select b;

Somewhat neater, clearer and (more to the point) working!

So, with working code, I now only had more hurdle to jump before I could claim this one had been cracked.

Copying geography or geometry data to another database

All of the above was going on on my development machine. Now I had working code, I wanted to put the data on the production server, so that the application could use it. This turned out to be harder than I thought. I tried the SQL Server Import/Export wizard, which usually works cleanly enough, but got an error telling me that it couldn’t convert geography data to geography data. Huh? Searching around for advice, I found someone who suggested trying to do the copy as NTEXT, NIMAGE, and various other data types, but none of these worked either.

After some more searching, I discovered an article describing it, that says that SQL Server has an XML file that contains definitions for how to convert various data types. Unfortunately, Microsoft seem to have forgotten to include the geography and geometry data types in there! I found some code to copy and paste and, lo and behold, it gave the same error message! Ho hum.

After some more messing around and frustrated attempts, I mentioned the problem to a friend of mine who came up with the rather simple suggestion of backing up the development database and attaching it to the SQL Server instance on the production machine. I had thought of this, but didn’t want to do it as the production database has live data in it, which would be lost. He pointed out to me that if I attached it under a different name, I could then copy the data from the newly-attached database to the production one (which would be very easy now that they were both on the same instance of SQL Server), and then delete the copy of the development database. Thank you Yaakov!

Thankfully, this all worked fine, and I finally have a working geographic search. As usual, it was a rough and frustrating ride, but I learned quite a few new things along the way.

# Debugging WPF data binding (Tuesday, February 22, 2011)
 

I was having some trouble with WPF data binding yesterday, where the binding looked correct, but the data wasn't being shown. It turned out that I had forgotten an .Include() on the original query (this data is being sent across the wire through a WCF service, so I can't use lazy loading), but along the way, I discovered a really useful blog post on how to debug WPF data binding.

Edit: After posting this entry, I noticed that one of the comments in the above blog post mentioned an older blog post that discussed the same subject. That adds a couple more ideas to the pot, so is worth reading as well.

Categories: Entity model framework | WCF | WPF

I came across an innocent-looking Linq problem the other day that really had me (and Yossi) baffled for some time.

It's easiest explained using the ubiquitous Northwind database (although just about any other relational database would probably do). Suppose you want a list of customers with their orders. Pretty easy eh? You do something like...

from c in context.Customers.Include("Orders") select c;

As you have included the Orders navigation property, this will include every order for the customer.

Now, what happens if you only want to include orders from this year? Sounds like a pretty simple request eh? Well, it isn't! If you were to do this in SQL, you could simply do something like...

select c.CustomerID, c.CompanyName, o.OrderDate from Customers as c
inner join Orders as o on o.CustomerID=c.CustomerID
where o.OrderDate > '1 Apr 1998'
order by CompanyName, OrderDate

Admittedly, this would return a flat dataset, with one row for each order, but you could fix this with some data shaping without too much problem.

Now the obvious thought would be to do something like this in Linq...

from c in context.Customers.Include("Orders") where c.Orders.OrderDate > new DateTime(2011, 1, 1) select c;

The problem is that this won't compile as the Orders collection doesn't have an OrderDate property. Orders is a collection of Order entities.

Well, this one had us going for ages. I posted a question up in two separate forums (fora?), and didn't get very far. Finally, a very helpful person posted an answer that worked.

What you have to do is create a query that creates an anonymous type that includes the Customer entity, and the Orders that you want. Then you create a second query that selects just the Customer entities from the first query...

using (NorthwindEntities context = new NorthwindEntities()) {
  context.ContextOptions.LazyLoadingEnabled = false;
  var customers = from c in context.Customers.Include("Orders")
                  where c.CompanyName.StartsWith("A")
                  select new { Customer = c, Orders = c.Orders.Where(o => o.OrderDate > new DateTime(1998, 1, 1)) };
  IEnumerable<Customer> customers2 = customers.AsEnumerable().Select(c => c.Customer);
}

What happens behind the scenes is that the Entity Framework uses relationship fixup to sort out which Order entities should be included in the query. I'm not 100% clear hat's going on here, but it works.

Two words of warning here. First, this won't work in the rather excellent LinqPad, as it doesn't support fixup. Second, you need to disable lazy loading.

If you want to read more about relationship fixup, see page 345 in Programming Entity Framework by Julia Lerman. I couldn't find a decent explanation of it on-line.

Well, after realising (or even RIAlising if you like puns) that RIA services weren't cut out for non-Silverlight clients, I went searching for The Next Big Thing. Can't remember if I mentioned it, so I'll bore you by repeating it, but all of this investigation is for a big project I'm in that will need some sort of central data access point that can be used by all sorts of clients, most likely being WinForms, WPF (one of my colleagues is really excited about WPF), ASP.NET and Silverlight. Ideally, we want all of these to be able to use the same point of access for data, with the business logic behind that point of access.

RIA looked like the answer, but doesn't seem to play ball with anything other than Silverlight. So, I went looking at its big brother WCF. The idea was to build an end-to-end solution that had a WCF service on top of an entity framework model, and various clients consuming the service.

This posts describes my initial excitement, great disappointment and final happiness (so far) with consuming a WCF service in WinForms, ASP.NET and Silverlight.

Categories: Entity model framework | WCF

RIA services are a great way to access your data from ASP.NET pages, but out of the box, they provide limited access to data not directly in the entity object itself. For example, an Action entity object (that holds information about an action that a user must do) will hold the ID of the user who has to do the action, but won''t hold the name of the user.

This post explains how you can extend the objects RIA passes back, without having to create new ones, or jump through hoops to do such a simple requirement. It also shows how to modify the Linq to include the information you want.

Anonymous types are a common occurrence when using Linq queries, as you often return objects that don't correspond exactly to objects in the entity model.

The problem is that these anonymous types can only be used easily in the same code block as the one in which they were created. If you try and pass them into another block, or retrieve them in a data event of an ASP.NET Repeater control, you'll run into problems.

This post shows how to get around this problem quite easily, and how to give yourself strongly-typed entity objects that can be passed around.