IL to SQL

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:

public virtual void DoEntityXSplit()
{
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(); //THIS...
}
db.SaveChanges(); //OR this
}

When you run DoEntityXSplit, the unoptimised code may run. However if supported, automatic optimisation is possible derived from the IL (Instruction Language – aka. .Net bytecode) body of the method, when:
i) The ORM (Object Relational Modelling – eg. nHibernate / EntityFramework) supports some sort of “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.

public 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, it should be built as a standalone dll which an ORM can leverage. Who knows maybe EF will pick this up?