LINQ to String Formatting (SQL)

I pulled the LINQ bits out of a larger context. I wanted a way to pass in a table name and list of fields and have it format them to parameterized UPDATE and INSERT statements (the full code goes out and figures the field types and adds appropriately typed params to a Command object).

For the UPDATE, I have a Where() to exclude the "id" column from the list of updated columns then only one Select() which concatenates them as "[fieldname] = @fieldname".

public string GetUpdateSQL( string table, string[] fields )
{
    return string.Format( @"update {0} set {1} WHERE ([id] = @id)", table, string.Join( ",", 
          fields.Where(f => f != "id").Select( f => "["+f+"] = @" + f ).ToArray() ) );
}

Notice that for the INSERT, I have two separate Select()s, one to build the field list & one for the param list.

public string GetInsertSQL( string table, string[] fields )
{
    return string.Format( "insert into {0} ({1}) values ({2}) ", table, 
          string.Join( ",", fields.Select( f => "["+f+"]" ).ToArray() ), 
          string.Join( ",", fields.Select( f => "@"+f ).ToArray() );
}

If I were to pass in "Changes" as the table and a field array of "id","product_id", "description" the function results would be:

update Changes set [product_id] = @product_id, [description] = @description where [id] = @id

...and...

insert into Changes ([id], [product_id], [description]) values (@id, @product_id, @description)  

LINQ does not do anything here that was impossible before... it just makes it so you don't have to do your own for loops or have any prep variables that would only exist just to be passed into the Format() call.

LINQ to Registry

The following example pulls all the values out of the registry in the "Uninstall" section (what shows up in the Add/Remove programs control panel applet).

I created a little class that gets instantiated during the LINQ query, used to just hold values for display in a grid.

class InstalledApp

{

    public InstalledApp(RegistryKey uninstallKey, string keyName)

    {

        RegistryKey key = uninstallKey.OpenSubKey(keyName, false);

        try

        {

            var d = key.GetValue("DisplayName");

            if (d != null) DisplayName = d.ToString();

            var s = key.GetValue("UninstallString");

            if (s != null) UnInstallPath = s.ToString();

        }

        finally

        {

            key.Close();

        }

    }

    public string DisplayName { get; set; }

    public string UnInstallPath { get; set; }

}

Next we pull all of the registry values out for the installed apps, then open up all the sub keys and read out the display name and the uninstall paths.

      RegistryKey lm_run = Registry.LocalMachine.OpenSubKey(

@"Software\Microsoft\Windows\CurrentVersion\Uninstall", false);

      try

      {

          bindingSource.DataSource = (from name in lm_run.GetSubKeyNames()

                                      let app = new InstalledApp(lm_run, name)

                                      where app.DisplayName != null

                                      select app).ToList();

      }

      finally

      {

          lm_run.Close();

      }

Assuming "bindingSource" is of type BindingSource and is the datasource for a grid, this code will populate the grid with a the apps' display names that can be uninstalled and the command to do it.

LINQ to Reflection 2

In the first reflection example, we just pulled out the types from the loaded assemblies that matched our criteria. In this example, we're searching through an array of objects and returning those that support a specific interface.

object[] objectArray = {new Queue<object>(), new StreamWriter(@"C:\temp.txt"), new List<object>()};

var supportsIEnumerable = (from obj in objectArray
                 from supportedInterface in obj.GetType().GetInterfaces()
                 where supportedInterface == typeof(IEnumerable)
                 select obj).ToList();

There are 2 objects in the supportsIEnumerable list.

LINQ to Controls (Validation)

For my next trick... I'll tack in a validator object into controls in my form and then call that object in a LINQ query to tell me if the input is valid. This is not the best way to accomplish this specific task, but I'm trying to keep my examples fairly simple and this should be easy to grok.

First, I create a class that can call a validation delegate.

class InputValidator
{
    public delegate bool ValidationDelegate( Control ctrl );
    private ValidationDelegate validateCode;
    private Control _ctrl;
    public InputValidator( Control ctrl, ValidationDelegate validate )
    {
        _ctrl = ctrl;
        validateCode = validate;
    }
    public bool IsValid()
    {
        return validateCode( _ctrl );
    }
}

Then, in the form initialization section, I'd assign the code to do the validation to the Tag property. If I were really wanting to creating an elegant validation solution that went beyond the Validating event of Control, I'd probably create extension methods/properties and tack them onto Control rather than using the Tag property. But let's keep our scope small.

textBox1.Tag = new InputValidator( textBox1, 
                delegate(Control ctrl) { return ctrl.Text.Length >= 5; } );

As delegates, the validation code can either be written for each control using anonymous methods, or shared between controls by creating a method with the correct signature and passing in that named delegate.

So, for the query, we can query all the controls on the form, get the ones where the Tag object is of the proper type, then cast the tag object and call the validation code. In the following query, we are returning all controls that are invalid.

var InvalidControls = from ctrl in Controls.Cast<Control>()
                      where ctrl.Tag != null
                      && ctrl.Tag.GetType() == typeof( InputValidator )
                      && !( (InputValidator)textBox1.Tag ).IsValid()
                      select ctrl;

Once you have the query, just foreach over the IEnumerable<Control> list and perform coloring or whatever operations you want. You may want to just have the LINQ query returned from a function that can be called from many places.

LINQ to Controls

In a perfect world, we'd be able to do this on a form:

var disabled = Controls.Select( c => !c.Enabled );

However, the Controls collection isn't one that is natively supported by LINQ. MS developed the custom collection of ControlCollection rather than using a generic (since generics didn't exist at the time).

