c#

LINQ Group by MAX Date Query

Matt Simner C#, LINQ ,

I’ve found some weird, wonderful and ridiculously complicated LINQ queries for getting the row with MAX(DATE) based on a key.  Most unnecessarily use lambda expressions, and some just had several interim steps.  I knew there had to be a better way, and found an unassuming post at the bottom of a StackOverflow page.

Here’s my non-lambda’d, contrived example…  assuming you’ve got an EntityFramework model (i.e. context)

            //Get Client Order with (max) order date 
            var maxclientOrder = (from clientOrder in context.ClientOrders
                               where clientOrder.OrderDate ==
                               (from clientOrder2 in context.ClientOrders
                                where clientOrder2.ClientID == clientOrder.ClientID
                                    select clientOrder2.OrderDate).Max()
                                select clientOrder).ToList();

Pass in any parameter to an InfoPath form with one piece of code

Matt Simner InfoPath ,

One thing that’s lacking in Microsoft InfoPath 2007 is the ability to simply map input parameters to the main data source (which is most likely where you want them to go). 

Unfortunately there’s no getting around the need to write code to ‘receive’ your input parameters, but with a thought you’ll be able to pass
in parameters with the same name as the fields in your form and have one block of code to paste into the code behind all forms – that will work for all. 

This includes a couple of utility functions that make life a bit easier when coding around fields in the form.
The ‘DeleteSelf’ line around the nil attribute is something that apparently gets around data type errors if you’ve got fields that aren’t just ‘string’ – e.g. ‘number’ etc.  I found this worked in the InfoPath client, but not in a browser (and had to change my field data type back to string, and add some regexp validation).

        public void FormEvents_Loading(object sender, LoadingEventArgs e)
        {

            //Try and identify any input parameters and put them into their requisite places in the main data source
            foreach (string parameter in e.InputParameters.Keys)
            {

                //Try and find in the main data source, then set the value
                XPathNavigator formNode = SelectSingleNode(String.Format("//my:{0}", parameter));
                if (formNode != null)
                {
                    if (formNode.MoveToAttribute("nil", "http://www.w3.org/2001/XMLSchema-instance"))
                        formNode.DeleteSelf();

                    formNode = SelectSingleNode(String.Format("//my:{0}", parameter));
                    formNode.SetValue(e.InputParameters[parameter]);
                }
            }
        }
        
        /// <summary>
        /// Select a single node from the Main data Source
        /// </summary>
        /// <param name="xpath"></param>
        /// <returns></returns>
        private XPathNavigator SelectSingleNode(string xpath)
        {
            string ns = LookupNamespace("my");
            XPathNavigator navigator = MainDataSource.CreateNavigator();
            return navigator.SelectSingleNode(xpath, NamespaceManager);
        }
 
 
        private string LookupNamespace(string ns)
        {
            return NamespaceManager.LookupNamespace(ns);
        }

LINQ to SQL Connection Strings with Class Library and Web.Config

Matt Simner .NET Framework, C#, LINQ , ,

Most Microsoft technologies that you can operate with a GUI come with some tradeoffs.  Things have certainly improved over the years and now something like the LINQ to SQL designer is pretty trouble free – unless of course you have something like this fairly common scenario:

I had a class library (Data Access), and decided to add LINQ to SQL classes for a new database that was being introduced.
This class library is also ultimately being consumed by WCF web services.  I have dev, test, prod environments, so I use ASP.NET Web Deployment projects to change configuration per environment for things like appSettings and connectionStrings.

It therefore followed that I wanted to configure the LINQ DataContext connection properties in web.config.  Out of the box you’ll find your connection properties go into your Settings properties class, which gets a little bit in the way.

If you start playing around with the generated classes to change where you’re getting the connection info from then any changes in the designer will wipe them out, so a (relatively) pain free approach to setting your connection safely is the following:

Go to your LINQ to SQL designer and remove the Connection String, and set Application Settings to False

Create a new partial class to mirror your DataContext, and set the constructor to retrieve from your alternative source…

using System;
using System.Linq;
using System.Configuration;
using System.Data.Linq;

namespace CodeBureau.Services.DataAccess
{
    public partial class MyDataContext : DataContext
    {
        public MyDataContext()
            : base(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString, 
              mappingSource)
        {
            OnCreated();
        }
    }
}

This will leave all your generated code intact, but will sort out your configuration woes.

