mcs/class/referencesource/System.Data.Entity/System/Data/Objects/Internal/EntitySqlQueryBuilder.cs

   1 //---------------------------------------------------------------------
   2 // <copyright file="EntitySqlQueryBuilder.cs" company="Microsoft">
   3 //      Copyright (c) Microsoft Corporation.  All rights reserved.
   4 // </copyright>
   5 //
   6 // @owner  Microsoft
   7 //---------------------------------------------------------------------
   8
   9 namespace System.Data.Objects.Internal
  10 {
  11     using System.Collections.Generic;
  12     using System.Data.Common;
  13     using System.Data.Common.Utils;
  14     using System.Data.Metadata.Edm;
  15     using System.Diagnostics;
  16     using System.Text;
  17
  18     /// <summary>
  19     /// Provides Entity-SQL query building services for <see cref="EntitySqlQueryState"/>.
  20     /// Knowledge of how to compose Entity-SQL fragments using query builder operators resides entirely in this class.
  21     /// </summary>
  22     internal static class EntitySqlQueryBuilder
  23     {
  24         /// <summary>
  25         /// Helper method to extract the Entity-SQL command text from an <see cref="ObjectQueryState"/> instance if that
  26         /// instance models an Entity-SQL-backed ObjectQuery, or to throw an exception indicating that query builder methods
  27         /// are not supported on this query.
  28         /// </summary>
  29         /// <param name="query">The instance from which the Entity-SQL command text should be retrieved</param>
  30         /// <returns>The Entity-SQL command text, if the specified query state instance is based on Entity-SQL</returns>
  31         /// <exception cref="NotSupportedException">
  32         ///     If the specified instance is not based on Entity-SQL command text, and so does not support Entity-SQL query builder methods
  33         /// </exception>
  34         private static string GetCommandText(ObjectQueryState query)
  35         {
  36             string commandText = null;
  37             if(!query.TryGetCommandText(out commandText))
  38             {
  39                 throw EntityUtil.NotSupported(System.Data.Entity.Strings.ObjectQuery_QueryBuilder_NotSupportedLinqSource);
  40             }
  41
  42             return commandText;
  43         }
  44
  45         /// <summary>
  46         /// Merges <see cref="ObjectParameter"/>s from a source ObjectQuery with ObjectParameters specified as an argument to a builder method.
  47         /// A new <see cref="ObjectParameterCollection"/> is returned that contains copies of parameters from both <paramref name="sourceQueryParams"/> and <paramref name="builderMethodParams"/>.
  48         /// </summary>
  49         /// <param name="context">The <see cref="ObjectContext"/> to use when constructing the new parameter collection</param>
  50         /// <param name="sourceQueryParams">ObjectParameters from the ObjectQuery on which the query builder method was called</param>
  51         /// <param name="builderMethodParams">ObjectParameters that were specified as an argument to the builder method</param>
  52         /// <returns>A new ObjectParameterCollection containing copies of all parameters</returns>
  53         private static ObjectParameterCollection MergeParameters(ObjectContext context, ObjectParameterCollection sourceQueryParams, ObjectParameter[] builderMethodParams)
  54         {
  55             Debug.Assert(builderMethodParams != null, "params array argument should not be null");
  56             if (sourceQueryParams == null && builderMethodParams.Length == 0)
  57             {
  58                 return null;
  59             }
  60
  61             ObjectParameterCollection mergedParams = ObjectParameterCollection.DeepCopy(sourceQueryParams);
  62             if (mergedParams == null)
  63             {
  64                 mergedParams = new ObjectParameterCollection(context.Perspective);
  65             }
  66
  67             foreach (ObjectParameter builderParam in builderMethodParams)
  68             {
  69                 mergedParams.Add(builderParam);
  70             }
  71
  72             return mergedParams;
  73         }
  74
  75         /// <summary>
  76         /// Merges <see cref="ObjectParameter"/>s from two ObjectQuery arguments to SetOp builder methods (Except, Intersect, Union, UnionAll).
  77         /// A new <see cref="ObjectParameterCollection"/> is returned that contains copies of parameters from both <paramref name="query1Params"/> and <paramref name="query2Params"/>.
  78         /// </summary>
  79         /// <param name="query1Params">ObjectParameters from the first ObjectQuery argument (on which the query builder method was called)</param>
  80         /// <param name="query2Params">ObjectParameters from the second ObjectQuery argument (specified as an argument to the builder method)</param>
  81         /// <returns>A new ObjectParameterCollection containing copies of all parameters</returns>
  82         private static ObjectParameterCollection MergeParameters(ObjectParameterCollection query1Params, ObjectParameterCollection query2Params)
  83         {
  84             if (query1Params == null && query2Params == null)
  85             {
  86                 return null;
  87             }
  88
  89             ObjectParameterCollection mergedParams;
  90             ObjectParameterCollection sourceParams;
  91             if (query1Params != null)
  92             {
  93                 mergedParams = ObjectParameterCollection.DeepCopy(query1Params);
  94                 sourceParams = query2Params;
  95             }
  96             else
  97             {
  98                 mergedParams = ObjectParameterCollection.DeepCopy(query2Params);
  99                 sourceParams = query1Params;
 100             }
 101
 102             if (sourceParams != null)
 103             {
 104                 foreach (ObjectParameter sourceParam in sourceParams)
 105                 {
 106                     mergedParams.Add(sourceParam.ShallowCopy());
 107                 }
 108             }
 109
 110             return mergedParams;
 111         }
 112
 113         private static ObjectQueryState NewBuilderQuery(ObjectQueryState sourceQuery, Type elementType, StringBuilder queryText, Span newSpan, IEnumerable<ObjectParameter> enumerableParams)
 114         {
 115             return NewBuilderQuery(sourceQuery, elementType, queryText, false, newSpan, enumerableParams);
 116         }
 117
 118         private static ObjectQueryState NewBuilderQuery(ObjectQueryState sourceQuery, Type elementType, StringBuilder queryText, bool allowsLimit, Span newSpan, IEnumerable<ObjectParameter> enumerableParams)
 119         {
 120             ObjectParameterCollection queryParams = enumerableParams as ObjectParameterCollection;
 121             if (queryParams == null && enumerableParams != null)
 122             {
 123                 queryParams = new ObjectParameterCollection(sourceQuery.ObjectContext.Perspective);
 124                 foreach (ObjectParameter objectParam in enumerableParams)
 125                 {
 126                     queryParams.Add(objectParam);
 127                 }
 128             }
 129
 130             EntitySqlQueryState newState = new EntitySqlQueryState(elementType, queryText.ToString(), allowsLimit, sourceQuery.ObjectContext, queryParams, newSpan);
 131
 132             sourceQuery.ApplySettingsTo(newState);
 133
 134             return newState;
 135         }
 136
 137         // Note that all query builder string constants contain embedded newlines to prevent manipulation of the
 138         // query text by single line comments (--) that might appear in user-supplied portions of the string such
 139         // as a filter predicate, projection list, etc.
 140
 141         #region SetOp Helpers
 142
 143         private const string _setOpEpilog =
 144 @"
 145 )";
 146
 147         private const string _setOpProlog =
 148 @"(
 149 ";
 150
 151         // SetOp helper - note that this doesn't merge Spans, since Except uses the original query's Span
 152         // while Intersect/Union/UnionAll use the merged Span.
 153         private static ObjectQueryState BuildSetOp(ObjectQueryState leftQuery, ObjectQueryState rightQuery, Span newSpan, string setOp)
 154         {
 155             // Assert that the arguments aren't null (should have been verified by ObjectQuery)
 156             Debug.Assert(leftQuery != null, "Left query is null?");
 157             Debug.Assert(rightQuery != null, "Right query is null?");
 158             Debug.Assert(leftQuery.ElementType.Equals(rightQuery.ElementType), "Incompatible element types in arguments to Except<T>/Intersect<T>/Union<T>/UnionAll<T>?");
 159
 160             // Retrieve the left and right arguments to the set operation -
 161             // this will throw if either input query is not an Entity-SQL query.
 162             string left = GetCommandText(leftQuery);
 163             string right = GetCommandText(rightQuery);
 164
 165             // ObjectQuery arguments must be associated with the same ObjectContext instance as the implemented query
 166             if (!object.ReferenceEquals(leftQuery.ObjectContext, rightQuery.ObjectContext))
 167             {
 168                 throw EntityUtil.Argument(System.Data.Entity.Strings.ObjectQuery_QueryBuilder_InvalidQueryArgument, "query");
 169             }
 170
 171             // Create a string builder only large enough to contain the new query text
 172             int queryLength = _setOpProlog.Length + left.Length + setOp.Length + right.Length + _setOpEpilog.Length;
 173             StringBuilder builder = new StringBuilder(queryLength);
 174
 175             // Build the new query
 176             builder.Append(_setOpProlog);
 177             builder.Append(left);
 178             builder.Append(setOp);
 179             builder.Append(right);
 180             builder.Append(_setOpEpilog);
 181
 182             // Create a new query implementation and apply the state of this implementation to it.
 183             // The Span of the query argument will be merged into the new query's Span by the caller, iff the Set Op is NOT Except.
 184             // See the Except, Intersect, Union and UnionAll methods in this class for examples.
 185             return NewBuilderQuery(leftQuery, leftQuery.ElementType, builder, newSpan, MergeParameters(leftQuery.Parameters, rightQuery.Parameters));
 186         }
 187         #endregion
 188
 189         #region Select/SelectValue Helpers
 190
 191         private const string _fromOp =
 192 @"
 193 FROM (
 194 ";
 195
 196         private const string _asOp =
 197 @"
 198 ) AS ";
 199
 200         private static ObjectQueryState BuildSelectOrSelectValue(ObjectQueryState query, string alias, string projection, ObjectParameter[] parameters, string projectOp, Type elementType)
 201         {
 202             Debug.Assert(!StringUtil.IsNullOrEmptyOrWhiteSpace(alias), "Invalid alias");
 203             Debug.Assert(!StringUtil.IsNullOrEmptyOrWhiteSpace(projection), "Invalid projection");
 204
 205             string queryText = GetCommandText(query);
 206
 207             // Build the new query string - "<project op> <projection> FROM (<this query>) AS <alias>"
 208             int queryLength = projectOp.Length +
 209                               projection.Length +
 210                               _fromOp.Length +
 211                               queryText.Length +
 212                               _asOp.Length +
 213                               alias.Length;
 214
 215             StringBuilder builder = new StringBuilder(queryLength);
 216             builder.Append(projectOp);
 217             builder.Append(projection);
 218             builder.Append(_fromOp);
 219             builder.Append(queryText);
 220             builder.Append(_asOp);
 221             builder.Append(alias);
 222
 223             // Create a new EntitySqlQueryImplementation that uses the new query as its command text.
 224             // Span should not be carried over from a Select or SelectValue operation.
 225             return NewBuilderQuery(query, elementType, builder, null, MergeParameters(query.ObjectContext, query.Parameters, parameters));
 226         }
 227
 228         #endregion
 229
 230         #region OrderBy/Where Helper
 231
 232         private static ObjectQueryState BuildOrderByOrWhere(ObjectQueryState query, string alias, string predicateOrKeys, ObjectParameter[] parameters, string op, string skipCount, bool allowsLimit)
 233         {
 234             Debug.Assert(!StringUtil.IsNullOrEmptyOrWhiteSpace(alias), "Invalid alias");
 235             Debug.Assert(!StringUtil.IsNullOrEmptyOrWhiteSpace(predicateOrKeys), "Invalid predicate/keys");
 236             Debug.Assert(null == skipCount || op == _orderByOp, "Skip clause used with WHERE operator?");
 237
 238             string queryText = GetCommandText(query);
 239
 240             // Build the new query string:
 241             // Either: "SELECT VALUE <alias> FROM (<this query>) AS <alias> WHERE <predicate>"
 242             //  (for Where)
 243             // Or:  "SELECT VALUE <alias> FROM (<this query>) AS <alias> ORDER BY <keys> <optional: SKIP <skip>>"
 244             // Depending on the value of 'op'
 245             int queryLength = _selectValueOp.Length +
 246                               alias.Length +
 247                               _fromOp.Length +
 248                               queryText.Length +
 249                               _asOp.Length +
 250                               alias.Length +
 251                               op.Length +
 252                               predicateOrKeys.Length;
 253
 254             if (skipCount != null)
 255             {
 256                 queryLength += (_skipOp.Length + skipCount.Length);
 257             }
 258
 259             StringBuilder builder = new StringBuilder(queryLength);
 260             builder.Append(_selectValueOp);
 261             builder.Append(alias);
 262             builder.Append(_fromOp);
 263             builder.Append(queryText);
 264             builder.Append(_asOp);
 265             builder.Append(alias);
 266             builder.Append(op);
 267             builder.Append(predicateOrKeys);
 268             if (skipCount != null)
 269             {
 270                 builder.Append(_skipOp);
 271                 builder.Append(skipCount);
 272             }
 273
 274             // Create a new EntitySqlQueryImplementation that uses the new query as its command text.
 275             // Span is carried over, no adjustment is needed.
 276             return NewBuilderQuery(query, query.ElementType, builder, allowsLimit, query.Span, MergeParameters(query.ObjectContext, query.Parameters,  parameters));
 277         }
 278
 279         #endregion
 280
 281
 282         #region Distinct
 283
 284         private const string _distinctProlog =
 285 @"SET(
 286 ";
 287
 288         private const string _distinctEpilog =
 289 @"
 290 )";
 291
 292         internal static ObjectQueryState Distinct(ObjectQueryState query)
 293         {
 294             // Build the new query string - "SET(<this query>)"
 295             string queryText = GetCommandText(query);
 296             StringBuilder builder = new StringBuilder(_distinctProlog.Length + queryText.Length + _distinctEpilog.Length);
 297             builder.Append(_distinctProlog);
 298             builder.Append(queryText);
 299             builder.Append(_distinctEpilog);
 300
 301             // Span is carried over, no adjustment is needed
 302
 303             return NewBuilderQuery(query, query.ElementType, builder, query.Span, ObjectParameterCollection.DeepCopy(query.Parameters));
 304         }
 305
 306         #endregion
 307
 308         #region Except
 309
 310         private const string _exceptOp =
 311 @"
 312 ) EXCEPT (
 313 ";
 314
 315         internal static ObjectQueryState Except(ObjectQueryState leftQuery, ObjectQueryState rightQuery)
 316         {
 317             // Call the SetOp helper.
 318             // Span is taken from the leftmost query.
 319             return EntitySqlQueryBuilder.BuildSetOp(leftQuery, rightQuery, leftQuery.Span, _exceptOp);
 320         }
 321
 322         #endregion
 323
 324         #region GroupBy
 325
 326         private const string _groupByOp =
 327 @"
 328 GROUP BY
 329 ";
 330
 331         internal static ObjectQueryState GroupBy(ObjectQueryState query, string alias, string keys, string projection, ObjectParameter[] parameters)
 332         {
 333             Debug.Assert(!StringUtil.IsNullOrEmptyOrWhiteSpace(alias), "Invalid alias");
 334             Debug.Assert(!StringUtil.IsNullOrEmptyOrWhiteSpace(alias), "Invalid keys");
 335             Debug.Assert(!StringUtil.IsNullOrEmptyOrWhiteSpace(projection), "Invalid projection");
 336
 337             string queryText = GetCommandText(query);
 338
 339             // Build the new query string:
 340             // "SELECT <projection> FROM (<this query>) AS <alias> GROUP BY <keys>"
 341             int queryLength = _selectOp.Length +
 342                               projection.Length +
 343                               _fromOp.Length +
 344                               queryText.Length +
 345                               _asOp.Length +
 346                               alias.Length +
 347                               _groupByOp.Length +
 348                               keys.Length;
 349
 350             StringBuilder builder = new StringBuilder(queryLength);
 351             builder.Append(_selectOp);
 352             builder.Append(projection);
 353             builder.Append(_fromOp);
 354             builder.Append(queryText);
 355             builder.Append(_asOp);
 356             builder.Append(alias);
 357             builder.Append(_groupByOp);
 358             builder.Append(keys);
 359
 360             // Create a new EntitySqlQueryImplementation that uses the new query as its command text.
 361             // Span should not be carried over from a GroupBy operation.
 362             return NewBuilderQuery(query, typeof(DbDataRecord), builder, null, MergeParameters(query.ObjectContext, query.Parameters, parameters));
 363         }
 364
 365         #endregion
 366
 367         #region Intersect
 368
 369         private const string _intersectOp =
 370 @"
 371 ) INTERSECT (
 372 ";
 373
 374         internal static ObjectQueryState Intersect(ObjectQueryState leftQuery, ObjectQueryState rightQuery)
 375         {
 376             // Ensure the Spans of the query arguments are merged into the new query's Span.
 377             Span newSpan = Span.CopyUnion(leftQuery.Span, rightQuery.Span);
 378             // Call the SetOp helper.
 379             return BuildSetOp(leftQuery, rightQuery, newSpan, _intersectOp);
 380         }
 381
 382         #endregion
 383
 384         #region OfType
 385
 386         private const string _ofTypeProlog =
 387 @"OFTYPE(
 388 (
 389 ";
 390
 391         private const string _ofTypeInfix =
 392 @"
 393 ),
 394 [";
 395
 396         private const string _ofTypeInfix2 = "].[";
 397
 398         private const string _ofTypeEpilog =
 399 @"]
 400 )";
 401
 402         internal static ObjectQueryState OfType(ObjectQueryState query, EdmType newType, Type clrOfType)
 403         {
 404             Debug.Assert(newType != null, "OfType cannot be null");
 405             Debug.Assert(Helper.IsEntityType(newType) || Helper.IsComplexType(newType), "OfType must be Entity or Complex type");
 406
 407             string queryText = GetCommandText(query);
 408
 409             // Build the new query string - "OFTYPE((<query>), [<type namespace>].[<type name>])"
 410             int queryLength = _ofTypeProlog.Length +
 411                               queryText.Length +
 412                               _ofTypeInfix.Length +
 413                               newType.NamespaceName.Length +
 414                               (newType.NamespaceName != string.Empty ? _ofTypeInfix2.Length : 0) +
 415                               newType.Name.Length +
 416                               _ofTypeEpilog.Length;
 417
 418             StringBuilder builder = new StringBuilder(queryLength);
 419             builder.Append(_ofTypeProlog);
 420             builder.Append(queryText);
 421             builder.Append(_ofTypeInfix);
 422             if (newType.NamespaceName != string.Empty)
 423             {
 424                 builder.Append(newType.NamespaceName);
 425                 builder.Append(_ofTypeInfix2);
 426             }
 427             builder.Append(newType.Name);
 428             builder.Append(_ofTypeEpilog);
 429
 430             // Create a new EntitySqlQueryImplementation that uses the new query as its command text.
 431             // Span is carried over, no adjustment is needed
 432             return NewBuilderQuery(query, clrOfType, builder, query.Span, ObjectParameterCollection.DeepCopy(query.Parameters));
 433         }
 434
 435         #endregion
 436
 437         #region OrderBy
 438
 439         private const string _orderByOp =
 440 @"
 441 ORDER BY
 442 ";
 443
 444         internal static ObjectQueryState OrderBy(ObjectQueryState query, string alias, string keys, ObjectParameter[] parameters)
 445         {
 446             return BuildOrderByOrWhere(query, alias, keys, parameters, _orderByOp, null, true);
 447         }
 448
 449         #endregion
 450
 451         #region Select
 452
 453         private const string _selectOp = "SELECT ";
 454
 455         internal static ObjectQueryState Select(ObjectQueryState query, string alias, string projection, ObjectParameter[] parameters)
 456         {
 457             return BuildSelectOrSelectValue(query, alias, projection, parameters, _selectOp, typeof(DbDataRecord));
 458         }
 459
 460         #endregion
 461
 462         #region SelectValue
 463
 464         private const string _selectValueOp = "SELECT VALUE ";
 465
 466         internal static ObjectQueryState SelectValue(ObjectQueryState query, string alias, string projection, ObjectParameter[] parameters, Type projectedType)
 467         {
 468             return BuildSelectOrSelectValue(query, alias, projection, parameters, _selectValueOp, projectedType);
 469         }
 470
 471         #endregion
 472
 473         #region Skip
 474
 475         private const string _skipOp =
 476 @"
 477 SKIP
 478 ";
 479
 480         internal static ObjectQueryState Skip(ObjectQueryState query, string alias, string keys, string count, ObjectParameter[] parameters)
 481         {
 482             Debug.Assert(!StringUtil.IsNullOrEmptyOrWhiteSpace(count), "Invalid skip count");
 483             return BuildOrderByOrWhere(query, alias, keys, parameters, _orderByOp, count, true);
 484         }
 485
 486         #endregion
 487
 488         #region Top
 489
 490         private const string _limitOp =
 491 @"
 492 LIMIT
 493 ";
 494         private const string _topOp =
 495 @"SELECT VALUE TOP(
 496 ";
 497
 498         private const string _topInfix =
 499 @"
 500 ) ";
 501
 502         internal static ObjectQueryState Top(ObjectQueryState query, string alias, string count, ObjectParameter[] parameters)
 503         {
 504             int queryLength = count.Length;
 505             string queryText = GetCommandText(query);
 506             bool limitAllowed = ((EntitySqlQueryState)query).AllowsLimitSubclause;
 507
 508             if (limitAllowed)
 509             {
 510                 // Build the new query string:
 511                 // <this query> LIMIT <count>
 512                 queryLength += (queryText.Length +
 513                                 _limitOp.Length
 514                     // + count.Length is added above
 515                                 );
 516             }
 517             else
 518             {
 519                 // Build the new query string:
 520                 // "SELECT VALUE TOP(<count>) <alias> FROM (<this query>) AS <alias>"
 521                 queryLength += (_topOp.Length +
 522                     // count.Length + is added above
 523                                _topInfix.Length +
 524                                alias.Length +
 525                                _fromOp.Length +
 526                                queryText.Length +
 527                                _asOp.Length +
 528                                alias.Length);
 529             }
 530
 531             StringBuilder builder = new StringBuilder(queryLength);
 532             if (limitAllowed)
 533             {
 534                 builder.Append(queryText);
 535                 builder.Append(_limitOp);
 536                 builder.Append(count);
 537             }
 538             else
 539             {
 540                 builder.Append(_topOp);
 541                 builder.Append(count);
 542                 builder.Append(_topInfix);
 543                 builder.Append(alias);
 544                 builder.Append(_fromOp);
 545                 builder.Append(queryText);
 546                 builder.Append(_asOp);
 547                 builder.Append(alias);
 548             }
 549
 550             // Create a new EntitySqlQueryImplementation that uses the new query as its command text.
 551             // Span is carried over, no adjustment is needed.
 552             return NewBuilderQuery(query, query.ElementType, builder, query.Span, MergeParameters(query.ObjectContext, query.Parameters, parameters));
 553         }
 554
 555         #endregion
 556
 557         #region Union
 558
 559         private const string _unionOp =
 560 @"
 561 ) UNION (
 562 ";
 563
 564         internal static ObjectQueryState Union(ObjectQueryState leftQuery, ObjectQueryState rightQuery)
 565         {
 566             // Ensure the Spans of the query arguments are merged into the new query's Span.
 567             Span newSpan = Span.CopyUnion(leftQuery.Span, rightQuery.Span);
 568             // Call the SetOp helper.
 569             return BuildSetOp(leftQuery, rightQuery, newSpan, _unionOp);
 570         }
 571
 572         #endregion
 573
 574         #region Union
 575
 576         private const string _unionAllOp =
 577 @"
 578 ) UNION ALL (
 579 ";
 580
 581         internal static ObjectQueryState UnionAll(ObjectQueryState leftQuery, ObjectQueryState rightQuery)
 582         {
 583             // Ensure the Spans of the query arguments are merged into the new query's Span.
 584             Span newSpan = Span.CopyUnion(leftQuery.Span, rightQuery.Span);
 585             // Call the SetOp helper.
 586             return BuildSetOp(leftQuery, rightQuery, newSpan, _unionAllOp);
 587         }
 588
 589         #endregion
 590
 591         #region Where
 592
 593         private const string _whereOp =
 594 @"
 595 WHERE
 596 ";
 597
 598         internal static ObjectQueryState Where(ObjectQueryState query, string alias, string predicate, ObjectParameter[] parameters)
 599         {
 600             return BuildOrderByOrWhere(query, alias, predicate, parameters, _whereOp, null, false);
 601         }
 602         #endregion
 603     }
 604 }