There's a way around the problem. Since the generics namespace adds the "Cast" extension method when it sees the "IEnumerable" interface, we can just cast the list objects as Control. Once you've done that, then you just do your normal LINQ queries.

var disabled = Controls.Cast<Control>().Select( c => !c.Enabled );

Or the more SQL-esque way:

var invalid = from cont in Controls.Cast<Control>()
              where cont.Text == "" && cont.Visible
              select cont;

LINQ to Reflection

var results = from assembly in AppDomain.CurrentDomain.GetAssemblies()
              from module in assembly.GetLoadedModules()
              from type in module.GetTypes()
              from method in type.GetMethods()
              where method.Name == "Execute"
              select new
              {
                  Assembly = assembly,
                  Module = module,
                  Type = type,
                  Method = method
              };

This returns a set of anonymously typed objects that contain references to all of the types in all of the loaded assemblies that have an "Execute" method. If this were written in the traditional way, it would look like:

class MethodInfoContainer {
    public MethodInfo Method;
    public Type Type;
    public Module Module;
    public Assembly Assembly;
}

And the code that accomplishes what the single LINQ statement did...

List<MethodInfoContainer> results = new List<MethodInfoContainer>();

foreach(Assembly assembly in AppDomain.CurrentDomain.GetAssemblies()){
    foreach ( Module module in assembly.GetLoadedModules() )
    {
        foreach ( Type type in module.GetTypes() )
        {
            foreach ( MethodInfo method in type.GetMethods() )
            {
                if ( method.Name == "Execute" )
                {
                    results.Add( new MethodInfoContainer()
                    {
                        Assembly = assembly,
                        Module = module,
                        Type = type,
                        Method = method
                    } );

                }
            }
        }
    }
};

LINQ to Textbox

var lines = textbox1.Lines.Select( l => l.Contains( "the" ) );

The point of this post is that LINQ is everywhere. Use it on whatever you want. The above shows the "Select" that LINQ adds to collections, arrays, etc when you include the System.Linq namespace. Below is the more SQL-like query syntax, with an added condition and a trim. It returns all the lines that have "the" in it.

var lines = from line in textbox1.Lines
           where line.Contains( "the" ) && line.Length > 10
           select line.Trim();

I wrote a simple application that has two large text boxes and a small text box. The example program only has 1 statement, which is the following.

            txtResult.Lines = (from line in txtToSearch.Lines
                    where line.Contains(txtSearchString.Text)
                    select line).ToArray();  

I get bug reports in large text files (which include historical bug data, call stack, loaded modules, etc) automatically emailed to me from our application. I can paste the bug report into the txtToSearch control, type in "up time" into the single line txtSearchString control, and the results are put into the multi-line txtResult control and it will pull out all the lines that have either system or program up times. LINQ is great.

LINQ to Processes

var procs = ( from proc in Process.GetProcesses()
              where proc.PriorityClass == ProcessPriorityClass.Normal &&
                proc.StartTime < DateTime.Now.AddHours( -6 ) &&
                proc.TotalProcessorTime.TotalMinutes > 30 &&
                proc.Responding &&
                !proc.MainModule.FileVersionInfo.IsDebug
              select proc ).ToList();

This query pulls back a List of Process objects that are running with normal priority, have been running for more than 6 hours, have used more than 30 minutes of processor time, are still responding and are not debug builds.

If security settings prevent you from getting details on a process, this query will throw an exception.

LINQ to Files

In a very simple example, "files" is IEnumerable of string.

var files = from file in Directory.GetFiles(Environment.CurrentDirectory)
        select file;

But that's boring and in no way uses the querying power of LINQ. So, let's spice it up.

var files = from file in Directory.GetFiles(Environment.CurrentDirectory, "tmp*.*")
 where File.GetLastWriteTime(file) > DateTime.Now.AddDays(-2) &&
 (File.GetAttributes(file) != FileAttributes.System)
 orderby File.GetCreationTime(file)
 select file;

This query finds non-system files modified within the past 2 days where the name starts with "tmp" where it's been ordered based on the creation date.

This next one works but what is wrong with it?

var files = from file in Directory.GetFiles(Environment.CurrentDirectory)
      where new FileInfo(file).CreationTime > DateTime.Now.AddDays(2)
      select file;

It's technically ok to new up objects in a LINQ query and sometimes is exactly what you want to do. In this case, it was to get the CreationTime from the FileInfo object. However, since there's the static File.GetCreationTime(f) function that returns a DateTime rather than the heavier FileInfo object, it reduces how much memory is used. Imagine doing this call repeatedly on large directories... it'd sure give the GC something to work on.

Let's say we want to have a query that peers into sub directories as well...

var files = from dir in Directory.GetDirectories( Environment.CurrentDirectory, "DLS*" )
       from file in Directory.GetFiles( dir, "*.exe" )
       where FileVersionInfo.GetVersionInfo( file ).CompanyName.Contains( "Microsoft" )
       select file;

So, that query first gets all the directories that match a naming pattern of "DLS*", then in each one of those directories, it gets all of the exe's and only returns the ones where the CompanyName contains "Microsoft". Notice that there are two from statements (it's like nesting a for loop).

The function "GetDirectories" just returns an array of strings, which of course can be used in LINQ queries.

Then, you just do whatever you want to with the results:

foreach (string file in files)
{
       File.Delete( file );
}

LINQ Stuff

There's so much hype around LINQ to SQL (DLINQ) and LINQ to XML (XLINQ) that some really cool uses have been totally ignored. This blog is to show examples of LINQ for completely other uses, querying over system and user collections.

Whenever I see that a function returns an array, generic list, or anything that supports IEnumerable then I figure out how I could use it in a LINQ query.