Pixata Custom Controls
For Lightswitch

Recent Posts

Popular tags (# posts in brackets)

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

Gratuitous link to StackExchange

Archives


Categories


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!


Acknowledgments

Theme modified from one by Tom Watts
C#/F# code styling by Manoli (for posts pre-2016) and Google code prettify (for post from Jan 2016 and beyond)


My rambling thoughts on exploring the .NET framework and related technologies

Whilst working on a new version of one of the main windows in an application, I ended up dumping loads of small jobs onto one of the other developers, as he was the main one working on the XAML file for the window, and if we both tried to edit the file, we ended up with conflicts.

After dumping the umpteenth extra job on him, I was trying to work out if there is a way I could ease his burden a little. There had been quite a few new issues where the extent of my involvement had been to modify the database and update the model. After that, I had to pass it over to him, to avoid us stomping on each other when working on the same files.

So, I got to wondering if there was any way we could split the new quote window up, so multiple people could work on it at the same time. I immediately rejected using user controls for parts of the window, as this has been a road to complexity that we've trodden before, and I for one really don't want to go down the route of handling multiple view models again. It gets too messy and complex. You know me, I'm a simple sort!

So, I looked into including XAML fragments, but gave up on that as a lost cause. I'm not the only one to want it, but it looks like no-one has successfully done it yet, at least, not without some scary code.

The next idea was to store some of the XAML as control templates in a resource dictionary. You don't want to go there, believe me!

However, the brain cell woke up around this point, and I contemplated using user controls, but sharing one view model between them. Unsure of how neat this would be, I tried it out, and was pleasantly surprised by something actually very obvious. My initial thought was to declare the view model as the DataContext for each control, but that led to problems with multiple instances of the view model, so was quickly abandoned.

However, a light bulb went on at this point, and I realised I was making it too complex.

When you add a control to your XAML, it automatically inherits the DataContext of its parent. This is true of user controls as well as built-in controls. What this means is that you can have a window whose DataContext is set as usual, then have user controls for bits of the window, don't have a separate view model for them, and don't set a DataContext on them at all. They will automatically inherits the DataContext of whatever is above them, enabling you to work as usual. This allows you to use the same view model for all user controls on the window.

This means that we could hive out various sections of the XAML into separate user controls, enabling me to work on one, and him to work on another. See the attached sample project, where I did just this. As you can see, if you click the button to show the rather simple quote window, the window itself and both user controls are using the same view model (check the hashcode shown in three places), and if you open another window, it uses its own view model. Download ShareViewModel.zip.

Quite amazing, but quite obvious when you think about it. It has a huge potential for simplifying many of our XAML files, which are often far too big to be manageable anyway.

We would have to be careful about naming and placing all of these user controls, to make sure we don't end up wasting time trying to find them all. I suggested that a VrtQuoteDetailsWindow.xaml would have a VrtQuoteDetailsWindowKeyInfoUserControl.xaml, a VrtQuoteDetailsWindowSomethingElseUserControl.xaml and so on, and that these should be stored in the same folder as the VrtQuoteDetailsWindow.xaml file. Yes, this moves away from the convention of splitting windows and user controls, but my personal opinion is that this is somewhat arbitrary anyway, and no great loss. I much prefer to keep related .xaml files together, irrespective of whether they are windows or user controls.

In order to keep the user controls together with the parent window, they should be nested. Whilst this can be done manually, it's slow and painful, and very easy to get wrong, which mucks up your project file and causes Visual Studio to have a hissy fit. Thankfully, someone has been kind enough to write a very neat extension, which enables you to nest files quickly and easily. Once you have it installed, you right-click on the file(s) that is/are to be a child(ren), and choose File Nesting -> Nest Item from the context menu (or just click Ctrl-Alt-N if you like keyboard shortcuts). This will pop up a small window, which will show you all the other files in the same folder...

Pick one and click the OK button. This will then nest the file(s) under the parent, leaving a neat structure. You can select multiple files, and nest them all under one parent in one go, which is nice.

As you can see, this worked very nicely for the quote window...

We were then able to work on separate parts of the same window, with a minimal amount of conflicts.

This then left the question of how to handle the view model, as we would still only have one of those. However, I have previously solved this problem by splitting the view model into several files. I had previously split another view model across four separate files, which makes it much easier to handle...

This is pretty easy to do. I was working with a class named VRTSystemsListViewModel. In such a case, you would add a new class to the same folder, and name it (say) VRTSystemsListViewModel_Commands. Then change the file name to VRTSystemsListViewModel.Commands.cs, and the class name to VRTSystemsListViewModel (making it partial of course). Then, you need to nest the child view model files under the parent (see above for those with an extremely short memory).

Note that the systems list view model was split into separate files for commands, INPC properties, messages, etc. On reflection, this wasn't the best way to do it, partly because it leaves related code spread over multiple files, and partly because it increases the chances of conflicts. If they had been split according to functionality, then we would have had the same benefits as explained earlier. I later did this for the system details window view model...

As you can see, the view model has been split according to features, making it less likely that two people would be working on the same files at the same time.

There was an unexpected side benefit to this idea. If you've spent any amount of time twiddling your thumbs while Visual Studio loads or saves a XAML file, you'll know how frustrating this is.

It's known issue with Visual Studio, which Microsoft have never acknowledged, and will presumably never fix. However, by splitting the XAML as described above, we dramatically reduced the amount of time Visual Studio spent thinking about loading and saving the XAML files. That made us happy

Thursday, 18 February 2016 17:00:00 (GMT Standard Time, UTC+00:00)

Every now and then, I have seen my CPU usage jump up to close to 100% on all cores, which can slow things down somewhat...

This looks really impressive, like I'm working so hard that my machine can't keep up with my frantic pace. Sadly, this isn't always the truth!

Looking at Task Manager, shows the culprit to be microsoft.alm.shared.remoting.remotecontainer.dll.

If you are using VS2013 or later, you may notice some annotations above your methods and properties...

This feature is known as Code Lens, and is one of those things that is really worth getting to know, as it's a massive time-saver.

Quick digression from the main theme of today's symposium to tell you why it's so good...

By default, there are about five or six code lenses, but as I've changed my options to show only one, only that one is visible in the picture above. This one allows you to see all references to the item in question. Click on the link and you'll see something like this...

This can be docked as a window (click the little rectangular icon in the top-right corner of the code lens pop up). If you double-click an item in the list, you are taken to the appropriate line. Jolly useful.

End of digression, back to the point

However, this fab feature comes with a price. The assembly responsible for scanning your code and updating the Code Lens is the one mentioned above, and can use whopping amounts of CPU.

However, as the most (and in my opinion only) useful part of Code Lens is the references, you can tell VS not to bother with the rest, which will avoid the CPU usage. If you write unit tests (I know, we all should, but let's be honest, most of us don’t!), then the unit test one is also very useful, as it shows you how many tests hit that method and how many passed. However, unless you're really doing TDD properly, it won't be that much use.

Go to Tools -> Options -> Text Editor -> All Languages -> Code Lens and uncheck all of the individual checkboxes. Leave the "Enable CodeLens" checkbox checked, as this will give you the references link...

This should make a big difference to the CPU usage.

Possibly useful extra tip

The other thing that sometimes helps is to have a look in C:\Users\<username>\AppData\Local\Temp\ALM\ShadowCopies\<some_guid> and see how many folders there are. The more there are, the slower Code Lens will be.

You can safely delete them all, and it will slowly replace them for you. I had 5,400 of them, but have seen reports of 190,000! You'll need to do this every now and then if you find that microsoft.alm.shared.remoting.remotecontainer.dll continues to use CPU.

However, I found that they were replaced almost as quickly as I deleted them, so your mileage may vary.

Wednesday, 17 February 2016 17:00:00 (GMT Standard Time, UTC+00:00)
# Tuesday, 16 February 2016

I came across a situation recently in which we needed an object in the client. Due to the cost of creating this object, we didn't want to create it when loading up the window as it might not be needed (if the user didn't click the appropriate button), we didn't want to store it on the window's main object graph, but equally, we wanted to keep hold of it once it had been created.

This situation is not uncommon, where you have to create a class that is expensive (in database or bandwidth terms) to create.

The obvious solution to this was to have a private variable for the instance of Expensive, and a second private bool variable to indicate if the Expensive object had been created. We could check the bool before accessing the Expensive variable. Even better, we could create a property that wrapped this up. Imagine our class is wittily named Expensive...

public class Expensive {
  // For our purposes, this will just be a simple class that writes a message to the output
  // window when it is created (so we can see when that happens)
  public Expensive() {
    Debug.WriteLine("Created new instance of Expensive, hash code is " + GetHashCode());
  }
  // Add a property, so we can reference something
  public int AnInteger { get; set; }
}

Note that in many cases you can't simply rely on checking if _expensive is not null, as null may well be a valid value (or lack thereof) for the object. Therefore, you need two variables.

This works, but has a few drawbacks. Firstly, it's a lot of code for a simple requirement. Imagine you needed to do this for several types, you would end up with a lot of boilerplate code. Secondly, this method involves multiple variables for each expensive object you want. Finally, and most importantly, another developer coming along at some future date might not realise how it's supposed to work, and assume that _expensive will always be available, bypassing the bool and causing a null reference exception. Even worse, when they discover the null reference, they might add extra code to create the object, ignoring the code you previously added to do just the same thing. This is not good.

A more elegant solution

Drum roll please... enter Lazy<T>. This neat little class (introduced in .NET 4.0) allows you to wrap all this up in one variable, and not have to worry about the details.

To illustrate, let's define our Expensive class as follows...

public class Expensive {
  // For our purposes, this will just be a simple class that writes a message to the output
  // window when it is created (so we can see when that happens)
  public Expensive() {
    Debug.WriteLine("Created new instance of Expensive, hash code is " + GetHashCode());
  }
  // Add a property, so we can reference something
  public int AnInteger { get; set; }
}

Instead of having the two private variables mentioned before, you just have one of type Lazy. When declaring it, you add the code that creates the instance as a constructor parameter. As a simple example, you could have something like this...

Lazy<Expensive> exLazy = new Lazy<Expensive>(() => new Expensive());

That's about as simple as it gets, but there's no reason why it couldn't include more code that uses (say) a WCF service, database, EF model or whatever in there.

Now, the clever thing is that the class doesn't get created until you actually want it. For example, see the following (rather over-the-top) code...

Debug.WriteLine("About to declare, but not use our lazy value");
Lazy exLazy = new Lazy(() => new Expensive());
Debug.WriteLine("Created the lazy value, but not used yet");
for (int i = 0; i < 5; i++) {
  Debug.WriteLine("  i: " + i);
  if (i > 1) {
    Debug.WriteLine("  Setting exLazy.Value.AnInteger to " + i);
    exLazy.Value.AnInteger = i;
  }
}
Debug.WriteLine("Ended loop, exLazy.Value.AnInteger is " + exLazy.Value.AnInteger);

Debug.WriteLine("Ended loop, exLazy.Value.AnInteger is " + exLazy.Value.AnInteger);

This uses a liberal amount of writing to the Output window so we can see what's happening. When you run it, you see the following...

About to declare, but not use our lazy value
Created the lazy value, but not used yet
  i: 0
  i: 1
  i: 2
  i: 3
  Setting exLazy.Value.AnInteger to 3
Created new instance of Expensive, hash code is 1707556
  i: 4
  Setting exLazy.Value.AnInteger to 4
  i: 5
  Setting exLazy.Value.AnInteger to 5
  i: 6
  Setting exLazy.Value.AnInteger to 6
  i: 7
  Setting exLazy.Value.AnInteger to 7
  i: 8
  Setting exLazy.Value.AnInteger to 8
  i: 9
  Setting exLazy.Value.AnInteger to 9
Ended loop, exLazy.Value.AnInteger is 9

As you can see, the Expensive class isn't actually created until you want it, and from then on, it exists without needing to check it, or create it again. This means that your code can just refer to exLazy.Value all the way through, and rely on the fact that if it doesn't exists, it will be created for you.

The Lazy<T> class also has also a bool property IsValueCreated, which (oddly enough) allows you to check if the value has been created. Not sure quite how useful that is, but it's there if you need it.

Not the sort of thing you need to use every day, but when you do, it provides a more elegant and low-risk approach than the first method.

C#
Tuesday, 16 February 2016 18:00:00 (GMT Standard Time, UTC+00:00)

What should have been a relatively easy job turned into a very complex one. How I did battle with SQL Server and (mostly) won.

By the way, in case you're wondering about the title (and the subheading below if you've looked further on), then you obviously haven't read The Hunting Of The Snark by Lewis Carroll. If so, I strongly recommend you do, partly because it's a wonderful piece of sophisticated nonsense, and mainly because it's far more entertaining than this article! It was subtitled "An Agony In Eight Fits" with each section being described as a Fit. It seemed appropriate for what happened here...

