Improvements to Generated SQL in .NET 4.0

re: Improvements to Generated SQL in .NET 4.0

Diego B Vega — Mon, 28 Nov 2011 18:06:30 GMT

@Mike Emerson: Thanks for bringing this up. A fix for this issue will be included in the update for the EF core libraries in .NET 4.5.

re: Improvements to Generated SQL in .NET 4.0

Mike Emerson — Mon, 28 Nov 2011 15:37:54 GMT

The bug with comparing ASCII strings with unicode mentioned in point 5 still exists when LINQ to Entities produces an IN clause. For example if I put into my query:

(status == "A" || status == "B")

where status is a non-unicode string, Linq-to-Entities produces the SQL query:

where status in ( N'A', N'B')

which causes serious performance issues.

re: Improvements to Generated SQL in .NET 4.0

Yogesh — Tue, 01 Feb 2011 19:44:38 GMT

We have an application which has the entity model created using vs2008 and at some point the project was upgraded to vs2010. However reading this blog, I observerd that the end SQL being generated by the application is still using the older version of .NET 3.5.

For ex: StartsWith operator used by the Ado.net dataservices still translates to the ncharindex sql call.

Is there anything specifically that needs to be done so that the application uses the EF4 version and generate the much improved sql?

re: Improvements to Generated SQL in .NET 4.0

Ron Ward — Mon, 01 Nov 2010 17:08:23 GMT

Re point 5 above. Seems to work correctly with string constants, variables of type string, not so much. Can someone point me toward a solution or at least some additional discussion on the matter.

Thanks.

re: Improvements to Generated SQL in .NET 4.0

Vitor Castro — Tue, 26 Oct 2010 09:14:44 GMT

Sorry, this comment was on done on the wrong post, please ignore

re: Improvements to Generated SQL in .NET 4.0

Vitor Castro — Tue, 26 Oct 2010 09:12:09 GMT

One question on TPH:

I have a scenario where I have a base abstract entity from which two types derive. One of these types is concrete while the other is abstract. From this second abstract type another concrete type is derived leaving me with this structure:

AbsT1

|--- ConcT1

|--- AbsT2

|--- ConcT2

This conceptual model is then mapped to a single table in the storage model (TPH). This table has a column to identify the type of record being stored.

In these mappings, conditions are added to both concrete types to get this column fixed values for each case.

Under this condition I always get mapping errors stating that ConcT1 and ConcT2 are mapped to the same rows in the table which in my oppinion shouldn't happen.

Additionally if I derive ConcT1 from AbsT2 instead, keeping everything else the same, all mapping errors are gone and everything works as expected. Another way of making things 'work' is to make both abstract types concrete instead.

I've already posted this scenario on Connect but no solution as of yet. Can you shed some light on this scenario?

Thanks in advance

re: Improvements to Generated SQL in .NET 4.0

Frans Bouma — Tue, 26 Oct 2010 08:16:17 GMT

Actually, point 5 isn't true, it still doesn't work, as I reported on the data-access mailing list. It would be great if someone from the team could update this post to enlight that issue.

re: Improvements to Generated SQL in .NET 4.0

Daniel Simmons — Thu, 27 May 2010 07:56:07 GMT

@Steve J,

What version of Entity Framework are you using? I'm pretty sure we did make a change to help with parameter name consistency that should be in the RTM version of EF 4, but I'd like to confirm that you are using something earlier than that. If you are seeing this with EF 4 RTM, then I'd like to follow-up.

@Mark,

Unfortunately we haven't made any changes to include in EF4. The reason Include uses joins is because that's the most straightforward way to guarantee a consistent snapshot of results. If you make separate statements (unless you have a ridiculously restrictive isolation level), then you run the risk of something changing between the first select statement and the second one which could cause your data to be inconsistent. Additionally, the thought is that if you are willing to take that risk, then you can always write two or more queries yourself without using include.

That said, more flexible fetch plans is one of the things on our backlog for future improvements to include. I'm just not sure yet when it will hit the top of the list.

- Danny

re: Improvements to Generated SQL in .NET 4.0

Mark White — Wed, 26 May 2010 08:36:56 GMT

Any improvments to how Included entities are loaded?

I'd really like to see includes handled as seperate select statements, rather than joins.

re: Improvements to Generated SQL in .NET 4.0

Steve J — Wed, 12 May 2010 15:40:43 GMT

Question about the generated SQL. In the current version of the Entity Framework, consecutive executions of a linq query keeps changing the name of the parameters. For example, the first execution of a linq query a parameter could be named something like @p__linq__5. The next execution the parameter has changed to @p__linq__23 or something similar. This is killing our SQL Server because it cant reuse the query plan for the generated sql.

We cant use compiled queries because our web site is very dynamic allowing sorting, filtering and searching. We have to dynamically build the conditions before execution of the query. This prevents the query from being compiled. Granted, the different conditions would also cause a recompile on the sql server but there would still be some re-use because a lot of queries would search with the same criteria.

So, have you implemented a consistent naming method for the parameters?