Dabas thinks…

LINQ looks like fun!

Being able to use a SQL similar language for all sorts of different objects really sounds quite exciting, and I just was hoping the opportunity would arise for me to make use of it.

That opportunity came with the need to compare incoming files from a business partner at work.

Essentially we receive files from this particular partner in two folders. Lets name them folder A and folder B. The files in folder A have meaningful information for us, but the files in folder B are simply useless. I wanted to create weekly statistics so I could prove to our contacts at the partner company that over 90% of the files received were of the second type, and maybe they could do something about either joining them into a daily file, or stop sending them all together.

Perfect scenery for the use of LINQ, right?

Assuming the file name and creation date for one of the folders were stored in a SQL table, what I was after would be something like:

SELECT COUNT(*) FROM FolderA where CreationDate > DateAdd(Week, -1, GetDate())

And maybe go one step further: Add grouping, to find out how many files arrived on the different days of the week. (How many on Monday, on Tuesday, etc)

SELECT COUNT(*), DATEPART(dw, CreationDate) as DayOfWeek 

FROM FolderA where CreationDate >= DateAdd(Week, -1, GetDate())

GROUP BY DATEPART(dw, CreationDate)

Furthermore, SQL lets me use the TOP keyword, in case I want the statistics to encompass more than the last week. Maybe I can do my statistics based on the last 10% of data, or so

Now.. I do not have this SQL table, so.. will LINQ be able to give me the same functionality?

First attempt:

   1: private static void GetStatistics(DateTime FromDate)

   2: {

   3:     string Location = ConfigurationManager.AppSettings["InboxLocation"];

   4:     DirectoryInfo diInbox = new DirectoryInfo(Location);

   5:     FileInfo[] InboxFiles = diInbox.GetFiles();

   6:     var files = from file in InboxFiles

   7:                 where file.CreationTime >= FromDate 

te

   8:                 select file;

   9:     int count = files.Count();

  10:     Console.WriteLine("Inbox Count: {0}", count);

  11:     Console.ReadLine();

  12: } 

It works!!!

Try it on your own system, it seems to be quite OK.  But on my work situation, where there are thousands of files in the folder… it is SLOW. (Take into account that folder B receives over 1000 files a day, so that the number of files is gigantic.. and growing each week)

Now, in SQL we can create an index, hence we do expect results to be returned within a reasonable time of seconds. But the file system, as far as I know is not built that way.

And although I specifically have a where clause in line 7, single stepping through the code reveals two facts:

1. The LINQ statement actually only gets executed when line 9 gets hit.
2. Even though I have a where clause in line 7, ALL Files get scanned through the LINQ statement. The where clause only influences the results that are inserted into var files.

This is the LINQ equivalent of the grouping SQL statement I posted earlier on. (Again, at work it is very slow, defeating its purpose)

   1: private static void GetStatistics(DateTime FromDate)

   2: {

   3:     DateTime dt = DateTime.Now;

   4:     string Location = ConfigurationManager.AppSettings["InboxLocation"];

   5:  

   6:     DirectoryInfo diInbox = new DirectoryInfo(Location);

   7:     FileInfo[] InboxFiles = diInbox.GetFiles();

   8:     var files = from file in InboxFiles

   9:                 where file.CreationTime.CompareTo(FromDate) >=0 

  10:                 group file by file.CreationTime.Date into g

  11:                 select new

  12:                 {

  13:                     Count = g.Count(),

  14:                     Date = g.Key

  15:                 };

  16:  

  17:  

  18:     foreach (var g in files)

  19:     {

  20:         Console.WriteLine("{0} {1}", g.Date, g.Count);

  21:     }

  22:     

  23:     Console.WriteLine(DateTime.Now - dt);

  24:     Console.ReadLine();

  25: }

Maybe the solution will be to create two LINQ statements, with the first one decreasing the size of the sample data? How does one translate the T-SQL TOP keyword?

I placed a question on Experts-Exchange asking for help, and learned about Take() (Click here to see the question)

This was my attempt using Take():

   1: private static void GetStatistics(DateTime FromDate, int Top)

   2: {

   3:     try

   4:     {

   5:         string Location = ConfigurationManager.AppSettings["InboxLocation"];

   6:  

   7:         DirectoryInfo diInbox = new DirectoryInfo(Location);

   8:         FileInfo[] InboxFiles = diInbox.GetFiles();

   9:  

  10:         //First we get the files and select only their dates while we order them descending

  11:         var files = from file in InboxFiles

  12:                     orderby file.CreationTime.Date descending

  13:                     select new

  14:                     {

  15:                         Date = file.CreationTime.Date

  16:                     };

  17:  

  18:         //Attempt using TakeWhile.. did not work

  19:         //var files1 = files.TakeWhile((file, Date) => (file.Date >= FromDate && file.Date <= ToDate));

  20:  

  21:         //We take a subset by using Take. Top is the number of files we want to restrict our search on

  22:         var files1 = files.Take(Top);

  23:  

  24:         //We group the results just found

  25:         var files2 = from file in files1

  26:                      where file.Date >= FromDate 

  27:                      group file by file.Date into g

  28:                      

  29:                      select new

  30:                     {

  31:                         Count = g.Count(),

  32:                         Date = g.Key

  33:                     };

  34:  

  35:         foreach (var g in files2)

  36:         {

  37:             Console.WriteLine("{0} {1}", g.Date, g.Count);

  38:         }

  39:         Console.ReadLine();

  40:     }

  41: }

