LINQ to REST Webservice

Kiva is a microlending site that allows you to loan as little as $25 (along with others to total $1500 or whatever) to entrepreneurs in developing nations. Some loans contain more risks than others and it's a good idea to look over your options to prevent loss if that's a concern of yours. Kiva has RESTful webservices that can spit out either XML or JSON. Below I have code that I run in LINQPad to select the best of all available loans, then it orders the results by when it will be repaid in full so that as a lender, you can re-lend that money as quickly as possible to help someone else.

It first goes through and selects all the highest rated field partners (established, 5 star rating, no delinquency, no defaults). Then it pulls the number of pages of data that are available for loans and then pulls page by page of loan information to get a collection of all new loans and filters out any that do not still have the "fundraising" status and selects only the group loans and joins these loans with the filtered partner listing. In the final block, it goes out and looks up each loan individually to get the repayment terms (the final payment) and filters out any loans that are not protected from loss due to exchange rates or nonpayment and orders by the final payment date.

//PARTNERS: fetch all partners, then remove any that have non-0 delinquency or default rates, are non-5 star rating, or are not "active" status (removes: pilot,paused,closed)

var partners = from partner in XDocument.Load(@"http://api.kivaws.org/v1/partners.xml").Descendants("partners").Descendants("partner")

               let status = (string)partner.Element("status")

               let rating = (string)partner.Element("rating") == "" ? 0.00 : (float)partner.Element("rating")

               let delinquency_rate = (string)partner.Element("delinquency_rate") == "" ? 0.00 : (float)partner.Element("delinquency_rate")

               let default_rate = (string)partner.Element("default_rate") == "" ? 0.00 : (float)partner.Element("default_rate")

               where status == "active" && default_rate == 0.00 && delinquency_rate == 0.00 && rating == 5

               select new { id = (int)partner.Element("id"), rating, name = (string)partner.Element("name") };

//LOANS: cycle over all the pages of the newest loans, keeping only ones that are still unfunded group

int pagecount = (int)XDocument.Load(@"http://api.kivaws.org/v1/loans/newest.xml").Descendants("paging").First().Element("pages");

var tot = from page in Enumerable.Range(1, pagecount)

          from loan in XDocument.Load(string.Format(@"http://api.kivaws.org/v1/loans/newest.xml?page={0}", page)).Descendants("loan")

          where (string)loan.Element("status") == "fundraising"

          let loan_amount = (int)loan.Element("loan_amount")

          let funded_amount = (string)loan.Element("funded_amount") == "" ? 0 : (int)loan.Element("funded_amount")

          let basket_amount = (string)loan.Element("basket_amount") == "" ? 0 : (int)loan.Element("basket_amount")

          let borrower_count = (int)loan.Element("borrower_count")

          let partner_id = (int)loan.Element("partner_id")

          let lid = (string)loan.Element("id")

          let remaining = loan_amount - funded_amount                      

          where borrower_count > 1

          join p in partners on partner_id equals p.id

          select new

          {

              lid,

              name = (string)loan.Element("name"),

              loan_amount,

              funded_amount,

              remaining,

              basket_amount,

              borrower_count,

              activity = (string)loan.Element("activity"),

              sector = (string)loan.Element("sector"),

              use = (string)loan.Element("use"),

              pid = p.id,

              partner_name = p.name,

              partner_rating = p.rating,

              url = string.Format(@"http://www.kiva.org/app.php?page=businesses&action=about&id={0}", lid) 

          };

//FINAL WEEDING PROCESS, REMOVE ALL RISK: removes any loans that can have currency loss or risk of nonpayment, order by the due date

var finalists = from l in tot

                let ldet = XDocument.Load(string.Format(@"http://api.kivaws.org/v1/loans/{0}.xml", l.lid))

                let finalpayment = DateTime.Parse((string)ldet.Descendants("scheduled_payments").Descendants("scheduled_payment").Last().Element("due_date"))

                let nonpayment = (string)ldet.Descendants("loss_liability").First().Element("nonpayment")

                let currency_exchange = (string)ldet.Descendants("loss_liability").First().Element("currency_exchange")

                where nonpayment == "partner" && currency_exchange == "partner"

                orderby finalpayment ascending

                select new { loan = l, finalpayment, nonpayment, currency_exchange };

To see these queries in action in a Silverlight app, visit my site.

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

                }
            }
        }
    }
};