For the Snark/Carroll aficionados, it's worth noting that most of the illustrations on this page are of the classic Henry Holiday edition of the Hunting Of The Snark. There were plenty more, but his illustrations best fit with Carroll's surreal poem.

Fit The First - Background

The project involved has a set of database tables that store information about support tickets raised by users. The desktop application that the support engineers use already had some searching facilities, but these were limited to things like open/close date, priority, status, etc.

I had a request to allow the user to enter some keywords, and we would show all support tickets that used the keywords in any of a number of fields. As SQL Server provides a full-text search facility, this seemed ideal for the task. Little did I know how complex it would be! I'm documenting it here, in case anyone needs to do something like this again. Hopefully this will save them a few hours of pain.

Fit The Second - The database structure

In order to avoid mucking around with the database that was under source control, I created a test database, and created some tables that included only the relevant fields...

The main table is SupportTickets, which contains two fields to be indexed. Each ticket can have any number of SupportTicketNotes, and for various boring historical reasons, each of those has an associated Actions entry which contains the Details field that is to be included in the search. In addition, a ticket may have a ProblemsAndSolutions entry linked, in which case the two fields shown there are also to be included.

OK, all ready, I thought this would be a doddle. Ha, should have known better!

Fit The Third - Setting up the full-text catalogue

