Complex SQL - Compile SQL from IL

[todd....@alivate.com.au]

There are some cases where one needs to perform some more complex processing, necessitating Application-side processing OR custom SQL commands for better performance. For example, splitting one column of comma delimited data into 3 other columns:

virtual void DoEntityXSplit()

{

  //Sorry, I'm not familiar with BLToolKit yet, so I'll use EF syntax

  var NeedSplitting = db.EntityXs.Where(x => !x.Splitted1.HasValue);

  foreach (var item in NeedSplitting)

  {

     string[] split = item.DelimitedField.Split(',');

     item.Splitted1 = split[0];

     item.Splitted2 = split[1];

     item.Splitted3 = split[2];

     item.Save(); //Or whatever you do in BLToolKit     

  }

}

When you run DoEntityXSplit, the unoptimised code may run. However if supported, that is, i) The ORM supports any ILtoSQL compilation at all; and ii) The function doesn't contain any unsupported patterns or references, then the raw SQL may be run. This could include the dynamic creation of a stored procedure even for even faster operation.

override void DoEntityXSplit()

{

  //This is pseudo SQL code

  db.RunQuery("

   declare cursor @NeedSplitting as (

     select ID, DelimitedField

     from EntityXs

     where Splitted1 is null

   );

   open @NeedSplitting;

   fetch next from @NeedSplitting into @ID, @DelimitedField

   while (@StillmoreRecords)

   begin

          @Splitted1 = fn_CSharpSplit(@DelimitedField, ',', 0)

          @Splitted2 = fn_CSharpSplit(@DelimitedField, ',', 1)

          @Splitted3 = fn_CSharpSplit(@DelimitedField, ',', 2)

          update EntityX

          set Splitted1 = @Splitted1, 

               Splitted2 = @Splitted2,

               Splitted3 = @Splitted3

          where ID = @ID

          fetch next from @NeedSplitting into @DelimitedField

   end

  ");

}

of course this could also be compiled to

override void DoEntityXSplit()

{

  //This is pseudo SQL code

  db.RunQuery("

   update EntityX

   set Splitted1 = fn_CSharpSplit(DelimitedField, ',', 0),

        Splitted2 = fn_CSharpSplit(@DelimitedField, ',', 1)

        Splitted3 = fn_CSharpSplit(@DelimitedField, ',', 2)

   where Splitted1 is null

  ");

}

but I wouldn't expect that from version 1 or would I?

Regardless, one should treat IL as source code for a compiler which has optimisations for T-SQL output. The ORM mappings would need to be read to resolve IL properties/fields to SQL fields. It may sounds crazy, but it's definitely achievable and this project looks like a perfect fit for such a feat.

Where will BLToolKit be in 10 years? I believe ILtoSQL should be a big part of that future picture. 

If I get time, I'm keen to have a go. And who knows maybe EF will pick this up?