Incorrect syntax near '('. Connection to database with entity framework

Question

I have ASP.NET MVC app which works good on my local server. When I'm deploying it into Windows Server 2008 Standard in IIS (v7.0) the app is running property (I can see panel to log in), but when I want to log in, I have such error. I have also page Contact and when I want to run it I have the same error.
I add Model.edmx using ADO.NET Entity Data Model.

The code is simple:

In controller:

private ImportEntities Entity = new ImportEntities();
public ActionResult Contact()
    {
        var dane = Entity.Contact.FirstOrDefault();
        return View("Contact",dane);
    }

Connection string in Web.config:

<add name="ImportEntities" connectionString="metadata=res://*/Models.Model.csdl|res://*/Models.Model.ssdl|res://*/Models.Model.msl;
 provider=System.Data.SqlClient;
 provider connection string=&quot;data source=AAA\aaa;
      initial catalog=Database_name;
      integrated security=True;
      MultipleActiveResultSets=True;
      App=EntityFramework&quot;"
 providerName="System.Data.EntityClient" />

The error:

Server Error in '/MyApp' Application.
Line 1: Incorrect syntax near '('. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.

Source Error: 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 


Stack Trace: 
[SqlException (0x80131904): Line 1: Incorrect syntax near '('.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1767866
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5352418
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61
   System.Data.SqlClient.SqlDataReader.get_MetaData() +90
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1406
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +437

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +507
   System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +730
   System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +131
   System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +36
   System.Linq.Enumerable.FirstOrDefault(IEnumerable`1 source) +152
   System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1(IEnumerable`1 sequence) +41
   System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle(IEnumerable`1 query, Expression queryRoot) +59
   System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +133
   System.Data.Entity.Internal.Linq.DbQueryProvider.Execute(Expression expression) +87
   System.Linq.Queryable.FirstOrDefault(IQueryable`1 source) +251
   MyApp.Controllers.HomeController.Contact() in c:\Users\mon\Documents\Visual Studio 2013\Projects\MyApp\MyApp\Controllers\HomeController.cs:39
   lambda_method(Closure , ControllerBase , Object[] ) +62
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +182
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.Async.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() +28
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +50
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32
   System.Web.Mvc.Async.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() +58
   System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +225
   System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__36(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +50
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34
   System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20() +24
   System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult) +99
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +50
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
   System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__18(IAsyncResult asyncResult) +14
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +55
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +39
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +55
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +29
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__3(IAsyncResult asyncResult) +25
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +55
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +31
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9514928
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155



--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18446

What I don't understand: where is that moment when my app use some string connection with '('? I can't install any additional programs to debug it on server and check what is wrong.

Do you have any ideas what and where could be wrong?

======
In Model.edmx -> Model.tt -> Contact.cs

namespace MyApp.Models
{
    using System;
    using System.Collections.Generic;

    public partial class Contact
    {
        public int id { get; set; }
        public string phone { get; set; }
        public string email { get; set; }
        public string address { get; set; }
        public string page { get; set; }
    }
}

======
I found sql query which use my app with @Paul_Zahra help.

SELECT TOP (1) 
[c].[id] AS [id], 
[c].[phone] AS [phone], 
[c].[mail] AS [mail], 
[c].[address] AS [address], 
[c].[page] AS [page]
FROM [dbo].[Contact] AS [c]

Now I can see that I have problem with first line. As I remember I shouldn't have (1), just 1. Do you know how to change it?

I used my own SQL query to build the database which I want to use. — Monic, Aug 18 '14 at 09:06
you should debug the section where the sql query is constructed — Robert, Aug 18 '14 at 09:07
I meant I just used this query in SQL Server Management Studio. In my app I don't use sql query. — Monic, Aug 18 '14 at 09:08
what is happening in ImportEntities, ImportEntities.Contact? — Robert, Aug 18 '14 at 09:09
@Robert do you mean Model.Contact? I've updated my question. — Monic, Aug 18 '14 at 09:15
@Monic Your connection string looks fine, one thing you could change is metadata=res://*/; Most likely you don't need all the other stuff for metadata. — Paul Zahra, Aug 18 '14 at 09:21
you cut out the most important part of the error: the stack trace. The error is telling you that some SQL query your app tried to execute was not formatted properly but you'll need to read up the stack trace to find where it's coming from. — Michael Edenfield, Aug 18 '14 at 11:25
I really don't understand this stack trace. I've updated question by adding whole stack trace. — Monic, Aug 18 '14 at 12:02

Paul Zahra · Answer 1 · 2014-08-18T12:28:50.903

1

It could be caused by you 'sharing' your context... try this... (It's generally best to use the Unit of Work approach for creating and consuming your db context unless you really know what your doing with the context and are usually willing to micro manage it)...

public ActionResult Contact()
{
    Contact dane = null;

    using (ImportEntities ctx = new ImportEntities())
    {
        ctx.Connection.Open();

        dane = ctx.Contact.FirstOrDefault()

        ctx.Connection.Close();
    }

    return View("Contact",dane);
}

EDIT:

Your issue is likely stemming from the DbProviderInfo.ProviderManifestToken; "A string that identifies that version of the database server being used. For example, the SQL Server provider uses the string "2008" for SQL Server 2008. This cannot be null but may be empty."

Are your local (works ok) and server versions of SQL Server the same? I guess not.

The token is specified in SSDL. See this post for some interesting insights into what is a pretty big problem with EF for some

edited Aug 18 '14 at 12:28

answered Aug 18 '14 at 09:27

Paul Zahra

9,522
8
54
76

I add this lines in code on server and it doesn't work. But now I try compile it in my project and it's error _only assignment call increment decrement and new object expressions can be used as a statement_ on "ctx.Connection.Open;" – Monic Aug 18 '14 at 09:58
@Monic Sorry my bad, made a few little mistakes... was missing () on the methods and the Entity.Contact.... should be ctx.Contact.... – Paul Zahra Aug 18 '14 at 10:04
I think that the right way it's `ctx.Database.Connection.Open();`. But unfortunately it doesn't help, the error still is :| – Monic Aug 18 '14 at 10:18
1

Look into this SO answer to help you examine the sql that is generated once on the server http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework The part on "Interceptors" sounds interesting, if you're using EF6.1 – Paul Zahra Aug 18 '14 at 10:59
For now I tested code (on my computer) from site you gave. Now I see how my query to database looks like. I've updated my question. – Monic Aug 18 '14 at 12:16
You've right. My local verstion of SQL Server is 2012 and server version is 2008R2. In Model.edmx I have `ProviderManifestToken="2008"` so I don't understand why it doesn't work. – Monic Aug 18 '14 at 13:00
@Monic which version of EntityFramework are you using? EF6 or 6.1 etc? Have you tried changing the token to 2012? – Paul Zahra Aug 18 '14 at 13:25
I have EF 6.1.1. I've tried change manually this version to 2012, but I've got error _Error 168: The provider did not return a ProviderManifest instance._ – Monic Aug 18 '14 at 14:07
@Monic EF6 using sql 2012 is not quite finished, one of their own people quotes going back to EF5 as a workaround! lol – Paul Zahra Aug 18 '14 at 14:18
You could try this... open VS and then switch to View=>Server Explorer and open the Server panel to create a connection, if this successfully, please copy the conn str of the remote one directly into your app or web.config. – Paul Zahra Aug 18 '14 at 14:19
I checked this out and I have the same connection string in these places. So should I really downgrade EF in my app? – Monic Aug 18 '14 at 19:25
If only as a test I would be tempted to try it, unless there is something specific to EF6 you require... you might even be using 3rd party products which don't fully support EF6 yet. – Paul Zahra Aug 19 '14 at 07:51
@Monic you could try installing Glimpse for EF6 to see if that highlights any issues, http://getglimpse.com/Extensions/ – Paul Zahra Aug 19 '14 at 07:54
I've installed it but how to use it? – Monic Aug 19 '14 at 10:13

score 0 · Answer 2 · answered Aug 18 '14 at 09:09

0

connectionString="metadata=res:

You didnt close up that "

answered Aug 18 '14 at 09:09

Horius

119
2
17

@Monic This answer is correct from what I see. Multiline strings don't work that way in c# – Vogel612 Aug 18 '14 at 11:06
I suggest you don't use the htmlentities, but rather escaped quotation marks `\"` or single quotation marks `'` depending on what you prefer – Vogel612 Aug 18 '14 at 11:11

Incorrect syntax near '('. Connection to database with entity framework

2 Answers2