Select MIN In LINQ

I recently had to take a set of swim time data for an entire season and determine the lowest time for each swimmer.  The TSQL in me thought “this is a snap – just do this:”

Select SwimmerID, MIN(Time) from dbo.tblTimesIndividual where MeetID > 74 and RaceStrokeID = 4 Group By SwimmerID

However, when I tried to implement in LINQ, things went bad pretty quickly.  I stumbled around with the language extensions for a bit before raising the white flag with MSDN.  The easiest implementation that I found was this:

var minQuery = from times in entities.tblTimesIndividuals where times.MeetID > 74 && times.RaceStrokeID == 4 group times by times.SwimmerID into grouping select new { grouping.Key, LowestFreeStyleTime = from times2 in grouping where times2.Time == grouping.Min(times3 => times3.Time) select times2 };

Yikes!  Since it worked, I kept it – however, I do wonder if there is an easier implementation.   I wonder if there is a tool to reverse engineer TSQL to LINQ – like you can look at the TSQL generated from EF when you send using SQL Profiler (which I can’t use on my remote server) or the ToTraceString() method like so:

string sql = ((ObjectQuery)freeMinQuery).ToTraceString();

The key thing is to remember that ObjectQuery is in System.Data.Objects so I had to add a using statement for that.  In any event, check out the TSQL:

SELECT [Project1].[SwimmerID] AS [SwimmerID], [Project1].[C1] AS [C1], [Project1].[TimesIndividualID] AS [TimesIndividualID], [Project1].[SwimmerID1] AS [SwimmerID1], [Project1].[MeetID] AS [MeetID], [Project1].[AgeGroupID] AS [AgeGroupID], [Project1].[RaceStrokeID] AS [RaceStrokeID], [Project1].[RaceTypeID] AS [RaceTypeID], [Project1].[RaceLengthID] AS [RaceLengthID], [Project1].[Lane] AS [Lane], [Project1].[Place] AS [Place], [Project1].[Time] AS [Time], [Project1].[TimerID] AS [TimerID] FROM ( SELECT [GroupBy1].[K1] AS [SwimmerID], [Extent2].[TimesIndividualID] AS [TimesIndividualID], [Extent2].[SwimmerID] AS [SwimmerID1], [Extent2].[MeetID] AS [MeetID], [Extent2].[AgeGroupID] AS [AgeGroupID], [Extent2].[RaceStrokeID] AS [RaceStrokeID], [Extent2].[RaceTypeID] AS [RaceTypeID], [Extent2].[RaceLengthID] AS [RaceLengthID], [Extent2].[Lane] AS [Lane], [Extent2].[Place] AS [Place], [Extent2].[Time] AS [Time], [Extent2].[TimerID] AS [TimerID], CASE WHEN ([Extent2].[TimesIndividualID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]FROM (SELECT [Extent1].[SwimmerID] AS [K1], MIN([Extent1].[Time]) AS [A1]FROM [dbo].[tblTimesIndividual] AS [Extent1]WHERE ([Extent1].[MeetID] > 74) AND (4 = [Extent1].[RaceStrokeID])GROUP BY [Extent1].[SwimmerID] ) AS [GroupBy1]LEFT OUTER JOIN [dbo].[tblTimesIndividual] AS [Extent2] ON ([Extent2].[MeetID] > 74) AND (4 = [Extent2].[RaceStrokeID]) AND ([GroupBy1].[K1] = [Extent2].[SwimmerID]) AND ([Extent2].[Time] = [GroupBy1].[A1])) AS [Project1]ORDER BY [Project1].[SwimmerID] ASC, [Project1].[C1] ASC

 

Yikes!  I’ll continue to search for a tool that can give hints to make my LINQ more clear and have it generate better TSQL.

Advertisements

Windows Phone 7 Design Time and Asynch Initialization

I am working on a Windows Phone 7 app and I ran into an interesting problem.  The architecture of the app is MVVM and the ViewModel is a ‘Threat’.  When consuming the ThreatViewModel in my Vi,ew I havew the following code:

image

This fails because the TheatViewModel created a proxy to consume a WCF service. I made the WCF-creating method, made it public, and called it during the initialization of the page:

public CurrentStatusView() { InitializeComponent(); ThreatViewModel threatViewModel = (ThreatViewModel)this.DataContext; threatViewModel.InitializeLocations(); }

The problem is that consuming the control – the initialize component gets called and this is the error message:

image

Since initialize component is getting called in deign time, the asynch is getting kicked off – and the designer is throwing an exception.  Rob Seder seems to think it is a .config issue – because the .config is not present in design time, the proxy doesn’t have the information to make the proxy call.

Stepping back, the proxy and asynch call needs to happen outside of the designer – at some point in the code behind to respond to an event on the screen. Perhaps application load or page load.  In any event, merging asycnh calls is not a good idea…

The power of separation

I am getting ready to deploy my 1st Windows Phone 7 app. The application consumes some data that is served up by a WCF service that I wrote. Until today, I had been testing the application using some dummy data that I hard-coded into the UI.

To add the WCF service, I added a new WCF project to my solution (and a supporting data project that uses Entity Frameworks). I ran into the same problem as I encountered last year that I forgot about – when you add a reference to a project that uses Entity Frameworks, you need to add a reference to System.Data.Entities in the consuming project. If not, you can see the business classes but the context will not work. Unfortunately, the only way to see this error it to hard-code the context into your consuming project, hit F5, and get this message:

'System.Data.Objects.DataClasses.EntityObject' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

Once I figured that out, I whipped up a basic method using some LINQ like so:

public Location GetLocation(int locationId) { var q = from l in context.RoadAlert_Location where l.LocationId == locationId select l; return MapLocation(q.FirstOrDefault()); }

Easy enough, I then added my interface:

[ServiceContract(Namespace="http://schemas.tff.com/2011/06/13/RoadAlert.Services")] [ServiceKnownType(typeof(Location))] [ServiceKnownType(typeof(List<Location>))] public interface IRoadAlert { [OperationContract] Location GetLocation(int locationId); [OperationContract] List<Location> GetLocations(int alertTypeId); }

And then published the service to my WinHost server. It published fine and I got the WSDL no problem. However, after I added a reference, I could not create a proxy class. I checked the clientConfig and I was surprised because it was empty:

image

I did a quick search and found posts like this one. I made sure I was using BasicHTTPBinding like so:

image

However, it still was not working. I would see the service reference and the wizard exited with no errors. However, when I tried to create the proxy, I got an exception.

I then decided to take a step back and separate the service and data projects from my solution – instead of deplying to WinHost. I started the service project and then tried to add a reference from the client. Once I did that, I got this in my client config:

<configuration> <system.serviceModel> <bindings> <basicHttpBinding> <binding name="BasicHttpBinding_IRoadAlert" maxBufferSize="2147483647" maxReceivedMessageSize="2147483647"> <security mode="None" /> </binding> </basicHttpBinding> </bindings> <client> <endpoint address="http://localhost:52296/RoadAlert.svc" binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_IRoadAlert" contract="Tff.RoadAlert.Services.IRoadAlert" name="BasicHttpBinding_IRoadAlert" /> </client> </system.serviceModel> </configuration>

Magic! I have no idea why/how it now works, but separating the projects did the trick. I then could create my proxy locally. Then to deploy, I just published the service and changed the endpoint address on the client. I found this Microsoft page that details many of the problems and steps I went through.

Another interesting note is that you can’t do a synchronous call in Win Phones – I Guess they all have to be asynch…

Old English NLP

I started working through the SharpNLP library as referenced in my last blog post. To recap, the SharpNLP library depends on some source binaries from the SharpEntropy library which depend on sonce data files that have to be placed in a specific location on your file system.

I cracked open the SharpNLP with reflector and took a spin through the more commonly used methods. It looks like you deal with strings and a couple of classes called “chunk” and “word” that have a couple of supporting enumerations.

With the library, you can split paragraphs into sentences, sentences into words, sentences into phrases (what they call chunks), and then identify each word and/or phrase with its part of speech. These functions seem perfect for my Old English translator.

Here is an example of a basic console application with the parser output.

public class Program { static void Main(string[] args) { Console.WriteLine("--Start--"); string testSentence = "A quick brown dog jumped over a lazy cat."; Parse parse = ParseSentence(testSentence); PrintParse(parse); Console.WriteLine("---End---"); Console.ReadLine(); } private static Parse ParseSentence(string sentence) { string path = @"C:\ProgramData\OpenNLP\Models\\"; EnglishTreebankParser parser = new EnglishTreebankParser(path, true, false); return parser.DoParse(sentence); } private static void PrintParse(Parse parse) { Parse rootNode = parse.GetChildren()[0]; Parse[] parses = rootNode.GetChildren(); foreach (Parse currentParse in parses) { Console.WriteLine(String.Format("{0} : {1}", currentParse.ToString(), currentParse.Type)); } } }

image

 

I then wanted to change the order from S-V-O to S-O-V or O-S-V. In this example, the output should be “A quick brown dog over a lazy cat jumped.” or even “Over a lazy cat a quick brown dog jumped.”

Looking at the output, the VP needs to be broken down even further. I changed the PrintParse to handle the children (using recursion):

private static void PrintParse(Parse parse) { Parse[] parses = parse.GetChildren(); foreach (Parse currentParse in parses) { Console.WriteLine(String.Format("{0} : {1}", currentParse.ToString(), currentParse.Type)); PrintParse(currentParse); } }

The output is now:

image

Things looks better – except I can’t figure out what the “TK” is – it is duplicating each word and it is not a part of speech (at least according to the WordType enum)…

Removing the TK, I get this (I dropped the Console window and went to the Output window) :

A quick brown dog jumped over a lazy cat . : S

A quick brown dog : NP

A : DT

quick : JJ

brown : JJ

dog : NN

jumped over a lazy cat : VP

jumped : VBD

over a lazy cat : PP

over : IN

a lazy cat : NP

a : DT

lazy : JJ

cat : NN

. : .

So to take a sentence and take it from S-V-O to O-S-V, I need to map the parsing word types to either a S,V,or O. In this example:

  • NP = S
  • VBD = V
  • PP=O

I can then rearrange the words into Old English. The challenge is that the word types are not context-free – the 1st NP that is in the sentence (“A quick brown dog”)is the Subject, but the second NP (“a lazy cat”) is the object – the PP is for the prepositional phrase. So the 1st NP that is encountered is the subject and the second NP is ignored if it is in a PP, but if it is alone (“A quick brown dog jumped a lazy cat”), then it becomes the object. I can build a table with the different patterns – at least the common ones, and leave it at that. Here is an example of the function (yes, I know this is procedural code at its worst, I’ll refactor once I have all of my thoughts down)

private static void AssignOldEnglishOrder(List<Phrase> phrases) { int nounCount = 0; int verbCount = 0; int objectCount = 0; foreach (Phrase phrase in phrases) { switch (phrase.ParseType) { case "NP": if (nounCount == 0) { phrase.SentenceSection = "S"; } else if (nounCount == 1) { phrase.SentenceSection = "O"; } else { phrase.SentenceSection = "Z"; } nounCount++; break; case "VBD": if (verbCount == 0) { phrase.SentenceSection = "V"; } else { phrase.SentenceSection = "Z"; } verbCount++; break; case "PP": if (objectCount == 0) { phrase.SentenceSection = "O"; } else { phrase.SentenceSection = "Z"; } objectCount++; break; default: break; } } }

I then whipped up a function that spits out the phrase in Old-English:

private static void PrintPhrases(List<Phrase> phrases) { StringBuilder stringBuilder = new StringBuilder(); foreach (Phrase phrase in phrases) { switch (phrase.SentenceSection) { case "O": stringBuilder.Insert(0, phrase.Text); break; case "S": if (stringBuilder.Length > 1) stringBuilder.Insert(1, phrase.Text); else stringBuilder.Append(phrase.Text); break; case "V": stringBuilder.Append(phrase.Text); break; case "Z": break; default: stringBuilder.Append(phrase.Text); break; } } Debug.WriteLine(stringBuilder.ToString()); }

And the output looks like this:

over a lazy catA quick brown dogjumped

My next steps are:

  • Spacing between words
  • Sentences formatted correctly (all lower case until final output)
  • Find..Replace key words for Old English
  • More patterns to match O-S-V from a variety of input sentences
  • Handle the apostrophe (Don’t comes down as [Don] ‘[t] from the parser)
  • Refactor using Unit tests to better patterns (strategy seems appropriate here)
  • Stick into a WCF service
  • Probably a bunch more stuff that I haven’t thought of

That’s it. I’ll pick it up more later.

Natural Language Parsing

I wanted to go back to a project that I started working on <gulp> three years ago. There is a great website called the Efanchiser that translates English into Jive/VallyGirl/Sweedish Chef. There are some knock-off sites like this one, but I prefer the original. When you go through the header file (written in C) of the translation methods (not the page’s html), you can see this comment:

/* chef.x - convert English on stdin to Mock Swedish on stdout * * The WC definition matches any word character, and the NW definition matches * any non-word character. Two start conditions are maintained: INW (in word) * and NIW (not in word). The first rule passes TeX commands without change. * * HISTORY * * Apr 26, 1993; John Hagerman: Added ! and ? to the Bork Bork Bork rule. * * Apr 15, 1992; John Hagerman: Created. */

Some guy at AT&T back in the 80s came up with the code – check out this comment for the language parsing. Looks like Google and Microsoft weren’t the 1st to come up with the idea about parsing English speech 😉

/* Copyright (c) 1989 AT&T */ /* All Rights Reserved */ /* THIS IS UNPUBLISHED PROPRIETARY SOURCE CODE OF AT&T */ /* The copyright notice above does not evidence any */ /* actual or intended publication of such source code. */

The actual .c file has the logic like this:

# line 28 "chef.l" { BEGIN NIW; i_seen = 0; printf("%c\nBork Bork Bork!",yytext[0]); } break; case 4: # line 31 "chef.l" ECHO; break; case 5: # line 32 "chef.l" ECHO; break; case 6: # line 34 "chef.l" { BEGIN INW; printf("un"); } break; case 7: # line 35 "chef.l" { BEGIN INW; printf("Un"); } break; case 8:

Basically, it is one big Select…Case statement with certain words/phrases replaced and throwing the words “Bork Bork Bork” at the end. The implementation uses word positioning like this:

0}; # define YYTYPE unsigned char struct yywork { YYTYPE verify, advance; } yycrank[] = { 0,0, 0,0, 1,7, 0,0, 0,0, 0,0, 0,0, 0,0, 0,0, 0,0, 0,0, 1,8, 9,34, 0,0, 0,0, 0,0, 0,0, 0,0, 0,0, 0,0, 15,39, 0,0, 0,0, 0,0, 0,0, 0,0, 0,0, 0,0, 0,0, 15,0, 0,0, 39,0, 0,0, 1,7, 1,9, 0,0, 0,0, 0,0, 0,0, 0,0, 1,10, 0,0, 0,0, 0,0, 0,0, 0,0, 3,7, 0,0

