Geek Answers Handbook

EF 4.1: Why does turning a constant into a variable add an extra query?

Today I discovered that the Entity Framework was adding an unnecessary subquery to the SQL that it generates. I started digging my code, trying to narrow down the place where it might come from. A (long), and later I indicated what causes it. But now I am more embarrassed than when I started, because I do not know why this causes it.

Basically, I found that under certain scenarios, simply converting a constant into a variable can change the SQL generated by the Entity Framework. I reduced everything to a minimum and packed it into a small console application:

using System;
using System.Data.Entity;
using System.Linq;

class Program
{
    private static readonly BlogContext _db = new BlogContext();

    static void Main(string[] args)
    {
        const string email = "foo@bar.com";

        var comments = from c in _db.Comments
                       where c.Email == email
                       select c;

        var result = (from p in _db.Posts
                      join c in comments on p.PostId equals c.PostId
                      orderby p.Title
                      select new { p.Title, c.Content });

        Console.WriteLine(result);
    }
}

public class BlogContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
    public DbSet<Comment> Comments { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
}

public class Comment
{
    public int CommentId { get; set; }
    public int PostId { get; set; }
    public string Email { get; set; }
    public string Content { get; set; }
}

This shows the following result, which is ideal:

SELECT
[Extent1].[PostId] AS [PostId],
[Extent1].[Title] AS [Title],
[Extent2].[Content] AS [Content]
FROM  [dbo].[Posts] AS [Extent1]
INNER JOIN [dbo].[Comments] AS [Extent2] ON [Extent1].[PostId] = [Extent2].[PostId]
WHERE N'foo@bar.com' = [Extent2].[Email]
ORDER BY [Extent1].[Title] ASC

Now, if I make a emailvariable:

/*const*/ string email = "foo@bar.com";

The output changes radically:

SELECT
[Project1].[PostId] AS [PostId],
[Project1].[Title] AS [Title],
[Project1].[Content] AS [Content]
FROM ( SELECT
        [Extent1].[PostId] AS [PostId],
        [Extent1].[Title] AS [Title],
        [Extent2].[Content] AS [Content]
        FROM  [dbo].[Posts] AS [Extent1]
        INNER JOIN [dbo].[Comments] AS [Extent2] ON [Extent1].[PostId] = [Extent2].[PostId]
        WHERE [Extent2].[Email] = @p__linq__0
)  AS [Project1]
ORDER BY [Project1].[Title] ASC

LINQ to SQL, , . , , , , . , . (, ) , , , ( ). ...

​​ SQL?

Update:

, , email ( ). , sub.

!

+4
4

SQL? , - , .

, . ? , .

+1

. LINQ . vs non const one ConstantExpression ParameterExpression.

const, LINQ ConstExpression , non const, ParameterExpression, - Runtime EF.

, , .

+4

- .

, .

( ) SQL, SQL Server (, , ) , .

, , , , , SQL , , .

, SQL , , / - " ", !

+3

. .

, , LINQ, . EF SQL . , , () .

Edit: , , ", EF". , EF . . EF . , EF. -, . .NET, C ? C, , ?

The only way to be safe and still be able to use a layer with high EF abstraction is to often use the SQL profiler and check if the queries are too long for real data. And if you find some of them, either convert them to direct SQL or stored procedures.

+1
source

More articles:


All Articles