How can I use a custom method in a LINQ-to-SQL select clause?

Question

How can I use a custom method in a LINQ-to-SQL select clause?

c# expression-trees linq-to-sql

Question

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?

DCShannon

Popular Answer

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.

DCShannon

View more on Stack Overflow

How can I use a custom method in a LINQ-to-SQL select clause?

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator