locked
Use UPPER function in CommandText in EntityDataSource RRS feed

  • Question

  • User-1355132948 posted

    Hi 

    I have a commandtext below within EntityDataSource I wish to use upper case function in the join clause in order to show all of my data, 

                                        CommandText="SELECT SES.SESSION_ID AS SESSION_ID, SES.FORM_ID, SES.AUDITOR, SES.AUDIT_DATE, SES.SERVICE_ID, SES.UNIT_ID,
                                                            USR.USER_ID, USR.NAME AS AUDITOR_NAME, USR.NAME_UPPER AS AUDITOR_NAME_UPPER,
                                                            FRM.FORM_ID, FRM.TITLE AS FORM_TITLE, FRM.TYPE AS FORM_TYPE,
                                                            SRV.SERVICE_ID, SRV.TITLE AS SERVICE_TITLE, SRV.TITLE_UPPER AS SERVICE_TITLE_UPPER, 
                                                            UNT.UNIT_ID, UNT.TITLE AS UNIT_TITLE, UNT.TITLE_UPPER AS UNIT_TITLE_UPPER
                                                     FROM AUDIT_SESSIONS AS SES 
                                                     JOIN USERS AS USR ON USR.USER_ID = SES.AUDITOR
                                                     JOIN FORMS AS FRM ON FRM.FORM_ID = SES.FORM_ID
                                                     LEFT OUTER JOIN SERVICES AS SRV ON SRV.SERVICE_ID = SES.SERVICE_ID
                                                     LEFT OUTER JOIN UNITS AS UNT ON UNT.UNIT_ID = SES.UNIT_ID"

    The problem is in USERS join condition need to be both upper or lower.

    I have tested the query in my oracle database (Toad) and UPPER() function is accepted, however in Visual Studio it doesn't accept it (nor AS clause).

    What is the CommandText Property ? is it normal SQL ? what can I use to fix my join query ?

    Thank You 

    Wednesday, May 30, 2018 9:42 AM

Answers

  • User-330142929 posted

    Hi Banan,

    I have tested the query in my oracle database (Toad) and UPPER() function is accepted, however in Visual Studio it doesn't accept it (nor AS clause).

    As far as I know, there is a difference between T-SQL and Entity-SQL. When we use Entity-SQL we usually use ToUpper, a ToLower string canonical functions.

    I have made a demo. wish it could be useful to you.

    Aspx.

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataSourceID="EntityDataSource1">
                    <Columns>
                        <asp:BoundField DataField="FirstName" HeaderText="FIRSTNAME" ReadOnly="True" SortExpression="FIRSTNAME" />
                        <asp:BoundField DataField="LastName" HeaderText="LASTNAME" SortExpression="LASTNAME" ReadOnly="True" />
                        <asp:BoundField DataField="OrderNo" HeaderText="OrderNo" ReadOnly="True" SortExpression="OrderNo" />
                    </Columns>
                   
                </asp:GridView>
                <asp:EntityDataSource ID="EntityDataSource1" runat="server" CommandText="SELECT ToUpper(P.FIRSTNAME) as FirstName, ToLower(P.LASTNAME) as LastName ,O.ORDERNO FROM PERSONS 
                    AS P INNER JOIN ORDERS AS O ON P.ID_P=O.ID_P" 
                    ConnectionString="name=DataStoreEntities2" DefaultContainerName="DataStoreEntities2" EnableFlattening="False" Select=""></asp:EntityDataSource>
    

    How it works.

    The CommandText property of the EntityDataSource control enables you to specify a query that uses a custom Entity SQL expression. Like the SELECT statement, the Entity SQL expression in the CommandText property creates a projection of the original data that is not updatable.

    In the other words, it is an Entity SQL command that defines the query not T-SQL statement.

    Here are some official link about T-SQL.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/entity-sql-language

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/string-canonical-functions

    Please feel free to let me know, if you have any question.

    Best Regards,

    Abraham

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 20, 2018 3:20 AM

