Use UPPER function in CommandText in EntityDataSource

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 

Answer 1

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.

Answer 2

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.

Answer 3

User-492460945 posted

Hi banan,

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

Answer 4

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

Answer 5

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

Answer 6

User-1355132948 posted

Many thanks very good explanation