CTE Sql query support in Entity Framework <DefiningQuery>

Question

Can entity framework support SQL queries that use CTE / row-numbers in the

<DefiningQuery> </DefiningQuery>

attribute of the .edmx file?

This in regards to a query given to me in this post that uses such features: Complex / Algorithmic SQL Query

I am getting the following InnerException from sql:

Incorrect syntax near the keyword 'with'.
Incorrect syntax near the keyword 'with'. If this statement is a common table ex
pression, an xmlnamespaces clause or a change tracking context clause, the previ
ous statement must be terminated with a semicolon.
Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ')'.

Adding the semi-colon before the opening 'with' statement does not resolve the issue. It says incorrect syntax near ';' in that case.

If unsupported, I think I could bind a stored procedure to a complex entity, but are there any other options?

Thanks.

Is there a reason you're trying to use a CTE? I don't know everything you went through to decide this, but the point of EF (and ORMs as a whole) is to abstract the database away to where you don't have to worry about specific database implementations. That being said, I'm sure there are operations out there that EF won't natively handle that you may need a CTE for, in that case, I would do as you suggested and have EF call a stored procedure (assuming it can). — Brian Ball
– Brian Ball, Commented Jan 5, 2011 at 1:57
Somewhat just out of curiosity. I have some custom entities that combine data from multiple tables using the DefiningQuery feature of entity framework. Theres also a simple algorithm I am using to calculate length of a users session from login and logout entries in a database. Another poster provided me with a very complex SQL query that actually does the algorithm all by itself. If I created a custom EntityType that used this as its Defining Query, I could very simply query the database for these sessions. — sean
– sean, Commented Jan 5, 2011 at 2:03
This answer? You could lob the whole thing into a View. e.g. CREATE VIEW yourview AS with events as ...and i.row=o.row Does that make things any easier? — Martin Smith
– Martin Smith, Commented Jan 5, 2011 at 2:03
Essentially a DefiningQuery is a client-side View from what I understand. So yes, I could create a view in the database. I'm not sure exactly how a custom EntityType in EF would relate to it though. — sean
– sean, Commented Jan 5, 2011 at 2:18

John Wigger · Accepted Answer · 2011-06-06 18:36:41Z

0

This post doesn't specify whether the CTE is recursive or non-recursive or not. There can be compelling performance reasons to use a recursive CTE that can outweigh the desire to abstract away the database (i.e using EF as an ORM).

In Entity Framework 4, there are workarounds as Martin mentions above with using a view. This blog post from Matthieu Mezil (http://msmvps.com/blogs/matthieu/archive/2010/06/16/how-to-include-recursion-table-valued-functions-in-linq-to-entities-queries-with-ef4.aspx) has some great detail on how to do just that.

answered Jun 6, 2011 at 18:36

John Wigger

9148 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CTE Sql query support in Entity Framework <DefiningQuery>

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related