Converting from scientific number format to decimal or string in C#

Matt Simner C# ,

I’m a programmer, but there are a few things I’ll admit I don’t do much of (as I have little interest in learning).  Playing with numbers in scientific notation is one of them.

I was importing from a spreadsheet and needed to get “6.00234419836431E+15” to something readable.

I discovered that you need to specify a NumberStyle when you parse as a decimal, because by default you’ll get an error as it expects an ‘easy’ conversion.

The number above had decimals, exponent, the lot – so the following line of code does the job nicely…

Console.WriteLine(decimal.Parse(“6.00234419836431E+15”, System.Globalization.NumberStyles.Any).ToString());
//Globalisation namespace just shown for clarity – you’ll want to put this in a using statement 🙂

This gives you “6002344198364310”. Sweet.

CommaDelimitedStringCollection – for when you want to write a comma delimited string from a collection !

Matt Simner .NET Framework, C# ,

Amazing that I still find BCL classes every day in .NET to do simple tasks.  I had a feeling that something may exist but didn’t expect to find it in the System.Configuration namespace.

using System.Configuration;

CommaDelimitedStringCollection strings = new CommaDelimitedStringCollection();

foreach(string item in myOtherCollection)
{
    strings.Add(item);
}

//Spit out your comma separated string
string output = strings.ToString();

Simple!

Issues with data binding, Typed Datasets, DataRows and Null Values

Matt Simner .NET Framework, ASP.NET, C# , ,

Had some fun this morning as we’re trying to get a simple framework set up for a web app, that others can understand, using out-of-the-box functionality if possible.  I tried to avoid the normal tendency to put the ‘architecture hat‘ on and overcook it.  If you can’t be bothered to read all of this then just scroll to the bottom for the ‘take home’ on binding against typed datasets with null values.


We started off the prototype with a typed Dataset (including a couple of DB tables, and key relationships between each), a table adapter for each table, and a DetailsView at the front end, bound to an objectdatasource, which in turn talks to a business object that calls the TableAdapter.  That’s a lot for one sentence so from top to bottom…


DetailsView –> ObjectDataSource –> BusinessService –> TableAdapter –> Database


This all sounds pretty straightforward, and it is – unless (as in this case) you decide to have your business object pass back a single (strongly typed) DataRow for your front end to bind against.  This seemed reasonable at the time because I only ever want to show one row here.


You’ll basically get a StrongTypingException for every null field you try and bind, because by default a null field value will raise an exception when accessed through its generated property. 


We got distracted for a while exploring this as the natural tendency is to think ‘ah – I’ll make sure it doesn’t raise the exception’ – treating the symptom rather than the cause.


It turns out that any non-string field in a typed dataset table can’t have a default of null (or anything other than ‘raise exception’).  If you stuff in a _null value directly in the XSD your fields will disappear from the public properties (rather curiously) when you compile – so that’s not an ‘enterprise’ option.


