none
CodeFirst 4.1 parameterized query sends unicode string to sqlserver RRS feed

  • Question

  • Hi,

    I'm trying to use EF 4.1 (CodeFirst) to query a table which has a char column (non-unicode).  I qualify the POCO column with the <Column(TypeName:="char")> attribute, but EF keeps sending a query to sqlserver that includes the 'N' character indicating it's an unicode value, and because of this sqlserver doesn't use the indexes defined.

    I've found entries on the forums that range in the 2009-2010 years, and they refer to "traditional" EF (model first), such as this.

    Does someone know how to handle this situation?

    Tuesday, October 11, 2011 6:02 PM

All replies

  • Hi,

    I've tested and it worked for me well.

    Here is my code:

    public class Person
    {
    	public int Id { get; set; }
    	[Column(TypeName="char")]
    	public string Name { get; set; }
    }
    
    // here is the query
    db.People.Where(p => p.Name == "testname").ToList();
    
    // here is the generated sql script
    SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[People] AS [Extent1]
    WHERE 'testname' = [Extent1].[Name]
    
    // column in database
    [Name] [char](8000)
    


    Anything I missed?

    Regards

    Tuesday, October 11, 2011 8:33 PM
  • Ok, I've found a semi-workaround using .AsNonUnicode (following your sample):

    p.Name == EntityFunctions.AsNonUnicode("testname")

    This changes the generated query to:

    exec sp_executesql N'SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
     COUNT(1) AS [A1]
     FROM [dbo].[TableX] AS [Extent1]
     WHERE ([Extent1].[Status] IN (256,128,32)) AND ([Extent1].[Name] = @p__linq__0)
    )  AS [GroupBy1]',N'@p__linq__0 varchar(8000)',@p__linq__0='161' 

    Initially the parameter was generated as nvarchar(4000), now it's varchar(8000), but ideally I need it to be char(3).  Is there a way to acomplish this?

     

     

     

    Wednesday, October 12, 2011 4:42 PM