All replies

  • User-492460945 posted

    Hi banan,

    The problem is in USERS join condition need to be both upper or lower.

    You want something like this?

    CommandText="SELECT SES.SESSION_ID AS SESSION_ID, SES.FORM_ID, SES.AUDITOR, SES.AUDIT_DATE, SES.SERVICE_ID, SES.UNIT_ID,
                                                            USR.USER_ID, USR.NAME AS AUDITOR_NAME, USR.NAME_UPPER AS AUDITOR_NAME_UPPER,
                                                            FRM.FORM_ID, FRM.TITLE AS FORM_TITLE, FRM.TYPE AS FORM_TYPE,
                                                            SRV.SERVICE_ID, SRV.TITLE AS SERVICE_TITLE, SRV.TITLE_UPPER AS SERVICE_TITLE_UPPER, 
                                                            UNT.UNIT_ID, UNT.TITLE AS UNIT_TITLE, UNT.TITLE_UPPER AS UNIT_TITLE_UPPER
                                                     FROM AUDIT_SESSIONS AS SES 
                                                     JOIN USERS AS USR ON UPPER(USR.USER_ID) = UPPER(SES.AUDITOR)
                                                     JOIN FORMS AS FRM ON FRM.FORM_ID = SES.FORM_ID
                                                     LEFT OUTER JOIN SERVICES AS SRV ON SRV.SERVICE_ID = SES.SERVICE_ID
                                                     LEFT OUTER JOIN UNITS AS UNT ON UNT.UNIT_ID = SES.UNIT_ID"

    Thanks,

    RajeshV.

    Thursday, May 31, 2018 1:38 PM
  • User-1355132948 posted

    Yes similar to this, I actually tried it but my system showed an error, what would be the proper way to format the command text.

    Sunday, June 3, 2018 12:31 PM
  • User-492460945 posted

    Hi banan,

    What is the error you received? Can you post it?

    Wednesday, June 6, 2018 9:32 AM
  • User-1355132948 posted

    'UPPER' cannot be resolved into a valid type or function. Near simple identifier, line 9, column 71.
    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.EntitySqlException: 'UPPER' cannot be resolved into a valid type or function. Near simple identifier, line 9, column 71.

    Source Error:


    Line 235: etsAuditSessions.AutoGenerateWhereClause = false;
    Line 236: etsAuditSessions.Where = whereClause;
    Line 237: gvAuditSessions.DataBind();
    Line 238: }
    Line 239: }

    Source File: d:\In-House Applications\QMIC_Dev\AuditForms\AuditFormList.aspx.cs Line: 237

    Stack Trace:


    [EntitySqlException: 'UPPER' cannot be resolved into a valid type or function. Near simple identifier, line 9, column 71.]
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertMethodExpr(MethodExpr methodExpr, Boolean includeInlineFunctions, SemanticResolver sr) +2710590
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertMethodExpr(Node expr, SemanticResolver sr) +47
    System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionsWithUntypedNulls(Node leftAst, Node rightAst, ErrorContext errCtx, Func`1 formatMessage, SemanticResolver sr) +26
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertEqualCompArgs(BuiltInExpr astBuiltInExpr, SemanticResolver sr) +161
    System.Data.Common.EntitySql.<>c.<CreateBuiltInExprConverter>b__97_10(BuiltInExpr bltInExpr, SemanticResolver sr) +16
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertBuiltIn(Node astExpr, SemanticResolver sr) +110
    System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpression(Node astExpr, SemanticResolver sr) +13
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessJoinClauseItem(JoinClauseItem joinClause, SemanticResolver sr, List`1& scopeEntries) +585
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +104
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessJoinClauseItem(JoinClauseItem joinClause, SemanticResolver sr, List`1& scopeEntries) +73
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +104
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessJoinClauseItem(JoinClauseItem joinClause, SemanticResolver sr, List`1& scopeEntries) +73
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +104
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessJoinClauseItem(JoinClauseItem joinClause, SemanticResolver sr, List`1& scopeEntries) +73
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +104
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClause(FromClause fromClause, SemanticResolver sr) +145
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQueryExpr(Node expr, SemanticResolver sr) +150
    System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertParenExpr(Node astExpr, SemanticResolver sr) +58
    System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpression(Node astExpr, SemanticResolver sr) +13
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessAliasedFromClauseItem(AliasedExpr aliasedExpr, SemanticResolver sr, List`1& scopeEntries) +26
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +61
    System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClause(FromClause fromClause, SemanticResolver sr) +145
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQueryExpr(Node expr, SemanticResolver sr) +150
    System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
    System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQueryStatementToDbExpression(Statement astStatement, SemanticResolver sr, List`1& functionDefs) +71
    System.Data.Common.EntitySql.SemanticAnalyzer.AnalyzeQueryCommand(Node astExpr) +51
    System.Data.Common.EntitySql.<>c.<AnalyzeQueryExpressionSemantics>b__5_0(SemanticAnalyzer analyzer, Node astExpr) +17
    System.Data.Common.EntitySql.CqlQuery.AnalyzeSemanticsCommon(Node astExpr, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables, Func`3 analysisFunction) +162
    System.Data.Common.EntitySql.CqlQuery.AnalyzeQueryExpressionSemantics(Node astQueryCommand, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables) +180
    System.Data.Common.EntitySql.<>c__DisplayClass1_0.<CompileQueryCommandLambda>b__0(Node astCommand, ParserOptions validatedParserOptions) +26
    System.Data.Common.EntitySql.CqlQuery.CompileCommon(String commandText, Perspective perspective, ParserOptions parserOptions, Func`3 compilationFunction) +93
    System.Data.Common.EntitySql.CqlQuery.CompileQueryCommandLambda(String queryCommandText, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables) +100
    System.Data.Objects.EntitySqlQueryState.Parse() +463
    System.Data.Objects.EntitySqlQueryState.GetResultType() +9
    System.Data.Objects.ObjectQuery.GetResultType() +58
    System.Web.UI.WebControls.EntityDataSourceQueryBuilder`1.BuildBasicQuery(ObjectContext context, Boolean computeCount) +107
    System.Web.UI.WebControls.EntityDataSourceView.ExecuteSelectTyped(DataSourceSelectArguments arguments, Creator qbConstructor) +353
    System.Web.UI.WebControls.EntityDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +605
    System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +22
    System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
    System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
    System.Web.UI.WebControls.GridView.DataBind() +9
    QMIC.AuditForms.AuditFormList.Filter() in d:\In-House Applications\QMIC_Dev\AuditForms\AuditFormList.aspx.cs:237
    QMIC.AuditForms.AuditFormList.Page_Load(Object sender, EventArgs e) in d:\In-House Applications\QMIC_Dev\AuditForms\AuditFormList.aspx.cs:132
    System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
    System.Web.UI.Control.OnLoad(EventArgs e) +95
    System.Web.UI.Control.LoadRecursive() +59
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2952

    Thursday, June 7, 2018 7:28 AM
  • User-330142929 posted

    Hi Banan,

    I have tested the query in my oracle database (Toad) and UPPER() function is accepted, however in Visual Studio it doesn't accept it (nor AS clause).

    As far as I know, there is a difference between T-SQL and Entity-SQL. When we use Entity-SQL we usually use ToUpper, a ToLower string canonical functions.

    I have made a demo. wish it could be useful to you.

    Aspx.

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataSourceID="EntityDataSource1">
                    <Columns>
                        <asp:BoundField DataField="FirstName" HeaderText="FIRSTNAME" ReadOnly="True" SortExpression="FIRSTNAME" />
                        <asp:BoundField DataField="LastName" HeaderText="LASTNAME" SortExpression="LASTNAME" ReadOnly="True" />
                        <asp:BoundField DataField="OrderNo" HeaderText="OrderNo" ReadOnly="True" SortExpression="OrderNo" />
                    </Columns>
                   
                </asp:GridView>
                <asp:EntityDataSource ID="EntityDataSource1" runat="server" CommandText="SELECT ToUpper(P.FIRSTNAME) as FirstName, ToLower(P.LASTNAME) as LastName ,O.ORDERNO FROM PERSONS 
                    AS P INNER JOIN ORDERS AS O ON P.ID_P=O.ID_P" 
                    ConnectionString="name=DataStoreEntities2" DefaultContainerName="DataStoreEntities2" EnableFlattening="False" Select=""></asp:EntityDataSource>
    

    How it works.

    The CommandText property of the EntityDataSource control enables you to specify a query that uses a custom Entity SQL expression. Like the SELECT statement, the Entity SQL expression in the CommandText property creates a projection of the original data that is not updatable.

    In the other words, it is an Entity SQL command that defines the query not T-SQL statement.

    Here are some official link about T-SQL.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/entity-sql-language

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/string-canonical-functions

    Please feel free to let me know, if you have any question.

    Best Regards,

    Abraham

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 20, 2018 3:20 AM
  • User-1355132948 posted

    Many thanks very good explanation 

    Tuesday, June 26, 2018 12:06 PM