Moving C# function to expression for use in Entity Framework / SQL Select

Question

I have some small C# functions and computed cols from views that I'd like to move to Expressions so that they execute directly against the datasource in native T/SQL.

I can do this inline in the Select, but I'd like to move it to a common function for re-use and testing.

var results = context
    .Products
    .Select(p => new StockDto
    {
        Stock = p.GoodStock - p.LiveStock // Real version is more complex.
    });

I've tried creating a function that returns an expression but C# attempts to assign the expression instead of the result of the expression and won't compile. Using the following Expression variable does not compile either.

public static readonly Expression<Func<DataModel.Product, int>> StockLevel = 
    expr => expr.GoodStock - 10;

I'm familiar with using expressions for appending to Where clauses but I can't figure out how to create an Expression that returns a string from a select statement.

I'd like to do this;

var results = context
    .Products
    .Select(p => new StockDto
    {
        Stock = StockExpression // StockExpression is written in C#
    });

LinqKit seems to be about making predicates (Where clauses) easier to work with, I can't see how to compile an Expression Tree. Is the only way to do this to code a complex expression tree by hand (I'd like to avoid that as it obscures the meaning of the code)?

Answer 1

LinqKit should work for you. You should call Invoke to use one expression in another.

You have two options for "expanding" the merged expressions:

You either invoke AsExpandable on the IQueryable so that it "expands" expressions automatically like this:

var results = context
    .Products
    .AsExpandable()
    .Select(p => new StockDto
    {
        Stock = StockLevel.Invoke(p) 
    });

Or you expand the expression manually before using it like this:

Expression<Func<Product, StockDto>> expression = p => new StockDto
{
    Stock = StockLevel.Invoke(p) 
};

expression = expression.Expand();

var results = context
    .Products
    .Select(expression);