none
Entity SQL Strangeness RRS feed

  • Question

  • I'm getting data out of my object context using .CreateQuery<DbDataRecord>(sqlStatement, parameterValues)

    This works perfectly.  However there are some duplicate records...

    SELECT  record.home_city, record.home_stabb FROM Employees AS record  ORDER BY record.home_city SKIP 0 LIMIT 15

    So I thought this would remove the duplicates:

    SELECT  DISTINCT record.home_city, record.home_stabb FROM Employees AS record  ORDER BY record.home_city SKIP 0 LIMIT 15

    Exact same statement, exact same object context.  However, when I add in the DISTINCT Modifier, I get the exception below.  I know all of the columns are correct because the first statement works perfectly.

    Is there something that I'm missing?  This makes no sense to me.

    System.Data.EntitySqlException was unhandled by user code
      Message='record.home_city' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near member access expression, line 1, column 95.
      Source=System.Data.Entity
      Column=95
      ErrorContext=member access expression, line 1, column 95
      ErrorDescription='record.home_city' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly.
      Line=1
      StackTrace:
           at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpression(Node astExpr, SemanticResolver sr)
           at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessOrderByClause(DbExpressionBinding source, QueryExpr queryExpr, Boolean& queryProjectionProcessed, SemanticResolver sr)
           at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQuery(Node expr, SemanticResolver sr)
           at System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr)
           at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpression(Node astExpr, SemanticResolver sr)
           at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQueryStatementToDbExpression(Statement astStatement, SemanticResolver sr)
           at System.Data.Common.EntitySql.SemanticAnalyzer.AnalyzeQueryCommand(Node astExpr)
           at System.Data.Common.EntitySql.CqlQuery.<AnalyzeQueryExpressionSemantics>b__8(SemanticAnalyzer analyzer, Node astExpr)
           at System.Data.Common.EntitySql.CqlQuery.AnalyzeSemanticsCommon[TResult](Node astExpr, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables, Func`3 analysisFunction)
           at System.Data.Common.EntitySql.CqlQuery.AnalyzeQueryExpressionSemantics(Node astQueryCommand, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables)
           at System.Data.Common.EntitySql.CqlQuery.<>c__DisplayClass4.<CompileQueryCommandLambda>b__3(Node astCommand, ParserOptions validatedParserOptions)
           at System.Data.Common.EntitySql.CqlQuery.CompileCommon[TResult](String commandText, Perspective perspective, ParserOptions parserOptions, Func`3 compilationFunction)
           at System.Data.Common.EntitySql.CqlQuery.CompileQueryCommandLambda(String queryCommandText, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables)
           at System.Data.Objects.EntitySqlQueryState.Parse()
           at System.Data.Objects.EntitySqlQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
           at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
           at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
           at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
           at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
           at NSP.OnlineEnrollment.DB.SQL.DataGrids.EnrolleeGrid.ExecuteEntityQuery(String selectStatement, ObjectParameter[] parameters) in C:\tfsprod\TeamFKA\Services\PreOnlineEnrollmentMain\NSP.OnlineEnrollment.DB\SQL\DataGrids\EnrolleeGrid.cs:line 18
           at NSP.MVC.JqGrid.DynamicEntityQuery`1.Execute() in C:\tfsprod\TeamFKA\Services\PreOnlineEnrollmentMain\NSP.MVC.JqGrid\DynamicEntityQuery.cs:line 135
           at NSP.MVC.JqGrid.GridDataModel.DoSearch() in C:\tfsprod\TeamFKA\Services\PreOnlineEnrollmentMain\NSP.MVC.JqGrid\GridDataModel.cs:line 55
           at IndividualPriming.Db.Tests.EnrolleeGridTest.ExecuteEntityQueryTest() in C:\tfsprod\TeamFKA\Services\PreOnlineEnrollmentMain\IndividualPriming.Db.Tests\EnrolleeGridTest.cs:line 82
      InnerException:

    Monday, December 3, 2012 3:23 PM

All replies

  • Hi Houtexwebdev,

    Welcome to the MSDN forum.

    I tested your problem on my side. It works normally without any exception. Could you please provide some related code snippet to help us reproduce your problem?

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 5, 2012 8:44 AM