CODEWITHSUNDEEP
c#entity-framework
Aaron Anodide
Aaron Anodide

Reputation: 17196

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

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?

Upvotes: 2

Views: 1891

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109252

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.

Upvotes: 3

web_bod
web_bod

Reputation: 5758

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/

Upvotes: 0

Related Questions