More actions ▼
Up Query without using LINQ

Entity SQL (ESQL) queries

Last modified on June 11, 2014 08:53
Contents

DevForce supports Entity SQL (ESQL) queries with its PassthruEsqlQuery() class. As with all other query types, PassthruEsqlQuery implements the IEntityQuery interface.


API

There are several PassThruEsqlQuery constructor overloads:

C#
public PassthruEsqlQuery(Type returnType, String esql)

public PassthruEsqlQuery(Type returnType, Type queryableType, String esql)

public PassthruEsqlQuery(Type returnType, ParameterizedEsql parameterizedEsql)

public PassthruEsqlQuery(Type returnType, Type queryableType, ParameterizedEsql parameterizedEsql)
VB
Public Sub New(ByVal returnType As Type, ByVal esql As String)

Public Sub New(ByVal returnType As Type, ByVal parameterizedEsql As ParameterizedEsql)
End Sub

Public Sub New(ByVal returnType As Type, ByVal queryableType As Type, ByVal parameterizedEsql As ParameterizedEsql)
End Sub 

Simple queries

The simplest calls require only a return type and a valid ESQL expression as show below:

C#
var query0 = new PassthruEsqlQuery(typeof(Customer),
 "SELECT VALUE c FROM Customers AS c Where c.Country == 'Brazil'");
var result0 = query.With(_em1).Execute().Cast<Customer>();

var query1 = new PassthruEsqlQuery(typeof(SalesOrderHeader),
 "SELECT VALUE SalesOrderHeader FROM SalesOrderHeaders AS SalesOrderHeader Where SalesOrderHeader.Customer.CustomerID < 10");
var results1 = q1.With(_em1).Execute().Cast<SalesOrderHeader>();

var query2 = new PassthruEsqlQuery(typeof(SalesPerson),
  "SELECT VALUE sp FROM SalesPersons AS sp Where sp.Bonus > 2000");
var results2 = query2.With(_em1).Execute().Cast<SalesPerson>();
VB
Dim query0 = New PassthruEsqlQuery(GetType(Customer), _
 "SELECT VALUE c FROM Customers AS c Where c.Country == 'Brazil'")
Dim result0 = query.With(_em1).Execute().Cast(Of Customer)()

Dim query1 = New PassthruEsqlQuery(GetType(SalesOrderHeader), _
 "SELECT VALUE SalesOrderHeader FROM SalesOrderHeaders AS SalesOrderHeader Where SalesOrderHeader.Customer.CustomerID < 10")
Dim results1 = q1.With(_em1).Execute().Cast(Of SalesOrderHeader)()

Dim query2 = New PassthruEsqlQuery(GetType(SalesPerson), _
 "SELECT VALUE sp FROM SalesPersons AS sp Where sp.Bonus > 2000")
Dim results2 = query2.With(_em1).Execute().Cast(Of SalesPerson)()

Note that because the PassthruEsqlQuery class is not generic all of the results from executing such a query must be cast to the appropriate result type.

Queries with parameters

Queries with parameters can also be used as shown below:

C#
var param = new QueryParameter("country", "Brazil");
var paramEsql = new ParameterizedEsql(
 "SELECT VALUE c FROM Customers AS c Where c.Country > @country", param);
var query = new PassthruEsqlQuery(typeof(Customer), paramEsql);
var result1 = query.With(_em1).Execute().Cast<Customer>();

param.Value = "Germany";
var result2 = query.With(_em1).Execute().Cast<Customer>();
VB
Dim param = New QueryParameter("country", "Brazil")
Dim paramEsql = New ParameterizedEsql( _
 "SELECT VALUE c FROM Customers AS c Where c.Country > @country", param)
Dim query = New PassthruEsqlQuery(GetType(Customer), paramEsql)
Dim result1 = query.With(_em1).Execute().Cast(Of Customer)()

param.Value = "Germany"
Dim result2 = query.With(_em1).Execute().Cast(Of Customer)()

Note that the value of the parameter can be changed and the same query re-executed, returning different results.

More complex queries

So far all of the queries shown have involved queries where the source type or queryable type of the query is the same as the return type. If this is not the case then we need to pass in both types to the constructor. For example: 

C#
var query1 = new PassthruEsqlQuery(typeof(Int32), typeof(Customer),
 "SELECT VALUE Count(c.CustomerType) FROM Customers AS c Where c.CustomerID < 10");
