3

I am trying to create an Entity SQL that is a union of two sub-queries. 

(SELECT VALUE DISTINCT ROW(e.ColumnA, e.ColumnB, 1 AS Rank) FROM Context.Entity AS E WHERE ...)
UNION ALL 
(SELECT VALUE DISTINCT ROW(e.ColumnA, e.ColumnB, 2 AS Rank) FROM Context.Entity AS E WHERE ...)
ORDER BY *??*  LIMIT 50

I have tried:

ORDER BY Rank

and 

ORDER BY e.Rank

but I keep getting:

System.Data.EntitySqlException: The query syntax is not valid. Near keyword 'ORDER'

Edit:

This is Entity Framework. In C#, the query is executed using:

var esql = "...";
ObjectParameter parameter0 = new ObjectParameter("p0", value1);
ObjectParameter parameter1 = new ObjectParameter("p1", value2);
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql, parameter0, parameter1);
var queryResults = query.Execute(MergeOption.NoTracking);

There is only a small portion of my application where I have to resort to using Entity SQL. Generally, the main use case is when I need to do: "WHERE Column LIKE 'A % value % with % multiple % wildcards'".

I do not think it is a problem with the Rank column. I do think it is how I am trying to apply an order by to two different esql statements joined by union all. Could someone suggest:

  1. How to apply a ORDER BY to this kind of UNION/UNION ALL statment
  2. How to order by the non-entity column expression.

Thanks.

apaderno
  • 28,547
  • 16
  • 75
  • 90
Phil Bolduc
  • 1,586
  • 1
  • 11
  • 19
  • You do not say which SQL server you are using, can we presume MS SQL 2012 ? This matter relates entirely to what can and can not be done with a particular SQL server implementation. – Darryl Miles Sep 20 '12 at 06:45
  • i dont think this is an entity framework question, tag removed – undefined Sep 20 '12 at 06:51
  • Luke: This is an Entity Framework question. I am using Entity SQL, see http://msdn.microsoft.com/en-us/library/bb387145.aspx. Entity SQL is a SQL-like language that enables you to query conceptual models in the Entity Framework. Conceptual models represent data as entities and relationships, and Entity SQL allows you to query those entities and relationships in a format that is familiar to those who have used SQL. Not all queries in Entity Framework have to use LINQ. There are some queries that not composable using LINQ (expression or method syntax). – Phil Bolduc Sep 20 '12 at 15:33
  • Darrl, I am using SQL Server 2008 R2. However, that should not matter as this is Entity SQL syntax and works on the conceptual models. – Phil Bolduc Sep 20 '12 at 15:40
  • Does the `order by` work if you don't do the union? i.e. Does it work if you only use one of the select statements? – Bob. Sep 20 '12 at 15:48
  • Bob: I couldn't get the ORDER BY to work when I used the ROW(). It complained that it could not bind the value. i.e., SELECT VALUE DISTINCT ROW(e.Column) FROM Context.Entity AS e ORDER BY e.Column. I am super busy today, and going to have to just use what can work for now. If I get a chance on the weekend, I may try to get this working. – Phil Bolduc Sep 20 '12 at 16:52

0 Answers0