Working around null values in LINQ queries

I’m almost ashamed to admit I hadn’t commonly used the ‘null coalescing operator‘ ?? in C# until recently, and was commonly writing code like

var myVar = myNullableVar == null ? myNullableVar.Value : 0;

or variations on a theme using HasValue etc (still better than the long-hand if-else mind you)

Clearly this is more readable as

var myVar == myNullableVar ?? 0;

Often I find that things break down when you introduce Entity Framework, as there’s limitations on what it will understand (from the point of view of translating to the underlying data context).  Null values though are another place you can save a bit of repetitive code, as you’ll quite often have nullable dates, or other nullable types..

var output = (
    from tab in context.MyTable
    where tab.EffectiveDate == effectiveDate
    select new 
    {
        Code = tab.Code,
        /* Old
        Value = tab.Value == null ? 0 : tab.Value.Value
        */
        // New
        Value = tab ?? 0
    }).ToList();

This is a pretty simple example, but in conjunction with the SqlFunctions library, you can keep things nice and neat with type conversions in your code.

It’s only when you look a little further into the language that you see c#’s got quite a nice set of operators now 🙂

 

LINQ Group by MAX Date Query

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();

Simple WPF Page Navigation From an MVVM ViewModel

Navigation in WPF is easy – unless of course you’re trying to apply an MVVM pattern.  Most examples tell you about all the great things you can do with MVVM/WPF whilst brushing such things as navigation under the carpet.  I’ve not found one person who’s adequately explained an MVVM example with ‘all’ of the facets you’ll need when writing a real application.

I’m not going to tell you how to implement full-scale configurable multi-context navigation using MVVM, but I’ll briefly discuss one approach to a nagging issue – that of triggering and controlling navigation from the ViewModel.  I’m also talking specifically about ‘pages’ here too, as I’m targeting a browser with this application.

WPF ‘Page’ objects expose a NavigationService property, which hooks into the WPF navigation framework.  This is very convenient and powerful.  MVVM effectively steers you away from doing anything behind your ‘views’, and tries to substitute the traditional coupling between view/controller/viewmodel (depending on the flavour) with reliance on data binding to give the viewmodel everything it needs to perform all the UI logic.

Your ViewModel isn’t supposed to have any reference to (or knowledge of) your view.  This means you won’t have a reference to the Page to be able to access its NavigationService.  There’s other ways to navigate, like using more of a frame appropach (most of the examples so this), but if you want to navigate ‘web-style’ from Page1 to Page2 to Page3 etc – controlling this from your ViewModel, what do you do?

After messing around with quite a number of approaches I’ve currently settled for a very simple technique that doesn’t feel ‘too’ dirty.  It became clear (in my case) that loading the ViewModel from the View is actually more appropriate and practical than loading the view from the ViewModel (through a DataTemplate mapping as others would suggest).  I found starting everything from the ViewModel paints you into something of a technical corner, as some core WPF functionality only exists at the view level.  You can of course write your own implementations, but I’ve always thought patterns are meant to ‘help’, and when they cease to help, you stop.

In our example, the Application object effectively sets things up by being the all-seeing eye on the navigation framework.

The code below simply sets the startup uri (from another library), and subscribes to the ‘navigated’ event, which will fire after every page movement.

    public partial class App : Application
    {

        private static NavigationService navigator;

        protected override void OnStartup(StartupEventArgs e)
        {
            base.OnStartup(e);
            this.StartupUri = 
new
Uri("pack://application:,,,/MyPageLibrary;component/MyStartupPage.xaml"); } void App_Navigated(object sender, NavigationEventArgs e) { Page page = e.Content as Page; if (page != null) ApplicationHelper.NavigationService = page.NavigationService; } }

The ApplicationHelper class is a simple static implementation to provide the whole application with what is in effect a ‘bus’ service – the means to navigate, using the NavigationService injected from the Application.  I said this was simple.

    public static class ApplicationHelper
    {
        private static NavigationService navigator;

        public static NavigationService NavigationService
        {
            set
            {
                navigator = value;
            }
            get
            {
                return navigator;
            }

        }

    }

The ViewModel is then free to navigate whereever it likes (I’m constructing the pages as objects here with parameters to use in constructing the viewmodel, rather than using a uri).

    //Now navigate to the detail view
//Datacontext used to construct the ViewModel
MyNextPage nextPage = new MyNextPage(SomeDataContext);
ApplicationHelper.NavigationService.Navigate(nextPage);

I’m sure this will evolve again (like everything I’m finding with WPF), but for now this seems to perform my basic requirements

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

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.

ASP.NET Data Binding – Accessing a parent data item from within a nested repeater

I’m maintaining an app at the moment that uses quite a few nested repeaters, and found that headers were being output when there was no data present.  It was found that the header was being written in the ItemTemplate of an ‘outer’ repeater, rather than as the HeaderTemplate of the ‘inner’ repeater.  The next problem was how to reference the outer repeater from the ‘inner’ HeaderTemplate…

The following will bind to a field called HeaderDescription.

<%# DataBinder.Eval(Container.Parent.Parent, “DataItem.HeaderDescription”) %>

The parent of the inner item is it’s repeater, so you have to go to it’s parent to get the right RepeaterItem.  Why don’t you just do the following you ask?

