More actions ▼
Up Query without using LINQ

Entity SQL (ESQL) queries

Last modified on August 15, 2012 17:22
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 costructor 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

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. Coroutine
  22. Create
  23. Debug
  24. Delete
  25. Deploy
  26. DevForce 2012
  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. IIS
  57. Include
  58. Interception
  59. iOS
  60. IVerifierProvider
  61. JavaScript
  62. LINQ
  63. Localization
  64. Log
  65. MEF
  66. MergeStrategy
  67. Metadata
  68. MetadataType
  69. Model
  70. Multi-threading
  71. MVVM
  72. Navigation
  73. Object Mapping
  74. ObjectDataSource
  75. OData
  76. Offline
  77. Paging
  78. Performance
  79. Persistence
  80. POCO
  81. Predicate
  82. PredicateBuilder
  83. PredicateDescription
  84. Prism
  85. Property Interceptors
  86. PropertySortSelector
  87. Push
  88. Query
  89. Query cache
  90. Query Inversion
  91. QueryStrategy
  92. Refetch
  93. Refresh
  94. Release Notes
  95. Sample Application
  96. Save
  97. SaveOptions
  98. SaveResult
  99. SaveStatus
  100. Saving
  101. Security
  102. Serialization
  103. Silverlight
  104. Stored Procedure
  105. Telerik
  106. Template
  107. Test
  108. ToQuery
  109. Tour
  110. Transactions
  111. Troubleshooting
  112. Validation
  113. Verifier
  114. VerifierEngine
  115. Video
  116. Visual Studio
  117. WCF
  118. WinForms
  119. WIP
  120. WPF

Spaces

 | 2009Documentation | Documentation

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