I would like to do something like what is described in this article. However, the article is from 2006, and requires extension methods the author wrote. I'm hoping there's something more built-in at this point.
The intent here is to save a method that contains only operations translatable to T-SQL and use it to assign specific properties in a LINQ-to-SQL select statement. If there's a radically different way to do this then what I've attempted, then that's also a valid answer.
How do I do it?
What I've Tried
I have a query sort of like this:
var theData = (
from inv in dc.Inventory
where inv.IsDeleted == false
join data in cdc.InventoryDatas
on inv.InvKey equals data.ReInvKey
where data.ReColKey == colKey
select new MyClass {
SerialNumber = inv.SerialNumber,
Data = InventoryData.DataExpression( data ),
Name = inv.Name,
}
);
Where InventoryData.DataExpression
is defined as such:
public static Expression<Func<InventoryData, string>> DataExpression = (
d => d.TextData != null ? d.TextData
: d.IntegerData != null ? d.IntegerData.ToString()
: d.DecimalData != null ? d.DecimalData.ToString()
: d.DateData != null ? d.DateData.ToString()
: d.BooleanData != null ? d.BooleanData.ToString()
: null
);
But it's not quite right. How can I make this work?
This works just fine when select only data from one table, and when I don't combine other expressions in the right side of the assignment statement for the same property:
Data = InventoryData.DataExpression.Compile()( data )
as do
select InventoryData.CompiledDataExpression( data )
and
select data.GetData()
where CompiledDataExpression
is defined as
public static Func<InventoryData, string> CompiledDataExpression
= InventoryData.DataExpression.Compile();
and GetData
is defined as
public string GetData() {
// execute the compiled delegete on this InventoryData
var ret = InventoryData.CompiledDataExpression( this );
return ret;
}
However, the delegate is not actually compiled to T-SQL. Instead, whole objects of the type associated with the table are pulled, and then the delegate is executed upon them locally.
I get an error when I try the full query, which joins on other tables and assigns the delegate to just one of several properties, some of which pull data from the other tables.