I tried changing the return type in my business method to a generic DataRow to try and force the data binding to NOT use the strongly typed properties (getting warmer but still no dice.  I knew I was missing something really simple…


I then stumbled across a post (bottom of the page) that illustrated databinding will use different methods to bind based on what interfaces it finds.  The binding mechanism basically has no choice but to use the strongly typed public properties on a strongly typed or generic DataRow, as there’s no way for it to successfully enumerate the values.  If you bind against almost anything else you’ll most likely have no issues (it appears the databinding doesn’t rely solely on ITypedList).  We’re now binding against a typed DataTable (rather than generic DataView) and all’s fine.  If you want (rather) more info on ITypedList then look here.


So…


Don’t databind against a typed (or untyped) DataRow!

If, For, Switch, but…

Matt Simner C# ,

I love that Scott Hanselman challenges his own as well as others’ thoughts on how to solve technical problems.


I’m not making any judgements about junior devs vs. senior devs. I’m talking about idioms and “vocab.” I think that using only if, for and switch is the Computer Programmer equivalent of using “like” in every sentence. Like, you know, like, he was all, like, whatever, and I was like, dude, and he was, like, ewh, and I was like meh, you know?


There’s some good examples on forcing yourself to learn new objects, features and constructs through refactoring.  If you don’t refactor then you’ll never know…

Using RSS.NET to re-write an existing feed XML file

Matt Simner C#, SubVersion, Tools , , , , ,

Don’t know what happened to RSS.NET.  Looks like it’s trying to go commercial, but still very quiet.  Code examples are few and far between (with some simple ones on the site).  Also on InformIT.


I’m writing a quick RSS console app (as I lost the last one I wrote!) that I can use to write an RSS feed from a SubVersion hook.  Most people use a pre-existing tool for Python (can’t remember the name), but I thought I’d give RSS.NET another go just for kicks.


The example above works fine assuming you’re maintaining state somewhere other than the feed xml file itself.  The example also assumes you’re serving this up to the web.  You can override the RssFeed.Write() method to take a filename.


If run the same code again (assuming you’ve written to a file) it will simply overwrite it with one item (not add to it).  This isn’t what I wanted so…


You need to



  1. Read the file back in if it’s there – otherwise create
  2. Add your item to the existing channel if it’s there – otherwise create
  3. Fix the date behaviour as RSS.NET always assumes UTC dates and appends ‘GMT’.  The problem here is that if you’re in Australia (like me) reading and rewriting the same items will effectively add several hours on to existing items every time, because you write the date you read back in for existing items (read and parse into region-specific date, then write back as is).  There’s two ways to fix this:

    1. Before you add your new item – loop through all items and change the item.PubDate to item.PubDate.ToUniversalTime().  This effectively sets it back to the ‘correct’ date.
    2. Change the RSSWriter class in RSS.NET to convert ToUniversalTime for the Item.PubDate, Channel.PubDate etc.  This seems like a better option, but it has potentially more knock on effects in RSS.NET.  I’m here to achieve a result, not change the behaviour (possibly adversely) of RSS.NET so I chose option 1

So here’s the code.  Not finished yet and rough around the edges, but works as I need.  The intention is to avoid the need for config files and configuring up of feeds specifically.  I just want a library function that’s called by a console app.  The web serving will simply be based on the location of the file and pointing to some folder in IIS.


        private static void WriteFeed(string feedFileName, string feedName, string feedDescription, 
        string feedURL, string itemTitle, string itemDescription, 
        DateTime itemPublishDate, string itemURL)
        {
            bool newFeed = false;
            //Try and first open the feed (to see if it’s existing)
            RssFeed feed = null;
            try
            {
                feed = RssFeed.Read(feedFileName);
            }
            catch (FileNotFoundException ex)
            {
                feed = new RssFeed();
                newFeed = true;
            }
            catch (Exception ex)
            {
                WriteError(ex);
                return;
            }

            RssChannel channel = null;

            //Loop through all channels and if we’ve got the same title reuse
            for (int i = 0; i < feed.Channels.Count; i++)
            {
                if (feed.Channels[i].Title == feedName)
                {
                    channel = feed.Channels[i];
                    break;
                }
            }

            if (channel == null)
            {
                channel = new RssChannel();
                feed.Channels.Add(channel); //might blow up if already there?
            }

            RssItem item = new RssItem();

            item.Title = itemTitle;
            item.Description = itemDescription;
            item.PubDate = itemPublishDate.ToUniversalTime();
            item.Link = new Uri(itemURL);

            //To ensure we don’t screw up existing dates – convert to UTC
            foreach (RssItem existingItem in channel.Items)
            {
                existingItem.PubDate = existingItem.PubDate.ToUniversalTime();
            }

            //Now add our new item
            channel.Items.Add(item);

            channel.Title = feedName;
            channel.Description = feedDescription;
            //channel.LastBuildDate = channel.Items.LatestPubDate();
            channel.PubDate = DateTime.UtcNow;
            channel.Link = new Uri(feedURL);

            feed.Write(feedFileName);
        }


Colorized by: CarlosAg.CodeColorizer

 


 

Parse and TryParse

Matt Simner .NET Framework, C# , , ,

Like many people I’d mistakenly thought that xxx.Parse was the only way to validate types like ints and DateTimes.  This kindof ‘was’ the case in .NET 1.1, but numeric data could be validated with Double.TryParse then converted to the correct type.


In .NET 2.0 you can happily use the TryParse method on pretty much all the types you’d want to validate – without the nasty overhead of catching an exception.  This post goes into a bit more detail, and links to a tool to benchmark the results for yourself.


I’ll be on the lookout to convert whereever I find int.Parse, as it’s pretty rare you ‘actually’ want an exception to be thrown…