<%# DataBinder.Eval(Container.Parent.Parent.DataItem, “HeaderDescription”) %>

..’cos it doesn’t work – The Eval method expects a ‘Control’ as its first parameter.  There’s other ways to do this server-side, but the first option is probably the easiest.

To complete the picture and only show when there’s data you can add the following to the ‘inner’ repeater declaration

OnItemDataBound=”ItemDataBound” Visible=”false”

then..

        protected void ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item)
            {
                if (!e.Item.Parent.Visible)
                    e.Item.Parent.Visible = true;
            }

        }

This will ensure that you’ll only show if you’ve bound a ‘data’ item (remember you’re doing binding in the HeaderTemplate too).  You could also hook similar things into other events, but it’s generally more convenient to put these things into events that relate to the actual control (pre_render’s probably another good candidate as it will only get called once and you can check the count in the DataSource).

Technorati Profile

Converting from scientific number format to decimal or string in 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 !

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!

Using local SQL 2005 Express databases, |DataDirectory| setting and SQLBulkCopy with SemiColon delimited text files


Had some fun with this today as I was utterly confused why I wasn’t able to save data to my SQL2005 express DB (linked within a Windows Forms VS 2005 project), even though I wasn’t getting any errors from a SQLBulkCopy operation.  The connection string was set (from the wizard) to use ‘|DataDirectory|\dbname.mdf.

It turned out of course that the reason there wasn’t an error was because the data was being written fine, but just not to the database I was looking at.  By default any database linked in your project file will be copied to the output folder (bin\debug) when you build.  It does this ‘every time‘.  I thought I was going mad as I wasn’t getting primary key violations trying to insert the same data over and over again!

The useful info is in this MSDN blog entry.  It talks about different options for working around the local databases, but I plumped for a subfolder (called data) in my project, then setting ‘Copy if newer’ on the MDF file rather than ‘do not copy’.  This just means that if you make a schema change then it will blow everything away (probably what I normally want in debug-land).

The reason I was using the SQLBulkCopy class was so I could take in a dataset picked from a semicolon-delimited text file. 
The first thing to do was read the text file into a dataset – and after some mucking about with the Jet OleDb provider and avoiding changing registry entries (to get it to recognise ‘;’ as the delimiter) I found that a schema.ini file is what’s required.

Here’s some quick code I then used to get a DataSet from a semicolon-delimited Text File:

       

       /// <summary>
        /// Import from a given file to database
        /// </summary>
        /// <param name=”fileName”>File to import from (CSV)</param>
        
public void Import(string fileName)
        {
            
if (File.Exists(fileName))
            {
                SqlConnection connection 
= new SqlConnection(Properties.Settings.Default.RBStatsConnectionString);
                
//Get Dataset from file
                
DataSet importData GetDataSetFromFile(fileName);

                //Import using SQLBulkCopy
                
SqlBulkCopy copyManager = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
                
copyManager.DestinationTableName “TShirtStatistic”;
                
copyManager.BatchSize importData.Tables[0].Rows.Count;
                try
                
{
                    copyManager.WriteToServer(importData.Tables[
0]);
                
}
                
catch (Exception exception)
                {
                    Console.WriteLine(exception)
;
                
}
                copyManager.Close()
;
                
connection.Close();
            
}

        }

        public DataSet GetDataSetFromFile(string fileName)
        {
            
//Ensure we’ve got a schema file to fudge the semicolon delimited text
            
string schemaFile Path.Combine(Path.GetDirectoryName(fileName), “schema.ini”);
            
File.Delete(schemaFile);
            string 
schemaContents “[” + Path.GetFileName(fileName) + “]\nColNameHeader=True\nCharacterSet=ANSI\nFormat=Delimited(;)”;
            
StreamWriter writer File.CreateText(schemaFile);
            
writer.Write(schemaContents);
            
writer.Close();

            string connectionString “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + Path.GetDirectoryName(fileName) + “\\;Extended Properties=’text;HDR=Yes;FMT=Delimited'”;
            string 
query “SELECT * FROM ” + Path.GetFileName(fileName);
            
OleDbConnection connection = new OleDbConnection(connectionString);
            
OleDbCommand command = new OleDbCommand(query, connection);

            connection.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);
            
DataSet ds = new DataSet();
            
adapter.Fill(ds);

            return ds;

        }

Updated DeletedOld CodeProject Article

After an extremely long time I’ve made an update to the DeleteOld console app and updated the CodeProject article.  The original was .NET 1.1 – this one’s now 2.0 (I’m not on 3 yet!)

It still does the same things it did – deletes files of a specified age, but now also:

  • Allows files to be deleted ‘newer’ than a certain age as well as ‘older’
  • Allows files to be deleted based on an absolute date (overriding the timeframe arguments)
  • Allows the ‘root’ path to be preserved if ‘remove empty folders’ is selected and the path specified is empty.
  • Fixed a bug in Arguments parsing regex as noted by dudik
  • Changed output datetime format to ‘full’ rather than specific dd/mm/yyyy as noted by a.plus.01

I had a need for the absolute date, so thought I may as well cover off a few other things. 

Hope it’s of some use…

You can also see my other CodeProject articles.