Entity Framework in an Enterprise Solution

I pitched my service to our governance board today. They had some concerns about Entity Framework (especially the head of the data team).

Their were two main concerns:

1. Since EF generates its SQL on the fly is it open to SQL Injection attacks (when used 开发者_运维知识库via WCF Services and WCF Data Services).
2. Will EF Scale out to support high load situations without becoming the "slow spot" in the path?

I am fairly sure of #1. )If EF was open to SQL Injection attacks I think I would have heard about it.) But I would love something from MS that says the inputs from Data Contracts are sanitized. (Kind of like a stored procedure (that does not use Dynamic SQL in the sproc).)

As for #2 I am not sure. When I start getting high volume of hits against my EF based services, is EF going to cause me grief?

1. You're less likely to have SQL injection with EF than with manually-written SQL.
2. There's no simple answer to this. "High load situations" is far too vague to succinctly answer. In general, the bigger you scale, the less any "one size fits all" solution works. As always, measure twice, cut once.

I agree with Craig. They trust hand-written SQL more than SQL generated by such big tool from Microsoft? To be honest, unless there is a strange bug in EF, it is not possible to do SQL injection, because EF always generates parameterized queries. In other words, their argument is plain bullocks.

And for #2, EF will not be the slow spot, it will always be your database, because you can scale your web service with EF on it out to multiple servers, without much problem most of the time. Scaling out your database however is a completely different ball game. However, I must say that O/RMs can easily generate 'sub optimal' SQL queries that are sometimes hard to tune. DBAs will not always be happy with this. However, in my experience is it is almost always possible to tune those SQL queries (by rewriting the LINQ queries). I've done that a lot in the last few month and it can be quite challenging some times. In that rare case that you can't optimize on the .NET side enough, you can always fall back to an indexed view, stored procedure or manual SQL, but that should be rare.

Just run the profiler and check what queries EF produces. You will see parametrized queries which are main defence against SQL injection. So no EF is not open to injection attacks but if you are using EntitySQL and concatenating strings manually you can have injection attack on higher layer.

Sure EF has performance costs. This is trade off of easy and rapid development where developers don't need to have too much knowledge about internal processes / technologies to build an application (in case of many MS tools a drilled monkey can build an application but performance and other quality measurements will be adequate).

Actually these performance costs can be very high and it is point of performance testing to find if there is performance bottleneck or not. If you find the part which is too slow it is up to you to optimize it. It can lead to code refactoring or creating stored procedures to handle complex queries.

In case of DataServices this can be more complex. Client can trigger some complex query which will simply take long and you don't have control over that query. Some defence against this is limiting number of returned / queried records on the service level. If user wants more records he must specify more detailed search criteria.

For #2 whenever an abstraction is introduced ther will be a trade-off with performance. Whetther it is significant for you or not can only be determined by performance testing and profiling in your situation. Read this post on why a custom ORM, Dapper was required for SO to give you an idea. Dapper also has a performance test suite you can use to ascertain how much of an impact EF will have on your typical queries.

There is also a #3. Some features of SQL are still not supported by EF and this could limt your design. e.g. Unique Key constraints are not supported yet.

EF is still evolving and keep an eye on the ADO.Net team blog.