EF Core Parameter Sniffing in SQL Server

Question

I've been watching Brent Ozar's training videos (the SQL Guru for me) and he talks about parameter sniffing and say EF does this, but for the life of me I cant get an example working. I was expecting to see parameters but it just creates the SQL like this with just equality and not @p1, @p2.

SELECT [p].[Id], [p].[Body], [p0].[Type]
FROM [Posts] AS [p]
INNER JOIN [PostTypes] AS [p0] ON [p].[PostTypeId] = [p0].[Id]
WHERE ([p].[PostTypeId] = 6) AND ([p].[CreationDate] >= '2011-01-01T00:00:00.000')

I created the DBContext from scaffolding his StackOverflow2013 database and I created a foreign key on the post type to get some sort of JOIN.

Does anyone know how I can get an example of parameter sniffing using EF as this just creates a new query plan every time?

If I call stored proc then I can get parameter sniffing.

My C# Code is as follows

  var result = ctx.Posts
    .Include(x => x.PostType)
    .Where(x => x.PostTypeId == 6 && x.CreationDate >= new DateTime(2013, 01, 01))
        .Select(x => new {
           Id = x.Id,
           Body = x.Body, 
           Type = x.PostType.Type
         }).ToList();

Answer 1

And soon after I find the answer :)

As Gert says in the comment I had to pass a variable, so EF is doing some magic there. So if I changed my code to this then it happens:

var myId = 6;

var result = ctx.Posts
    .Include(x => x.PostType)
    .Where(x => x.PostTypeId == myId && x.CreationDate >= new DateTime(2013, 01, 01))
        .Select(x => new {
            Id = x.Id,
            Body = x.Body, 
            Type = x.PostType.Type
        }).ToList();

I then get SQL like this

exec sp_executesql N'SELECT [p].[Id], [p].[Body], [p0].[Type]
FROM [Posts] AS [p]
INNER JOIN [PostTypes] AS [p0] ON [p].[PostTypeId] = [p0].[Id]
WHERE ([p].[PostTypeId] = @__myId_0) AND ([p].[CreationDate] >= ''2013-01-01T00:00:00.000'')',N'@__myId_0 int',@__myId_0=6

I can then execute another one with a different value

exec sp_executesql N'SELECT [p].[Id], [p].[Body], [p0].[Type]
FROM [Posts] AS [p]
INNER JOIN [PostTypes] AS [p0] ON [p].[PostTypeId] = [p0].[Id]
WHERE ([p].[PostTypeId] = @__myId_0) AND ([p].[CreationDate] >= ''2013-01-01T00:00:00.000'')',N'@__myId_0 int',@__myId_0=7

I can then see in the execution plan that the same query plan was used, as the top one has the first query that setup the plan then the second one uses it and keeps the estimated 166 rows but actually only got 4 rows back

Also I can confirm this if I look at the second queries properties, and can see it was compiled with 6 put was run with 7