Creating full-text catalogues isn't too hard. You find the table you want to index, right-click it, and from the "Full-Text index" sub-menu, choose "Define Full-Text Index" (note the inconsistent capitalisation, naughty Microsoft!)...

When the wizard appears, click past the first step (which just checks you have a primary key defined, and allows you to choose which to use), and you'll see the fields in the table...

Check off the one(s) that you want to be indexed, and click Next. Click Next again to go past the change tracking step (you want to leave it as Automatically).

You now get to choose or create the full-text catalogue that will be used for the fields you chose. If this is the first time you've created a catalogue for this table, you'll want to check the "Create a new catalogue" checkbox and enter a name for the catalogue. I use the table name with FT appended, but I can't claim this is any standard.

Make sure to click the Insensitive radio button. You can leave the two dropdowns at the bottom as they are and click Next.

Click Next again to go past the population schedules step as we don't need it.

You will then be shown a summary of the catalogue, and if you click Finish, it will be created and populated for you. Depending on how much data is in the table, it may take a few minutes for the catalogue to be fully populated.

So now we are ready to query the data...

Fit The Fourth - Single-table queries

There are various ways of querying full-text catalogues, and you can easily search to find out the benefits of each one. I used fulltexttable, as it has a very powerful algorithm for matching text, including variant word forms, proximity search, stop word ignoring, etc.

