Many of us are familiar with N+1 problem when working with database queries. The problem was known before ORMs (Object-relational mapping frameworks) came around, but it seems that ORMs exacerbated it.
The problem goes like this. If your database has a table of cars and each car has a list of wheels (stored in another table), if you first query for all cars (1) and then for each car you query for its wheels (N) you get orders of magnitude less efficient query because you in fact have N+1 queries, whereas you could just have one.
I'll show you one scenario in which it is difficult for a less experienced developer to spot that they have an N+1 problem while using ORM.
Let's assume we have the following tables definition in Microsoft Sql Server:
CREATE TABLE [dbo].[Article]( [Id] [int] IDENTITY(1,1) NOT NULL, [Title] [nvarchar](max) NULL, CONSTRAINT [PK_dbo.Article] PRIMARY KEY CLUSTERED ([Id] ASC) ) GO CREATE TABLE [dbo].[ArticleScore]( [Id] [int] IDENTITY(1,1) NOT NULL, [ArticleId] [int] NOT NULL, [ActualCity] [nvarchar](10) NULL, [Score] [int] NOT NULL, CONSTRAINT [PK_dbo.ArticleScore] PRIMARY KEY CLUSTERED ([Id] ASC) ) GO ALTER TABLE [dbo].[ArticleScore] WITH CHECK ADD CONSTRAINT [FK_dbo.ArticleScore_dbo.Article_ArticleId] FOREIGN KEY([ArticleId]) REFERENCES [dbo].[Article] ([Id]) GO
There is nothing fancy here, the main thing is that we have two tables that have a foreign key relationship.
Now let's assume that we use EntityFramework, and somewhere in the code we have a line like this:
var scores = context.ArticleScores.Where(x => x.Score < 100).ToList();
Now the scores list gets passed around a few methods, and somewhere else it's used like this:
var filtered = scores.Where(x => x.Article.Title != "trash").ToList();
This last line will cause a separate query to be executed for each Article object. When the number of objects are large, the overhead as we all know serious.
The problem with this particular case can be solved with eager loading, and Entity Framework offers Include method that would load Article objects along with ArticleScore objects in the first query, thus eliminating the need to query the database at all for the second query.
Now to my question.
I would (apparently incorrectly) imagine, that there should be enough information for ORM to execute the second query in a single sql query, thus reducing N+1 problem to 1+1 problem which, in most cases won't be a problem.
ORM knows about relationship between Article table and ArticleScore table. It knows that conceptually a list of ArticleScore objects represent a slice of ArticleScore table. Thus it should be able to figure out how to get the corresponding slice of the Article table in one query, it does not look like a rocket science.
So could you please tell me, is there some fundamental problem, when writing ORMs, that would prevent elimination of most of the N+1 problems?
I understand that you cannot prevent someone from shooting oneself in the foot by writing specifically incorrect code, but in the examples like the one given above many people can easily miss the problem altogether, which is apparent to me after a few code reviews I conducted. To many people this code looks like it should have worked without exhibiting N+1.