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

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?

Looks like I might be able to use something called LINQKit, which maybe implements the extension methods from that article? Trying it tomorrow. — DCShannon, Apr 29 '15 at 05:15
Check this out: http://stackoverflow.com/questions/29448432/pass-expression-parameter-as-argument-to-another-expression/29471092#29471092 — MBoros, Apr 30 '15 at 00:48

DCShannon · Answer 1 · 2015-05-01T01:42:08.957

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.

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

1 Answers1