At first I thought that this really improved the time considerably, but after testing further, it was disappointing to concede, that no matter what I am doing, LINQ will traverse through the whole gigantic folder.

The answer is simple: I have to create an index somehow. I have been told that in LINQ you can add your own providers, and probably handle fileinfo indexing there. I have not yet reached such an advanced LINQ stage yet, and probably it will be easier to just load the whole folder into a SQL table, set up an index there, and let SQL do what it knows yet.

I still think LINQ looks like fun!

This is a day of firsts.

Not only my first technical blog entry, but also my first .NET 3.5 program, and the first time I use LINQ

I installed Visual Studio 2008 immediately after the Heroes Happen {Here} event in Sydney, but have mostly used it to edit and add to existing .NET 2.0 applications.

LINQ has intrigued me for a while, specially LINQ to XML, but all the demos I have seen so far have been relevant to LINQ to SQL and LINQ to Objects.

Then yesterday, I received an email from Experts Exchange asking me to help out with a question on XML.

I have been a member of Experts Exchange since 2003, when I was struggling with the steep learning curve from VB6 to .NET.

And this learning curve was a hard one to master: Today you just Google a few keywords and you probably will find at least two good examples that will answer any question you have. Not so in those days. The books on .NET were just thick hard copies of the help system, with hardly any good examples to choose from.

My first experts exchange question simply stated: How do I read data from a particular source, then update the same source, or another one? Today this would have been considered a naive question, but in those days, moving from an ADO frame of mind to the disconnected DataSet concept was not easy.

Getting back to the point:

In this particular question, the asker is in a similar situation: He is trying to use LINQ to do the simple task of adding an XmlNode to an existing document. He knows a little LINQ using VB.NET, but is stuck and does not understand the exception his code is causing.

Sounds so simple an easy!

But after two days, and two calls for help from more experienced experts who received an email similar to the one I got, the question still was unanswered.

"Well", I thought to myself, "it is time to get myself acquainted with the new technology". Best way to learn to swim is to jump into the pool.

First Step: Google for "LINQ to XML"

2 minutes later I link to "LINQ to XML – 5 Minute Overview". Sounds quite promising! OK, so now we have XElements and XAttributes instead of XmlElements and XmlAttributes. Must be easy then to just solve the problem with the ‘ol XmlDocument type of objects, but using instances of these classes instead.

First try:

Use part of the asker’s code:

Dim xmlTrack as XElement=
                      <track id="45"> <trackId>45</trackId> </track>

Dim doc as XmlDocument = new XmlDocument() ‘Now lets see how I add an XElement to it.

Intellisense, help me out!!!  No luck…. I am still using the older classes!

Ahh… It is XDocument I have to use… makes sense!

Second try

Dim doc as XDocument = new XDocument()

doc.Load(sPath) ‘sPath points to the document which starts with <tracks><track>….

doc.Add(track) ‘Geee.. This is sooo easy!!! Lets test it and claim victory

Oh Nooo!!!… InvalidOperationException was unhandled. This operation would create an incorrectly structured document. 😦

Ok.. that makes sense. The DocumentElement is <tracks>…</tracks>, and I am adding <track> to the end of the document, hence the document will not have one root element, and hence it is not structured properly.
What I need to do is to add a child to <tracks>… should be easy…. but… There is no .AddChild, or .Children in intellisense!!! mmmm….

Intellisense aided attempts:

doc.AddAfterSelf(track)
doc.AddFirst(track)
doc.FirstNode.AddAfterSelf(track)
doc.LastNode.AddBeforeSelf(track)

All fail. In short, they all try to do the same thing as before as the Documents first (and last) node is still <tracks>. GRRRR. How do I get to the inner <track> node and then use AddAfterSelf or AddLast?

Time to Google for an answer: "XElement first child Dim" (Adding Dim guarantees VB.NET examples, use C# in the search otherwise)

LINQ To XML Samples – DML give me the clue I need and the final code looks like this:

Dim sPath = "tracks.xml"
Dim doc As XDocument

If File.Exists(sPath) Then
    doc = XDocument.Load(sPath)
    Dim track As XElement =
                            <track id="45">
                                <trackId>45</trackId>
                            </track>

    ‘Dim Tracks = doc.<tracks> ***** First attempt. Still does not work. Still same problem as above ******
    Dim Tracks = doc.<tracks>.<track> ‘It is so easy once you get the hang of it! It still is confusing though, but you should compare this with an XPath //tracks/track statement, which will return all <track> nodes
    Tracks.Last().AddAfterSelf(track)
    doc.Save(sPath)
End If

The complete question and solution at Experts Exchange can be seen here