TSQL framework 5 entity is not compatible with SQL 2005 stored procedures

Question

I am using Entity Framework 5, first the code for the SQL 2005 database. I have a repository with a method that executes the stored procedure. The method is as follows:

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 option I tried:

  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 get an error message:

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 the declaration and destination on the same 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 thing sql 2008 ( 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 is not supported in SQL 2005- Changing a 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 great! So my question is, how does an entity structure use this mental syntax? I have done some search queries and people are talking about ProviderManifestToken, but this, apparently, is only necessary when switching to a completely different platform, for example sql ce, and not between versions of sql. So, is there a parameter that I don't have, or can I modify the request to make it execute differently?

Thanks everyone!

Answer 1

I finally got this job: I tried this article http://blog.oneunicorn.com/2012/04/21/code-first-building-blocks/ (Arthur Vickers in the entity framework team) to tell DbContext to use syntax sql 2005. Therefore, when I create a 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); 

Now the following SQL is executed:

 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 fine in SQL 2005. A warning though is that the MiniProfiler (which I use to monitor object structure calls) does not correctly show 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 

who stopped me, noticing that I fixed it for a few hours! Thus, the MiniProfiler SQL monitoring lesson does not replace the old old Sql profiler!