var result1 = query1.With(_em1).Execute().Cast<Int32>();

var query2 = new PassthruEsqlQuery(typeof(Decimal), typeof(SalesPerson),
 "SELECT VALUE Sum(sp.Bonus) FROM SalesPersons AS sp Where sp.Bonus > 2000");
var result2 = query2.With(_em1).Execute().Cast<Decimal>();
VB
Dim query1 = New PassthruEsqlQuery(GetType(Int32), GetType(Customer), _
 "SELECT VALUE Count(c.CustomerType) FROM Customers AS c Where c.CustomerID < 10")
Dim result1 = query1.With(_em1).Execute().Cast(Of Int32)()

Dim query2 = New PassthruEsqlQuery(GetType(Decimal), GetType(SalesPerson), _
 "SELECT VALUE Sum(sp.Bonus) FROM SalesPersons AS sp Where sp.Bonus > 2000")
Dim result2 = query2.With(_em1).Execute().Cast(Of Decimal)()

Additional notes

When you use Entity SQL, you’re responsible for formulating a query string that constitutes a valid query. If you goof, you won’t know until you run it.

A PassthruEsqlQuery can only be executed with the DataSourceOnly query strategy.  

DevForce does not currently support ESQL queries with anonymous projections. 

Learn more

Entity SQL Quick Reference
Literals in Entity SQL
Canonical functions in Entity SQL

Tags: Query ESQL
Created by DevForce on December 13, 2010 13:04

Navigation

Tag Cloud

  1. Data Binding
  2. Data Sources
  3. Design Views
  4. Validation
  5. Anonymous
  6. Architecture
  7. ASP.NET
  8. Asynchronous
  9. Attach
  10. Azure
  11. CacheQueryOptions
  12. Caching
  13. Closure
  14. Cocktail
  15. code first
  16. Code Generation
  17. Code Sample
  18. Concurrency
  19. Configure
  20. Cookbook
  21. Create
  22. Debug
  23. Delete
  24. Deploy
  25. DevForce 2010
  26. DevForce Classic
  27. DevForce-Caliburn framework
  28. Disconnect
  29. Discovery
  30. Display
  31. Dynamic query
  32. EDM
  33. EDMX
  34. Entity cache
  35. Entity Framework
  36. EntityAspect
  37. EntityCache
  38. EntityKey
  39. EntityKeyQuery
  40. EntityManager
  41. EntityProperty
  42. EntityQuery
  43. EntityQueryOperation
  44. EntitySaveOperation
  45. EntityServer
  46. EntityServerSaveInterceptor
  47. EntityState
  48. EntityVersion
  49. ESQL
  50. Exception
  51. Extensibility
  52. Extension
  53. Faking
  54. Fetching
  55. FetchStrategy
  56. Getting started
  57. IIS
  58. Include
  59. Interception
  60. iOS
  61. IVerifierProvider
  62. JavaScript
  63. LINQ
  64. Localization
  65. Log
  66. MEF
  67. MergeStrategy
  68. Metadata
  69. MetadataType
  70. Model
  71. Multi-threading
  72. MVVM
  73. Navigation
  74. NET Native
  75. Object Mapping
  76. OData
  77. Offline
  78. Paging
  79. Performance
  80. Persistence
  81. POCO
  82. Predicate
  83. PredicateBuilder
  84. PredicateDescription
  85. Prism
  86. Property Interceptors
  87. PropertySortSelector
  88. Push
  89. Query
  90. Query cache
  91. Query Inversion
  92. QueryStrategy
  93. Refetch
  94. Refresh
  95. Release Notes
  96. Sample Application
  97. Save
  98. SaveOptions
  99. SaveResult
  100. SaveStatus
  101. Saving
  102. Security
  103. Serialization
  104. Silverlight
  105. Stored Procedure
  106. Support
  107. Telerik
  108. Template
  109. Test
  110. ToQuery
  111. Tour
  112. Transactions
  113. Troubleshooting
  114. UAP
  115. UWP
  116. Validation
  117. Verifier
  118. VerifierEngine
  119. Video
  120. WCF
  121. Windows 10
  122. Windows Phone
  123. Windows Store
  124. WinForms
  125. WinRT
  126. WIP
  127. WPF

Spaces

 | 2009Documentation | Documentation

This wiki is licensed under a Creative Commons 2.0 license. XWiki Enterprise 3.2 - Documentation. Copyright © 2020 IdeaBlade