In order to search the data, you can use a query something like the following...

select ID, k.rank, ShortSummary, Description from SupportTickets st

inner join freetexttable(SupportTickets, (ShortSummary, Description), 'computer') as k on st.ID=k.[key]

order by rank desc

This uses freetexttable to create a full-text indexed view of the table, using the two columns we included in the catalogue. You can use any combination of indexed columns here, so you can query a subset of the fields if you wish. You can enter as many words as you like, and SQL Server will attempt to match as many as it can, taking frequency into account.

This gives results that look like this...

The first column is the ticket ID. The second column shows the rank, which is SQL Server's indication of how well the row matched the keyword(s) entered. Having searched around, I have come to the conclusion that the actual algorithm for calculating those ranks is based in some very deep Kabbalistic literature, and is partially based on how buckets of goat's entrails you've sacrificed to the Great God Of Database Servers that day. Microsoft seem to be pretty quiet on how the algorithm works, leaving us rather in the dark. This is a little embarrassing when you demo the feature to the client, and they, knowing their data a lot better that you, query for some significant keyword, and get the wrong results. You have to try and explain why your fab code, that you thought was working wonderfully well, came up with the wrong answers! Ho hum.

Note that as you can see from the query, we are selecting from the SupportTickets table, we can include any fields we like, not just those that were indexed.

If you only want to do full-text searching on one table, then you can skip down to Fit The Ninth, and avoid four fits of madness. Otherwise, take a deep breath and read on.

At this stage, I was smiling to myself, thinking that the job was nearly over. All I need to do is join the table,s and I'm done. Ha, teach me a lesson eh?

Fit The Fifth - Joining the tables

Armed with the above query, I set about writing one that would use all of the tables, and give me results that searched the tickets, their notes and the associated problems and solutions.

I'll spare you the gory details, but it turns out that you can't do this. Well, you can write some pretty scary queries that involve joining various freetexttables, but it doesn't work the way you expect, and you do your head in trying to work out how to combine the ranks.

So, off you go back to your favourite search engine, and you find out that what you really need is a view...

Fit The Sixth - Creating a view for the query, and the discovery of stuff

Views are very useful in situations like this, as they enable you to write a complex query, wrap it in a view, and pretend it's a single table. They tend to be read-only, but that's not an issue here.

So, all I needed to do was write a query that joined the various tables, and wrap it in a view. simple eh?

Again, sparing you a few hours' worth of unpleasant details, the end result was that this wasn't so easy, as joining the tables resulted in multiple rows for each support ticket (ie one for each note). This made querying across all the fields impossible.

What I really needed was a way to pull all of the note information into one field, leaving me with one row per ticket, but with all of the note data present. If you've used the Linq Aggregate() method, you'll know what I mean.

After some searching, I came across the delightfully named stuff T-SQL keyword. When used in conjunction with its close friend xml, it enables you to do just what I described above. The syntax is a bit hairy at first, but it works...

select st.ID, st.ShortSummary, st.Description, isnull(ps.Problem, '') Problem, isnull(ps.Solution, '') Solution,
    (select isnull(stuff((select cast(Details as varchar(max)) + ' ' from SupportTicketNotes stn
    inner join Actions a on a.ID=stn.ActionID where stn.SupportTicketID=st.ID for xml path('')), 1, 0, ''), '')) as Notes
from SupportTickets st
left join ProblemsAndSolutions ps on ps.ID=st.ProblemAndSolutionID

The first line of this is fairly simple, it's just selecting various fields. The second and third lines are where the clever stuff (sic) happens. I'm not going to give a full explanation of the stuff/xml syntax, partly because I don't fully understand it myself, and partly because it's dead dull and boring! If you're really interested, you can cure your insomnia by having a look at the MSDN pages about it. The query above is a little more complex than it needed to be, as the Details field of the Actions table was declared as type "text" which is incompatible with varchar, so I had to cast the one unto the other.

Anyway, the end result was the the data returned by this query included a Notes column that was an aggregation of all of the ticket's note details. I wrapped this query in a view, tested it out and was happy (briefly).

Ah, I must have cracked it this time. Well, no...

Fit The Seventh - Of unique keys, unique clustered indexes, schema binding and left joins

So there I was, ready to create a full-text catalogue on my shiny new view. You would think they would have the decency to leave me happy, but no, it all went wrong again.

When I tried to create the catalogue, I got an error message telling me it couldn't be created, as I needed a unique key on the view. Well, I thought I had one. After all, the primary table in the view is the SupportTickets table, and that has a unique key, which was included in the query, so what's the problem? The problem is that SQL Server is the boss, and it doesn't recognise that field as a unique key.

OK, so off to Google I went, and discovered that ll you need to do is create a unique clustered index for the primary key, and away you go. Or not...

