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!

Ecto

Arguably, they could have provided support for SQL Server 2005 by using the old fashioned variable declaration, but I wouldn't call it a "mental syntax"; I think it should be everyone's preferred way of declaring a variable in T-SQL, provided you're not targeting anything older than SQL Server 2008. – Vershen

haha yes, don't get me wrong- it is definitely the cleaner neater syntax- It's always bugged me having to split the declaration/assignment across two lines! But it's only better if the target platform supports it! – Ecto

What about ADO.Net and SqlCommand? You can write a wrapper to call stored procs using this approach. I know it's old method and it doesn't answers your question, but it can solve your problem. – Kurzawa

Yep- exactly; that is my plan B- but I feel like I've lost if I have to go down that route... but it is looking more and more like the way I will end up going – Ecto

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!

Ecto