EDM and Store functions exposed in LINQ

Exposing EDM and database server functions to LINQ

Diego Vega — Thu, 09 Oct 2008 06:36:23 GMT

Alex published today a description Colin and I wrote on a new feature the team has been working on for

re: EDM and Store functions exposed in LINQ

Zeeshan Hirani — Thu, 09 Oct 2008 09:45:29 GMT

In the above examples, I did not see any example of functions returning out paramter and table values functions. In linq to sql I have functions returning a collection of entity which i can join against an existing query. Is that supported?

Also it would be nice to get support for functions taking table, a new featured in sql server 2008?

thks

Zeeshan Hirani

re: EDM and Store functions exposed in LINQ

Zeeshan Hirani — Thu, 09 Oct 2008 22:24:36 GMT

will the function defined on teh clr be available for use with entit sql as well?

re: EDM and Store functions exposed in LINQ

divega — Fri, 10 Oct 2008 08:53:02 GMT

Zeeshan,

Regarding TVFs and TVPs, we do want to support them with this mechanism, but we first need to build the capability in lower layers in the Entity Framework stack. At this point, we are not sure where this is going to land.

Regarding your question on whether the CLR functions is going to be available from Entity SQL queries: if you are referring to arbitrary CLR functions being available in Entity SQL, the answer is no. That would require of a general mechanism to translate IL code to the query language of the database server. For instance, there is no general way to translate arbitrary IL code into T-SQL. If you are referring to the functions we are exposing to LINQ with this feature, they are already available to Entity SQL queries.

Perhaps you are interested in having methods that have both a runtime CLR behavior and a corresponding semantics when used in queries. Then the answer is yes, that would be possible with this mechanism.

Thanks,

Diego

re: EDM and Store functions exposed in LINQ

Zeeshan Hirani — Fri, 10 Oct 2008 10:26:42 GMT

Thanks Diego for the explanation. I was looking for functions exposed to linq be also available for esql which u said it would be.

Another question. Not sure if this is a right place to ask. Can we expect to retun partially filled entity cuz lot of times we do not need entire entity yet Ef requires a full entity set. Can the restriction be reduced to returning partially filled entity containing only entity key.

Like in Linq to sql, I can return partially filled entity from stored procedure or dynamic sql as long as the reocrd set contains the column name that matches teh column name defined as the primary key on the entity.

Also can we get relationship span to work with stored procedure as well?

re: EDM and Store functions exposed in LINQ

Stefan Dobrev — Fri, 10 Oct 2008 13:12:08 GMT

The support seems promising.

Here are 2 ideas I have in mind:

1. For the static functions - can't we just declare them as extern without providing "dummy" implementation that throws exceptions.

2. It is good to think about some kind of alternative syntax for Expression tree, because currently it seems a little bit ugly. Maybe some kind of expression tree's magic can do the trick:

public static double? StandardDeviation( this IQueryable<int?> source )

{

Expression ex = EntityExpression.Create( source.StandardDeviation() );

return source.Provider.Execute<double?>( ex );

}

re: EDM and Store functions exposed in LINQ

Diego Vega — Sat, 11 Oct 2008 13:40:01 GMT

Zeeshan,

I would like to understand more about the scenarios for "partially filled entities". I not sure what is exactly possible and in which circumstances this is more useful than a projection. I am aware that we support materializing arbitrary classes and using ExecuteQuery and Translate in LINQ to SQL, and I agree that is a useful feature.

I will follow up on your other question on query span iwth stored procedures with someone that may know the answer.

re: EDM and Store functions exposed in LINQ

efdesign — Sat, 11 Oct 2008 13:59:09 GMT

Stefan,

At the language/CLR level, "extern" requires an implementation at type load time. What we are doing in this case does not really require any changes at lower levels of the framework.

The idea of using this kind of "expression factory" method is interesting. It may result in code that is easier to read than building the expressions directly as we do. However, in the case of our internal code and generated code, the simpler expressions we get when we use the current way seem to be a better choice.

Thanks,

Diego

re: EDM and Store functions exposed in LINQ

Zeeshan Hirani — Sun, 12 Oct 2008 11:25:59 GMT

Diego,

Thanks for the explanation. For returning partially filled entity, the only reason i had was it was easier to pas it across tiers as compared to anonymous types. LIke for databainding scenaious, I had some stored procedures that would execute some complicated query to return few columns for my entity. When i was doing linq to sql i got away but as i moved my app over entity framework, I struggled through fixing my stored procedure to meet EF needs. More so I would need somethings from extensions to be moved to core product that i use on a day to day basis on my project. Like I would create a stored procedure in the database and map it to any clr type entity or not it doesn't matter. There is so much pain that one has to go through to use a stored procedure and if the stored procedure does not map to an entity, you are out of luck.

what else.. let me think

More esql operatos needs to be supported in queryview.

Be able t specify at the mapping level the default sort to be used in case there is no sort provided for associations or entitysets. LIke the other day I had the need to apply a sort everywhere I am retrieving orders for customers to be sorted by order date.

Wilson ormapper supported this..

flexible stored procedure mapping. I want to returning idenity column using output paramters. I can hack it by using command text but i dont like that option. my list is endless so i will stop.

But thanks for listening to our needs. I am very happy that i moved my project to EF, my code is much cleaner from the linq to sql queries because now my model is complicated but get a new developer started with our model is much easier and quicker.

Thks

re: EDM and Store functions exposed in LINQ

Alex Mineev (MSFT) — Tue, 02 Dec 2008 12:08:32 GMT

Regarding the partially filled entities: you can define a function that will return entity refs instead of entities.

Model Defined Functions

Entity Framework Design — Fri, 09 Jan 2009 04:55:38 GMT

Today the Entity Framework, and more specifically the Entity Data Model, have a limited notion of Functions.

re: EDM and Store functions exposed in LINQ

aaa — Sat, 10 Jan 2009 00:08:54 GMT

what about sprocs with table valued parameters? very useful for bulk-inserts!

Update on Computed Properties

Entity Framework Design — Tue, 13 Jan 2009 07:27:46 GMT

A while back I wrote a post that introduced the concept of Computed Properties . Since that time we’ve