I tried the following fabulous piece of SQL...

create unique clustered index IX_SupportTicketsWithExtraInfo on SupportTicketsWithExtraInfo(ID)

...where SupportTicketsWithExtraInfo was the snappy name of my view. I was somewhat annoyed to be told that the index couldn't be created due to some weird error about schema binding. I can't remember the exact text, but it was something weird like that.

So back off to Google to find out what that was all about, and it seems that all you need to do is alter the view to include schema binding...

create view SupportTicketsWithExtraInfo with schemabinding
as
...rest of the SQL here...

You know what's coming don't you. Another classic Microsoft "what the heck does that mean" error message...

Cannot schema bind view 'dbo.SupportTicketsWithExtraInfo' because name 'SupportTicketsWithExtraInfo is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Huh? It turns out that in order to use schema binding you have to use table names that consist of two parts, such as Furry.Ferrets and the like. Well, there was no way I was going to rename the tables, and having tried to use aliases and views to get around the problem, I realised that I wasn't going to be able to do it that way.

I don't remember the exact sequence of events at this point, as I think hysteria was setting in, but I eventually got to the stage where I had something that (theoretically at least) could be used in the full-text catalogue. Sadly, it wasn't to be.

The final insult in this sorry story was when I found out that you can't use full-text indexing on a view that uses a left or right join. No, I'm not making this up, honest!

I came across a forum post where someone tantalisingly mentioned a trick where you could simulate an inner join, which would have been just what I wanted, except that I couldn't find any hint of it anywhere.

So that was it. After all that, I was scuppered at the last stage. Back to the drawing board.

Note that if you want to create a full-text index on a view that doesn't include left or right joins, you will be OK, and can gleefully skip down to Fit The Ninth. As for me, I had to ensure Fit The Eight first.

Fit The Eighth - The summary table and the triggers

At this point, I had to rethink the whole thing from the beginning. This was quite a strain on the brain cell, and involved a strong cup of herbal brew, but I eventually came up with the earth-shatteringly obvious idea of creating a new table to hold summaries of the support ticket information, and creating a full-text catalogue against that.

Remember stuff and xml from Fit The Sixth? Well that had enabled me to aggregate the note data into a single row in the view. All I needed to do was use this to aggregate the note data into the summary table. It was messy, but I ended up with triggers on the main tables that looked like this...

create trigger SupportTicketNotes_Update on SupportTicketNotes
after update
as
declare @ID int
select @ID = (select SupportTicketID from inserted)
declare @Notes varchar(max)
select @Notes = (select isnull(stuff((select cast(Details as varchar(max)) + ' '
  from SupportTicketNotes stn inner join Actions a on a.ID=stn.ActionID
  where stn.Active=1 and stn.SupportTicketID=@ID for xml path('')), 1, 0, ''), ''))
update SupportTicketsSummaries set Notes=@Notes where ID=@ID

With similar triggers on the other tables, I ended up with a SupportTicketsSummaries table that contained the fields I needed, and was a plain old single table. Hurray, back to Fit The Fourth!

I created a full-text catalogue against the summary table, and was finally able to write a query that would query all the support ticket data...

select ID, k.rank, ShortSummary + ' ' + Description + ' ' + Problem + ' ' + Solution + ' ' + Notes Info
from SupportTicketsSummaries st
inner join freetexttable(SupportTicketsSummaries, (ShortSummary, Description, Problem, Solution, Notes), @SearchText) as k on st.ID=k.[key]
order by rank desc

I wrapped this up in a stored procedure, with a parameter for the search text, and breathed a sigh of relief. I'd done it, I was finally ready to import the stored procedure into the Entity Framework model, and get on with implementing the code.

You know what's coming don't you? You should by now.

Fit The Ninth - Trying to find the stored procedure

I went into Visual Studio, opened the .edmx file, right-clicked on the designer surface and chose "Update model from database" as I'd done so many times before. I opened the Stored Procedures section, and scrolled down to my fab new stored procedure... that wasn't there! Huh? Wh'appen?

Back to SSMS to double-check. No, it really is there. Try updating the model again, no joy.

It turns out that you have to grant EXEC permissions on the stored procedure before you can see it in the Entity Framework model. I checked the connection string to see which user was being used to access the database, and granted EXEC permissions.

After a few false starts, I managed to get past this hurdle, only to hit the next one...

Fit The Tenth - Where are my parameters?

One of the nice things about importing a stored procedure into Entity Framework is that the wizard will check what columns are returned, and either create or map a complex entity to match the results. Except it didn't. When I clicked the button to get the schema, I got the grammatically awful message “The selected stored procedure or function returns no columns.” I know they are American, but they could make some effort to write decent English! I know, it's probably decent American, but I wasn't in a mood to argue to the point at this stage!

After a lot of searching, I didn't get very far. I found a lot of people reporting the same problem, but they were all using dynamic SQL or temporary tables. I wasn't using either, so didn't get any benefit from the suggestions.

I never did find an answer to this bit. I wondered if maybe the freetexttable was seen as a temporary table, or maybe it actually is a temporary table. I couldn't find an answer, but I did come across a wonderfully simple way around it.