Which is pretty cool but really hard to duplicate or extend.

I wanted to add an old-english translator. Instead of adding to the C++ project, I thought of creating a WCF Service that creates an Old-English translation using C#.

My first step was to find a natural language parser that can break the input sentence into its words and tag each word as a part of speech. A quick bing search found a couple of options:

· This site has what I am looking for – but he does not expose the tokenizing as a web service or provide the API source code. I really am not interested in parsing the html so I moved on.

· The Sharp Natural Language Parser (SNLP) found on the codeplex seems to fit the bill – but it is complex to implement and it is a very brittle solution – it relies on a third party library called Maximum Entropy Models (MEM) that has to have a certain folder structure created. The SNLP has a database alternative (called Wordnet Lexical Database) but that link is not working.

· This site has what I am looking for – but he wants to charge a fee to use it. No thanks.

· There is another codeplex project that uses Pyton called ConceptNetUtils found here. I really don’t want to learn Python for this.

There are more hits, but the quality of the sites gets worse and worse.

Biting the bullet, I decided to use the SNLP project from codeplex and therefore use the MEM on my local file system. A great example is found here. Perhaps as a follow up project I will abstract the MEM from the file system. I did have to peek at the MEM to see if it would be an easy port – here is one of the files in notepad:

image

Bummer – looks like that project will be harder said than done….

So I added the directories to my source folder and hit F5 – sure enough it worked. I then started to dive into the examples and see how the API was structured. That is the subject of my next post.

To get an idea of how to make an English phrase an old English phrase, I turned to Bing. There are a couple of sites that translate individual words (like this one). I also got a laugh because there are TONS of sites that willtranslate Shakespeare to modern English – doubtlessly helping High School English students everywhere.

There are a couple of components:

· Rearrange the sentence structure as recommended here

· Find..replace certain words/phrases as found here

There are obviously other things I could do to make the Old English better, but I thought I would start with the basics.