Can SQL level functions be made available to LINQ to Entity queries?

Question

I wish I could write LINQ to Entity queries that called other functions:

from c in context.Widgets
where MyFunc(c.name)
select c

That causes an error because the expression obviously can't be converted to TSQL that calls MyFunc.

Well, I'm thinking, unless MyFunc was either a user defined function or (I think better yet) a SQL/CLR function.

So is this possible, and moreover is it recommended?

If it's not possible, is my desire to have this feature valid and possibly going to be addressed by a future of ADO.NET?

Answer 1

You should create a user-defined function MyFunc in the database and "import" it manually into your context (edmx, so database first), both in the XML and as a stub in a partial class off the context class. The procedure is described here:

How to use a custom database function (Note that "StorageNamespace" is the namespace that you find in the XML file under <edmx:StorageModels><Schema Namespace=....

MSDN has a similar description.

Answer 2

EF is pitched at synchronising .net classes with the structure of the database and is best where the database is dumb and all the logic sits in the classes.

But you can map to functions and stored procedures - it's a bit technical to explain (a lot easier to do) - let me find some links for you.

Here's a bad way to do it:

// Query that calls the OrderTotal function to recalculate the order total.
string queryString = @"USING Microsoft.Samples.Entity;
    FUNCTION OrderTotal(o SalesOrderHeader) AS
    (o.SubTotal + o.TaxAmt + o.Freight)

    SELECT [order].TotalDue AS currentTotal, OrderTotal([order]) AS calculated
    FROM AdventureWorksEntities.SalesOrderHeaders AS [order]
    WHERE [order].Contact.ContactID = @customer";

int customerId = 364;


using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(queryString, context);
    query.Parameters.Add(new ObjectParameter("customer",customerId));

    foreach (DbDataRecord rec in query)
    {
        Console.WriteLine("Order Total: Current - {0}, Calculated - {1}.", 
            rec[0], rec[1]);
    }
}

http://msdn.microsoft.com/en-us/library/dd490951.aspx

An here's how to do it properly:

http://scipbe.wordpress.com/2010/08/30/stored-procedures-udfs-in-the-entity-framework-part-1/