The facility to detect the schema and generate a complex entity is a nice extra, but you don't actually have to use it. All you need to do is open the Model Browser, right-click on the Complex Types node, and add a new complex type that has properties that match the columns returned by your stored procedure. Make sure the names match the columns returned by your stored procedure exactly.

Then start the function import wizard, choose complex type, and pick the one you just created from the drop-down list.

Fit The Eleventh - Using the new function in the code

This bit turned out to be surprisingly easy, once I had remembered to regenerate the entities. The following code was all that was needed to get the results of the full-text search...

List<SupportTicketsFullTextSearchResults> rankedIDs = ((SalesTrackerCRMEntities)getContext()).SupportTicketsSearch(searchText).ToList();

Armed with this, I was able to pull out the full tickets for those in this query, and deliver them in ranked order.

As a final touch, I normalised the ranks, so that the highest one was always 100, and the others decreased, then used this to give the user an indication of the relative relevance of each ticket...

I showed this to the client, and got a heart-warming enthusiastic response. Made it all worthwhile.

Well, almost!

Fit The Eleventh - Finally reached dry land, none the worse for my adventures

Reminiscent in many ways of the late Mr Ulysses, I had finally made it back to dry land in one piece.

Tuesday, 16 February 2016 17:00:00 (GMT Standard Time, UTC+00:00)

As my regular reader will doubtless remember, I recently blogged about the important lesson I learnt while solving problem 8. I prophetically commented there…

"So, am I going to contemplate my problem domain before diving in and coding next time? Probably not, but at least if I don’t, I might have some idea where to look when the bug reports come in!"

Hmm, I thought I was joking! Well, sadly I wasn’t.

Faced with problem 14, I jumped right in and began coding as usual. My first attempt looked like this…

let collatz n =
  Seq.unfold (fun i -> if i % 2 = 0 then Some(i, i / 2) else Some(i, 3 * i + 1)) n
let collatzLength n =
  (collatz n |> Seq.takeWhile (fun n -> n <> 1) |> Seq.length) + 1

I had tested this on a smaller range, and it worked fine. Remembering what I thought I had learnt from problem 8, I did a quick scan of the range of numbers generated, and satisfied that an int would cope with it, set off to solve the problem…

[1..1000000] |> Seq.map (fun n -> n, collatzLength n) |> Seq.maxBy snd

Whereas this had only taken a second or two on small ranges, it churned away for a very long time when given the full range, ie up to one million. Although a million is quite a lot, it shouldn’t have taken that long to solve.

I tried the problem in C#, and had the same issue…

  int maxColl = 0;
  int maxLen = 0;
  for (int i = 2; i < 1000000; i++) {
    int coll = i;
    int len = 1;
    while (coll != 1) {
      if (coll % 2 == 0) {
        coll = coll / 2;
      } else {
        coll = 3 * coll + 1;
      }
      len++;
    }
    if (len > maxLen) {
      maxLen = len;
      maxColl = i;
    }
  }

Somewhat frustrated and baffled, I gave up and started searching around for other people’s code. I came across a C# solution that looked remarkably like the one above, that ran in about 2.4s. This was even more frustrating.

Eventually, it was pointed out to me that when you run it with the full range of starting points, some of the intermediate numbers generated in the sequence grow larger than the limits of an int, which causes the number to overflow. Under normal circumstances, this doesn’t cause an exception, but means that the number goes negative. Once that happens, the Collatz sequence will never go positive again, so will never terminate (assuming we consider the value 1 as the end of the sequence). This was easily confirmed by adding a “checked” block around the C# code, and seeing the exception thrown. Changing the “int” to “long” in the code above allowed it to give the correct answer in about 2.3s.

So what should I have learnt?

Well, I should have taken more care over my number ranges, just like in problem 8. The sad thing is that I thought I had, but I obviously didn’t check carefully enough.

Thinking about it, when the code took so long, I should have put some logging in there to show where it was up to. That would have shown the problem immediately, as I would have seen the negative values in the sequence. Strike One.

The other point is that it raises the issue of validating your input. If my function had done this, I would have found the problem very quickly. For example, changing my collatz function as follows would have raised the issue as soon as I tried to run it…

let collatz n =
  Seq.unfold (fun i -> 
    if i <= 0 then failwith "The input must be at least 1"
    if i % 2 = 0 then Some(i, i / 2) else Some(i, 3 * i + 1)) n

This sort of issue comes up more often than you might think. As developers, we (and I use the plural deliberately, I’ve seen plenty of others make the same mistakes) bravely assume that the values sent into our functions/methods are within acceptable ranges. When they aren’t, we get exceptions that are often very hard to debug.

Microsoft began to address this issue with Code Contracts. In theory, these are an excellent and easy way to address exactly this problem. In practice, I never found them to work, and gave up. Maybe it’s time to revisit them and try again.

Another day, another lesson ignored!

C# | F# | Project Euler
Thursday, 11 February 2016 17:00:00 (GMT Standard Time, UTC+00:00)
# Sunday, 07 February 2016

