Use column/property facets for parameter types in Query Pipeline

[carlcamilleri]

Unless I'm missing something, parameterised queries (against SQL Server in my case) are treating all strings as NVARCHAR

I have the below table definition:

modelBuilder.Entity<Products>(entity =>
            {
                entity.ToTable("Products", "ofr");

                entity.HasIndex(e => e.ProductId).HasName("IX_Products_ProductId").IsUnique();

                entity.HasIndex(e => new { e.Id, e.ProductId, e.PublicationId }).HasName("IX_ofr_Products_PublicationId");

                entity.Property(e => e.ProductId)
                    .IsRequired()
                    .HasMaxLength(50)                    
                    .HasColumnType("VARCHAR");
            });

Then a query:

 var q = from p in products

                        join sp in dbContext.SiteProducts
                        on new { Id = p.Id, ProductId = p.ProductId }
                        equals new { Id = sp.ProdId, ProductId = req.ProductId}

                        where   sp.SiteId == req.Engine.Context.SiteId                                            

                        select p;
var res = q.ToList();

The query generated is:

exec sp_executesql N'SELECT TOP(1) [p].[Id], [p].[ProductId], [p].[PublicationId]
FROM [ofr].[Products] AS [p]
INNER JOIN [ofr].[SiteProducts] AS [sp] ON ([p].[Id] = [sp].[ProdId]) AND ([p].[ProductId] = @__req_ProductId_0)
WHERE [sp].[SiteId] = @__req_Engine_Context_SiteId_1',N'@__req_ProductId_0 nvarchar(4000),@__req_Engine_Context_SiteId_1 int',@__req_ProductId_0=N'350003995',@__req_Engine_Context_SiteId_1=1

The "ProductId" parameter is therefore treated as an NVARCHAR, obviously missing the index.

I have tested with EF6 against the same database, and the query generated correctly uses a VARCHAR parameter:

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[ProductId] AS [ProductId], 
    [Extent1].[PublicationId] AS [PublicationId]
    FROM  [ofr].[Products] AS [Extent1]
    INNER JOIN [ofr].[SiteProducts] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[ProdId]) AND ([Extent1].[ProductId] = @p__linq__0)
    WHERE 1 = [Extent2].[SiteId]',N'@p__linq__0 varchar(8000)',@p__linq__0='324324324'

Is this something which is in-line to be fixed, or is there perhaps a workaround?

Thanks

[ErikEJ]

Try:

.HasColumnType("VARCHAR(50)");

[carlcamilleri]

Hi,

Thanks, tested but unfortunately same query is generated:

        modelBuilder.Entity<Products>(entity =>
            {
                entity.ToTable("Products", "ofr");

                entity.HasIndex(e => e.ProductId).HasName("IX_Products_ProductId").IsUnique();

                entity.HasIndex(e => new { e.Id, e.ProductId, e.PublicationId }).HasName("IX_ofr_Products_PublicationId");

                entity.Property(e => e.ProductId)
                    .IsRequired()
                    .HasMaxLength(50)                    
                    .HasColumnType("VARCHAR(50)");
            });

exec sp_executesql N'SELECT [p].[Id], [p].[ProductId], [p].[PublicationId]
FROM [ofr].[Products] AS [p]
INNER JOIN [ofr].[SiteProducts] AS [sp] ON ([p].[Id] = [sp].[ProdId]) AND ([p].[ProductId] = @__req_ProductId_0)
WHERE [sp].[SiteId] = @__req_Engine_Context_SiteId_1',N'@__req_ProductId_0 nvarchar(4000),@__req_Engine_Context_SiteId_1 int',@__req_ProductId_0=N'350003995',@__req_Engine_Context_SiteId_1=1

For reference I'm running this from a DNX project, my project.json file:

{
  "version": "1.0.0-*",
  "description": "...",
  "authors": [ "..." ],
  "tags": [ "" ],
  "projectUrl": "",
  "licenseUrl": "",
  "frameworks": {    
    "dnx451": {
      "dependencies": {
        "Microsoft.CSharp": "4.0.1-beta-23516",
        "System.Collections": "4.0.11-beta-23516",
        "System.Linq": "4.0.1-beta-23516",
        "System.Runtime": "4.0.21-beta-23516",
        "System.Threading": "4.0.11-beta-23516"
      }
    }
  },
  "dependencies": {
    "EntityFramework.Commands": "7.0.0-rc1-final",
    "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
    "EntityFramework.MicrosoftSqlServer.Design": "7.0.0-rc1-final",    
    "EntityFramework.Core": "7.0.0-rc1-final",    
    "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging.Abstractions": "1.0.0-rc1-final",
    "Microsoft.Extensions.DependencyInjection.Abstractions": "1.0.0-rc1-final",
    "Microsoft.Extensions.DependencyInjection": "1.0.0-rc1-final"    
  },
  "commands": {
    "ef": "EntityFramework.Commands"
  }
}

[rowanmiller]

Probably considered a duplicate of #4134 and missing the index would be a good reason to do that work sooner rather than later.

[carlcamilleri]

Thanks for the feedback.

Just to add one further detail in case it helps: this is only impacting queries which compare VARCHAR columns to string variables.

The following query compares a VARCHAR column to an explicit string value "12345"

  var q = from p in products

                        join sp in dbContext.SiteProducts
                        on new { Id = p.Id, ProductId = p.ProductId }
                        equals new { Id = sp.ProdId, ProductId = "12345"}

                        where   sp.SiteId == req.Engine.Context.SiteId                                            

                        select p;

The comparison at SQL level is fine, "12345" is not presented as N'12345" and therefore utilises indexes on the VARCHAR column correctly:

exec sp_executesql N'SELECT [p].[Id], [p].[ProductId], [p].[PublicationId]
FROM [ofr].[Products] AS [p]
INNER JOIN [ofr].[SiteProducts] AS [sp] ON ([p].[Id] = [sp].[ProdId]) AND ([p].[ProductId] = ''12345'')
WHERE [sp].[SiteId] = @__req_Engine_Context_SiteId_0',N'@__req_Engine_Context_SiteId_0 int',@__req_Engine_Context_SiteId_0=1

[mikary]

Once facets for parameter types have been added to the query pipeline, we should ensure the DefaultQuerySqlGenerator uses the IRelationalCommandBuilder / IRelationalTypeMapper overload that maps from the IProperty type instead of the clr type.