Entity Framework or Why I Implement Repository

Abstractions? Who needs abstractions?
EF is the coolest ORM from MS. habrahabr.ru/post/157267 is really cool. To develop such a system is very expensive in all areas. CodeFirs thing, knowledge of database design, but what is there! No knowledge of SQL itself is needed - and this is awesome. But is it all rosy?
Julie Lerman can tell a lot msdn.microsoft.com/en-us/magazine/ff898427.aspx

Nothing like?
This is the implementation of Repository + UoW. So, using EF, doing all sorts of wrappers is useless?
EF is an incredibly popular technology, it’s difficult to find the right provider for your storage.
The main idea of ​​EF (like any Repository) is to separate the object layer from the storage layer. But why?
Everything is simple - to change the storage without pain and the object is independent.

When do I need to change the storage?

I would like to never, but as they say "never say ..". I am working on a project in which there are two battle layers of the storage and one more demonstration. The client chooses how he will store his data.

Do I need to build an abstraction on top of the abstraction and do something that is already implemented?

The answer lies in the compatibility of specific providers. Here's what compatibility says one of the developers of such providers Yevhen Shchyholyev www.infoq.com/articles/multiple-databases . Problems that were understood at the intuitive level are reinforced by the developers themselves.
Here are some of them

Data types

The Entity Framework supports a limited set of .NET types (signed integers, Single, Double, Decimal, String, Byte [], TimeSpan, DateTime, DateTimeOffset, Boolean, Guid). However, not all .NET types from this list are supported by databases.

Types - the eternal problem. That is, a single script executed on one provider (namely on the provider) may not give the same results on the other.

DateTimeOffset support

DataTimeOffset is the most problematic type of .NET. It is supported in SQL Server MS 2008, Oracle and SQLite, but not in SQL Server MS 2005, MySQL or PostgreSQL, since these databases do not have DateTime, which can save time zone offsets.

The problem we have is that the date is stored in UTC, but EF under SQLight persistently returned to Local. The problem was easily solved, we use Mapping.

Performance

If the specific features of a particular database are not taken into account, it is possible that a LINQ query may be processed faster in one database and slower in another.
The pagination of data performed by the LINQ methods .Skip () /. Take () can be one of the most costly operations. Unlike in SQL Server, Skip / Take, Oracle generates two subquery sorts in an internal subquery that can negatively affect performance. It should be noted that pagination can be quite inefficient in other databases, especially if the sorting is performed on non-indexable columns.

Here is the most interesting, for different providers you need to write your queries on LINQ. Another interesting scenario: they wrote some functionality with a bunch of requests for the context and everything works fine. But the application is cross and you need to check how it will work on another provider. It was found that some of the requests were not effective. It does not matter - optimization. All is well. The question of how optimization has affected the work with the source provider?

Request Restrictions

In EF, a developer can generate queries using either LINQ to Entities, or Entity SQL, or a combination of both. In any case, such a request is converted by the EF mechanism into an intermediate representation as an expression tree and sent to the EF provider so that it generates a SQL query. Since EF was originally developed for SQL Server, sometimes EF providers have to significantly transform the original expression tree in order to generate the correct SQL for a specific database. However, this is not always possible .

It clearly states mission impossible . SQL SQLyu provider, provider. Here even it is not a question that the request will not be effective, but that it can crumble the execution of the application.

The corresponding MSDN section says that all these functions are supported by all EF providers:
"This section discusses canonical functions that are supported by all data providers and can be used by all query technologies."
This section seems to be over-optimistic, since different DBMSs have different functionality, and sometimes it’s impossible to implement all the compatibility features.

Here is the link . But not anyhow, but the leading developer of a set of third-party providers says mission impossible .

Conclusion

In this article I tried to provide a brief description of the most common problems in the development of EF applications for non-Microsoft SQL Server databases. Undoubtedly, many other aspects did not receive attention in this article ...

That is, it is not all the problems that may arise alongside the described ones, and which can only be learned by standing on a rake.

Summing up.

EF is a good abstraction, and like any abstraction, it is not without its main drawback - its implementation. The problems described in this article are not taken from the head and not from the mouth of skeptics, but from the first hands - the developer of providers.
Providers are developed by different companies and I am sure that if providers from different manufacturers use compatibility problems in one project, there will be much more. forums.mysql.com/read.php?174,614148,614148
I am by no means saying NO EF , I say yes to the Repository, because all the problems described are easily circumvented with the help of this abstraction that will wrap the EF and absorb compatibility issues.

PS

Only a technological example is considered, for which it may be necessary to implement the “Bicycle”, the reasons for which it is possible and necessary to use the repository and gadgets are much more.