Up until now, I have been using Manoli’s code formatter to add code to this site. I’ve had a couple of issues with this, mainly that the line numbers it includes are part of the text, so if you copy the code and paste it into Visual Studio, LinqPad, etc, you get the line numbers as well.

I recently discovered that Google have a code formatter, which seems to do the job nicely, doesn’t require you to paste the code into a formatter before adding it to the site, and uses an order list HTML tag to create the line numbers. It also has a few extra themes, meaning I can colour the code to look more like the dark theme I use in VS.

Here is some C# formatted with the new method…

public long MaxProduct (string s, int n) {
  return Enumerable.Range (0, s.Length - n + 1)
      .Select (i => s.Substring (i, n))
      .Max (s1 => s1.ToCharArray().Aggregate (1, (long a, char c) => a * (c - '0')));
}

Hmm, judging by the preview in Live Writer, it looks pretty awful! Shame, it looked fine in a plain HTML file. Have to post it to the blog and see how it looks there.

Edit: Looked OK. Shame I can't read the code when editing! Let's try some F# and see...

// Find the sum of all primes below two million
let primes max = 
    let array = new BitArray(max, true);
    let lastp = Math.Sqrt(float max) |> int
    for p in 2..lastp+1 do
        if array.Get(p) then
            for pm in p*2..p..max-1 do
                array.Set(pm, false);
    seq { for i in 2..max-1 do if array.Get(i) then yield i }

primes 2000000 |> Seq.map (fun n -> bigint n) |> Seq.sum

OK, I’m convinced!

Sunday, 07 February 2016 17:00:00 (GMT Standard Time, UTC+00:00)

The problem

As I briefly mentioned in my rant about the F# fanboy lies, I have been using Project Euler to help me learn F#. I have got as far as problem 8, which was to find the largest product in a series of digits. To save you the bother of clicking the link, here is the description…

The four adjacent digits in the 1000-digit number that have the greatest product are 9 × 9 × 8 × 9 = 5832.

73167176531330624919225119674426574742355349194934
96983520312774506326239578318016984801869478851843
85861560789112949495459501737958331952853208805511
12540698747158523863050715693290963295227443043557
66896648950445244523161731856403098711121722383113
62229893423380308135336276614282806444486645238749
30358907296290491560440772390713810515859307960866
70172427121883998797908792274921901699720888093776
65727333001053367881220235421809751254540594752243
52584907711670556013604839586446706324415722155397
53697817977846174064955149290862569321978468622482
83972241375657056057490261407972968652414535100474
82166370484403199890008895243450658541227588666881
16427171479924442928230863465674813919123162824586
17866458359124566529476545682848912883142607690042
24219022671055626321111109370544217506941658960408
07198403850962455444362981230987879927244284909188
84580156166097919133875499200524063689912560717606
05886116467109405077541002256983155200055935729725
71636269561882670428252483600823257530420752963450

Find the thirteen adjacent digits in the 1000-digit number that have the greatest product. What is the value of this product?

Apart from the fact that this was an interesting problem, I learnt a very important lesson from this one, and thought it worth sharing.

Solving the problem - or not!

My initial stab at this looked like this…

   1:  let chop n (s : string) =
   2:    [ for i in [0..(s.Length - n)] do yield s.[i..(i + n - 1)]]
   3:  let product (s : string) =
   4:    s |> Seq.fold (fun p c -> p * (int (string c))) 1
   5:  let lgstProduct n (s : string) =
   6:    s |> chop n |> Seq.map product |> Seq.max

The chop function chops the string into chunks of length n, the product function calculates the product of the digits (assuming that the string passed in only contains numbers of course), and the lgstProduct function sticks these together to find the maximum product.

I tried this with the 1000 digit number passed as a string, and using 4 for the chunk size, and it produced the right answer, 5832. Wanting to make the code shorter and neater, I included the two helper functions in the main one, and managed to come up with this...

   1:  let largestProductInt64 (n : int64) (s : string) =
   2:    [ for i in [0L..((int64 s.Length) - n)] do yield s.[(int i)..int(i + n - 1L)]]
   3:    |> Seq.map (fun s -> s, s |> Seq.fold (fun p c -> p * (int64 (int (string c)))) 1L)
   4:    |> Seq.maxBy snd

Note that I changed the code to give me a tuple, containing both the highest product, and the n-character chunk that produced it. Chuffed to bits, I threw the number 13 at it, and got the answer ("9781797784617", 2091059712) , which I duly plugged into the answer box on the Project Euler site, only to be told that it was wrong! What a chutzpah! Of course it’s right, my code works!

Or does it?

So what went wrong?

Having spent quite a bit of time testing my code, and convincing myself that it was right, I resorted to searching for other people’s answers to the same problem. Along the way, I came across someone who had had exactly the same problem as me, albeit in C++, and had come up with the same (wrong) answer.

It turns out that the issue was simple. When multiplying 13 digits together, you could potentially end up with 9^13, ie 2,541,865,828,329. Given that the maximum number that can be stored in the .NET int type is 2,147,483,647 the problem becomes apparent.

