locked
EF generating wrong syntax? RRS feed

  • Question

  • Hi all,

    I'm trying to retrieve a user based on his login name so I do:

    ctx.Users.First(o => o.Login == Login);

    But I get an exception with an inner exception of "Incorrect syntax near '('". Unfortunately I cannot see the generated SQL.

    Is there a way to view the sql before the above line is executed, and, why is this problem happening?

    Thanks,

    Ivan

    Wednesday, May 9, 2012 8:19 AM

Answers

  • Cyro75,

    Check out the link for different options on profiling the output from Entity Framework in order to see what T-SQL is being generated for the .First(): http://msdn.microsoft.com/en-us/magazine/gg490349.aspx, in addition to that, another option would be to run the query in LINQPad: http://www.linqpad.net/

    Also, this isn't really a solution, but if the .Where() executes fine against the DB then you could use the workaround of performing the .First() in memory:

    ctx.Users.Where(o => o.Login == Login).AsEnumerable().First();

    Regards,

    Tyler

    • Marked as answer by Cryo75 Monday, May 14, 2012 6:21 AM
    Wednesday, May 9, 2012 11:13 AM

All replies

  • You can use ToTraceString() on the ObjectQuery e.g.

    (ctx.Users.First(o => o.Login == Login) as ObjectQuery).ToTraceString();

    or use SQL Profiler (unfortunately not available with the Express editions)


    Wednesday, May 9, 2012 9:09 AM
  • I get the following compile error:

    Cannot convert type 'EntityModel.User' to 'System.Data.Objects.ObjectQuery' via a reference conversion, boxing conversion, unboxing conversion, wrapping conversion, or null type conversion

    Wednesday, May 9, 2012 9:15 AM
  • Hi Cyro75,

    You'll need to change the .First() to a .Where() in order to get an ObjectQuery. This won't get you the exact same query, but will likely give you an idea of what's going on:

    (ctx.Users.Where(o => o.Login == Login) as ObjectQuery).ToTraceString();

    Regards,

    Tyler

    Wednesday, May 9, 2012 10:27 AM
  • Well it says 'Expression cannot contain lambda expressions'
    Wednesday, May 9, 2012 10:31 AM
  • Ok, I see something now and the query executes fine in t-sql (but it's the 'Where' query not the 'First'. Any I definitely don't see '(' that it's complaining about.
    Wednesday, May 9, 2012 10:38 AM
  • Cyro75,

    Check out the link for different options on profiling the output from Entity Framework in order to see what T-SQL is being generated for the .First(): http://msdn.microsoft.com/en-us/magazine/gg490349.aspx, in addition to that, another option would be to run the query in LINQPad: http://www.linqpad.net/

    Also, this isn't really a solution, but if the .Where() executes fine against the DB then you could use the workaround of performing the .First() in memory:

    ctx.Users.Where(o => o.Login == Login).AsEnumerable().First();

    Regards,

    Tyler

    • Marked as answer by Cryo75 Monday, May 14, 2012 6:21 AM
    Wednesday, May 9, 2012 11:13 AM
  • The workaround works although I'm not that happy that the original code won't work because that's the basic loading query I do for all my entities!!!
    Wednesday, May 9, 2012 11:44 AM
  • The code seems no wrong

    Wednesday, May 9, 2012 3:58 PM
  • Hi Cryo75,

    Welcome to MSDN Forum.

    I don't find anything wrong from the code, it should works fine. I wrote a demo and try to repro the issue, but in my demo as below, it works. Please refer to it below.

    namespace ConsoleApplication3
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (TestEntities context = new TestEntities())
                {
                    string city = Console.ReadLine();
                    Team t = context.Teams.First(x => x.city == city);
                    Console.WriteLine(t.boss);
                    Console.Read();
                }
            }
        }
    }

    I also traced the T-SQL which generated by Entity Framework from SQL Server Profiler,

    exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[id] AS [id], 
    [Extent1].[name] AS [name], 
    [Extent1].[city] AS [city], 
    [Extent1].[boss] AS [boss]
    FROM [dbo].[Team] AS [Extent1]
    WHERE [Extent1].[city] = @p__linq__0',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'ShangHai'

    The result is,

    If possible, please upload your project to SkyDrive and post the link here, I will help you to test it and to find the root cause.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    • Edited by Allen_MSDN Wednesday, May 9, 2012 4:01 PM
    Wednesday, May 9, 2012 4:01 PM
  • I even tried this:

    User obj = ctx.Database.Users.First(o => o.ID == 1);

    but it doesn't work.

    Thursday, May 10, 2012 6:56 AM
  • Hi Cryo75,

    I'd appreciate if you can upload the project or a demo to SkyDrive and post the link here. I will help you to find the root cause.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 10, 2012 6:59 AM
  • Here's the link of a sample project:

    https://skydrive.live.com/redir.aspx?cid=ba8db0c447edb711&resid=BA8DB0C447EDB711!218&parid=BA8DB0C447EDB711!214&authkey=!AJjjvvSDyw2iwJ4

    Thursday, May 10, 2012 7:38 AM
  • Hi Cryo75,

    I have downloaded the project and tested, the First method works well on my machine. What you have upload is a Class Library, I create a new Console Application and include the Class Library, then write the code as below.

     class Program
        {
            static void Main(string[] args)
            {
                testEntities cxt = new testEntities();
                User u = cxt.Users.First(x => x.id_no == 1);
                Console.WriteLine(u.name_user);
                Console.Read();
            }
        }

    Everything works well.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 10, 2012 8:00 AM
  • I'm using SQL Server 2008 R2. Could it be this reason?
    Thursday, May 10, 2012 8:10 AM
  • Hi Cryo75,

    The server on my machine is SQL Server 2008 R2 too. The exception is very strange, it's a typical query statement, there's no reason it will throw the exception. Could you please run the project on other machines? Try to find whether it can works on other machines.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 10, 2012 8:13 AM
  • Unfortunately, only my machine is equipped with SQL Server 2008 R2 and Visual Studio C# Express 2010. Also, this problem happens on every other entity when I try to use 'First'.
    Thursday, May 10, 2012 8:15 AM
  • Hi Cryo75,

    From your code, you used Code First-DbContext, so just to call ToString method to see the "T-SQL":

    var query=ctx.Users.First(o => o.Login == Login);

    string tsql=query.ToString();

    Another try is: var query=ctx.User.Where(o => o.Login == Login).FirstOrDefault();

    If my suggestion still couldn't fit for your question, would you please feel freee to refine your exception information here."Incorrect syntax near '('" is a general database error.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 10, 2012 9:17 AM
  • Hi Cryo75,

    From your code, you used Code First-DbContext, so just to call ToString method to see the "T-SQL":

    var query=ctx.Users.First(o => o.Login == Login);

    string tsql=query.ToString();

    Another try is: var query=ctx.User.Where(o => o.Login == Login).FirstOrDefault();

    If my suggestion still couldn't fit for your question, would you please feel freee to refine your exception information here."Incorrect syntax near '('" is a general database error.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Even executing the 'var query...' line throws an exception. Here is the complete exception:

    System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Zeile 1: Falsche Syntax in der Nähe von '('.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       --- End of inner exception stack trace ---
       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__0[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Linq.Queryable.First[TSource](IQueryable`1 source, Expression`1 predicate)
       at AboServer.User.Load(String Login)
    Thursday, May 10, 2012 9:28 AM
  • I compiled and imported my assembly into LINQPad and tried executing the same statement. This is the sql generated using First:

    SELECT TOP (1)
    [Extent1].[id_no] AS [id_no],
    [Extent1].[id_no_primarygroup] AS [id_no_primarygroup],
    [Extent1].[name_user] AS [name_user],
    [Extent1].[name_login] AS [name_login],
    [Extent1].[pwd] AS [pwd],
    [Extent1].[pwd_change_allowed] AS [pwd_change_allowed],
    [Extent1].[login_until] AS [login_until],
    [Extent1].[login_time_from] AS [login_time_from],
    [Extent1].[login_time_until] AS [login_time_until],
    [Extent1].[id_no_usergroup] AS [id_no_usergroup],
    [Extent1].[tel] AS [tel],
    [Extent1].[fax] AS [fax],
    [Extent1].[email] AS [email],
    [Extent1].[mobile] AS [mobile],
    [Extent1].[short_sign] AS [short_sign],
    [Extent1].[position] AS [position]
    FROM [dbo].[users] AS [Extent1]
    WHERE N'sv' = [Extent1].[name_login]

    And this is the sql generated using Where:

    SELECT
    [Extent1].[id_no] AS [id_no],
    [Extent1].[id_no_primarygroup] AS [id_no_primarygroup],
    [Extent1].[name_user] AS [name_user],
    [Extent1].[name_login] AS [name_login],
    [Extent1].[pwd] AS [pwd],
    [Extent1].[pwd_change_allowed] AS [pwd_change_allowed],
    [Extent1].[login_until] AS [login_until],
    [Extent1].[login_time_from] AS [login_time_from],
    [Extent1].[login_time_until] AS [login_time_until],
    [Extent1].[id_no_usergroup] AS [id_no_usergroup],
    [Extent1].[tel] AS [tel],
    [Extent1].[fax] AS [fax],
    [Extent1].[email] AS [email],
    [Extent1].[mobile] AS [mobile],
    [Extent1].[short_sign] AS [short_sign],
    [Extent1].[position] AS [position]
    FROM [dbo].[users] AS [Extent1]
    WHERE N'sv' = [Extent1].[name_login]

    The database is currently running under SQL Server 2005 and the TOP (1) is the problem event in T-SQL. However, if I try TOP 1, it works fine.

    Can something be done to avoid this problem because we have clients that use SQL Server 2000, 2005 or 2008.

    Ivan


    • Edited by Cryo75 Thursday, May 10, 2012 10:51 AM
    Thursday, May 10, 2012 10:23 AM
  • This appears to be a known issue with EF, you'll need to modify the SSDL by hand to set it to compatibility mode for SQL Server 2000. See this link for more info: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/47d73f2d-65a7-4e6d-8a03-747280c1f829 

    Regards,

    Tyler

    Thursday, May 10, 2012 11:15 AM
  • Hi Cryo75,

    Would you mind letting me know whether @Tyler_A's solution can help?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 14, 2012 5:55 AM
  • Hi Cryo75,

    Would you mind letting me know whether @Tyler_A's solution can help?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Yes it works like a charm if you have SQL Server 2005 and 2008 and you don't want to have separate code or change the provider version in the model.

    Monday, May 14, 2012 6:22 AM