Entity framework 5 TSQL not compatible with SQL 2005 calling stored procedures

Question

I'm using Entity Framework 5, Code first against a SQL 2005 database. I have a repository, with a method which executes a stored procedure- The method looks like this;

   public IEnumerable<PossibleDuplicateCustomer> GetPossibleDuplicates(Customer customer)
    {

        return DbContext.Database.SqlQuery<PossibleDuplicateCustomer>(
            "EXEC SearchPotentialDuplicates @CustomerId = {0}, @FirstName = {1}, @LastName = {2}, @dob = {3}",
            customer.CustomerId,
            customer.CustomerFirstName,
            customer.CustomerLastName,
            customer.Dob);
    }

Another variant I tried is;

    public IEnumerable<PossibleDuplicateCustomer> GetPossibleDuplicates(Customer customer)
    {
        return DbContext.Database.SqlQuery<PossibleDuplicateCustomer>(
            "SearchPotentialDuplicates @CustomerId, @FirstName, @LastName, @dob",
            new SqlParameter("CustomerId", customer.CustomerId),
            new SqlParameter("FirstName", customer.CustomerFirstName),
            new SqlParameter("LastName", customer.CustomerLastName),
            new SqlParameter("dob", customer.Dob));
    }

When I execute this- i receive an error;

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'SearchPotentialDuplicates'.

So i grabbed the generated sql using miniprofiler- which gave me;

DECLARE @p0 int = 12644,
    @p1 nvarchar(4) = N'adam',
    @p2 nvarchar(3) = N'ant',
    @p3 datetime = '1951-11-01T00:00:00'

EXEC SearchPotentialDuplicates @CustomerId = @p0, @FirstName = @p1, @LastName = @p2, @dob = @p3   

I tried copying and pasting this into ssms and it gave an error because the syntax of declaring and assigning in one line is not supported

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@p0".

This is a new sql 2008 thing (http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/05/sql-server-2008-t-sql-declare-and-set-in-the-same-statement.aspx) and not support in SQL 2005- Changing the query to;

DECLARE @CustomerId int  ,
    @FirstName nvarchar(25),
    @LastName nvarchar(25) ,
    @dob datetime 

SET @CustomerId  = 12645
SET @FirstName  = N'adam'
SET @LastName  = N'ant'
SET @dob  = '1951-11-01T00:00:00'

exec SearchPotentialDuplicates @CustomerId, @FirstName, @LastName, @dob  

works fine! SO my question is how come entity framework is using this mental syntax? I've done some googling and people talk about ProviderManifestToken but this apparently is only needed if going to a totally different platform like sql ce, not between sql versions. So is there a setting I'm missing, or can i change the query to force it to execute in a different way?

Thanks All!

Answer 1

Finally got this working- I've tried following this article http://blog.oneunicorn.com/2012/04/21/code-first-building-blocks/ (by Arthur Vickers on the entity framework team) to tell the DbContext to use the sql 2005 syntax- So when I build the context I do this;

var builder = new DbModelBuilder();
builder.Entity<PossibleDuplicateCustomer>();
var model = builder.Build(new DbProviderInfo("System.Data.SqlClient", "2005"));
_compiledSql2005Model = model.Compile();

// OverdriveDbContext : DbContext
var context = new OverdriveDbContext(
    nameOrConnectionString: "OverdriveConnectionString", 
    model: _compiledSql2005Model);

This now executes the following SQL;

exec sp_executesql N'EXEC SearchPotentialDuplicates @CustomerId, @FirstName, @LastName, @dob',N'@CustomerId int,@FirstName nvarchar(4),@LastName nvarchar(3),@dob datetime',@CustomerId=12645,@FirstName=N'adam',@LastName=N'ant',@dob='1951-11-01 00:00:00'

which works perfectly in SQL 2005. A word of caution though- MiniProfiler (which I'm using to monitor entity framework calls) incorrectly shows the sql executed as:

DECLARE @CustomerId int = 12645,
    @FirstName nvarchar(4) = N'adam',
    @LastName nvarchar(3) = N'ant',
    @dob datetime = '1951-11-01T00:00:00'

EXEC SearchPotentialDuplicates @CustomerId, @FirstName, @LastName, @dob   

which stopped me noticing that I'd fixed this for a few hours! So the lesson there is MiniProfiler's SQL monitoring is no substitute for good old Sql Profiler!