I changed my code to use int64, which is the F# equivalent of the .NET “long” type, and can hold numbers up to 9,223,372,036,854,775,807. Lo and behold, project Euler acquiesced, and accepted my answer.

In order to make my code even more general, I actually changed it to use bigint, which can hold any size of integer, but the point I want to take away from this remains the same…

What I learnt in school today

I think there is a very important lesson here. Like many of us, I piled in and started coding without really thinking about the problem. What I should have done is take a look at the problem domain, and think it through. It should have been obvious that the eventual product was going to be too large to fit into a 32-bit integer, which is probably why the Project Euler people chose such a large number in the first place. Had I done that, I would probably have got the right answer first time.

Now, I don’t know about you, but I almost never get these sorts of interesting problems in my day job. I usually get “Pull the data from the database, display it on a window, wait for the user to make changes and then save it,” which is significantly less interesting. However, I think the basic point remains valid. Without thinking through the scope of the problem, and the bounds of the domain, it’s very easy to pile and and get coding, whilst introducing all sorts of subtle bugs. My tests worked absolutely fine, simply because I was testing on small numbers. How many times do we developers test our code against a Noddy database, mainly to save development time? No need to put your hands up, we’re all guilty.

Had my largest product function been production code, I would have released a bug that would only have been spotted some time down the line. Depending on how easy/hard it would be to predict the right numbers, it’s possible that it might not have been spotted for a long time. People would just assume that the number produced was correct.

So, am I going to contemplate my problem domain before diving in and coding next time? Probably not, but at least if I don’t, I might have some idea where to look when the bug reports come in!

Improving the code

Having sorted all that out, I asked for a code review, and came across a really useful F# function that I hadn’t seen before. My chop function, included as the first line of my slimline largestProduct function split the input string into a sequence of chunks of length n. It turns out that F# has the Seq.windowed function that does exactly the same thing, but is more readable.

I also got a slightly better understanding of function composition, and saw how to reduce the number of brackets needed to convert the character to a bigint. I ended up with…

   1:  let largestProduct n (s : string) =
   2:    Seq.windowed n s
   3:    |> Seq.map (fun s -> s, s |> Seq.fold (fun p c -> p * (string >> bigint.Parse) c) 1I)
   4:    |> Seq.maxBy snd

I was quite pleased with this. A lot of functionality in four lines.

Solving the problem in C#

I was interested to see if I could solve the problem in C# as well, so I fired up LinqPad and jumped in. My initial version (including the extra bits need to run it in LinqPad, and the line to write out the result) looked like this…

   1:  void Main() {
   2:    string s = "7316...3450"; // NOTE: Snipped for brevity!!
   3:    int n = 13;
   4:   
   5:    var maxProduct = MaxProduct(s, n);
   6:    Console.WriteLine ("1) Max product is " + maxProduct.Item2 + " from " + maxProduct.Item1);
   7:  }
   8:   
   9:  public Tuple<string, long> MaxProduct(string s, int n) {
  10:    return Chop (s, n)
  11:      .Select (s1 => new Tuple<string, long> (s1, Product (s1)))
  12:      .OrderByDescending (t => t.Item2)
  13:      .First();
  14:  }
  15:   
  16:  public long Product (string s) {
  17:    long res = 1;
  18:    for (int i = 0; i < s.Length; i++) {
  19:      res *= Convert.ToInt32 (s [i].ToString());
  20:    }
  21:    return res;
  22:  }
  23:   
  24:  public IEnumerable<string> Chop (string s, int n) {
  25:    for (int i = 0; i < s.Length - n + 1; i++) {
  26:      yield return s.Substring (i, n);
  27:    }
  28:  }

Hmm, quite a lot of code there. Looks like F# really is shorter and cleaner!

There must be a way to improve this. A few moments’ thought made me realise that the Product() method is really doing what the Linq Aggregate() extension method does. Also, the Chop() method could easily be done with Linq if I fed in a range of numbers for the starting positions of the substring (like I did in my original F# code).

After a short bit of fiddling, I came up with this rather improved C# version…

   1:  public long MaxProduct (string s, int n) {
   2:    return Enumerable.Range (0, s.Length - n + 1)
   3:        .Select (i => s.Substring (i, n))
   4:        .Max (s1 => s1.ToCharArray().Aggregate (1, (long a, char c) => a * Convert.ToInt64 (c.ToString())));
   5:  }

That's much better! Once you ignore the extraneous bits, the body of the actual method is only three lines, a mere one line longer than the F# version. The F’'# is definitely cleaner, but as I’ve mentioned before, that’s not always an advantage.

After passing this problem around the team, one of the brighter sparks came up with an even shorter version that runs faster…

   1:  public long MaxProductEC (string s, int n) {
   2:    return Enumerable.Range (0, s.Length - n + 1)
   3:      .Max (i => s.Substring (i, n).Aggregate ((long)1, (a, c) => a * (c - '0')));
   4:  }

I defy anyone to tell me that C# is verbose! Don’t get me wrong, I’m really enjoying F#, but the lies are getting on my nerves!

All in all, an interesting exercise.

C# | F# | Project Euler
Monday, 01 February 2016 02:53:00 (GMT Standard Time, UTC+00:00)