Diego Vega's blog

Entity Framework news and sporadic epiphanies

Entity SQL Non-Quirkiness

Entity SQL Non-Quirkiness

  • Comments 8

Zlatko has been posting about one LINQ to Entities new feature included in the upcoming Beta 3, so I decided to take revenge and make a 100% Entity SQL post. Here is something I ran against the other day:

Let's assume we need to retrieve the Order with the maximum OrderID, which is a really awful way to get the ID of the order you just inserted! :)

In your everyday store-specific SQL, you can use a MAX() aggregate function in a subquery as a WHERE predicate. In Transact SQL, it should look like this:

SELECT *
FROM   Products AS p
WHERE
  p.ProductID =(SELECT MAX(p2.ProductID
                    
FROM   Products as p2);

So far, so good. If you have been playing a little with Entity SQL, you will probably guess how the equivalent Entity SQL would look like:

SELECT VALUE p
FROM   Products AS p
WHERE  p.ProductID =(SELECT MAX(p2.ProductID
                    
FROM   Products as p2);

But if you run this query, what you get is this interesting exception:

System.Data.QueryException: Argument types 'Edm.Int32' and 'Transient.collection[Transient.rowtype[(_##groupAggMax2,Edm.Int32(Nullable=True,DefaultValue=))](Nullable=True,DefaultValue=)]' are incompatible for this operation, near WHERE predicate, line 1, column 60.

The subquery is actually returning a Transient.collection of a Transient.rowtype... Those are internal things, so for illustration purposes, let's turn to the LINQ perspective of life:

var query = from p in context.Products
           
select new { p.ProductID };

int
productID = query;

 

(Argh, this post is no longer 100% Entity SQL!)

No surprisingly, what you get is a compile-time exception:

Cannot implicitly convert type 'System.Linq.IQueryable<AnonymousType#1>' to 'int'.

Both exceptions are homologous, and for a text-based query language, Entity SQL happens to be very type-safe at its core. Standard SQL makes the basic assumption that it is ok to implicitly convert single-item collections of single-column projections to discrete scalars. We don't.

The basic theme in Version 1.0 of the Entity Framework is to build a solid foundation for the future. As a consequence, one thing we avoid doing is "magic" type conversions except when they make perfect sense (think union of projection queries with exactly the same shape). The motive: magic conversions tend to mine the stability and composability of the language.

That said, this buys us freedom to hand-pick certain implicit behavior in the future, if we find enough feedback and proof that it makes sense.

That's enough on the rationale. Now, how do I make it work? There are two approaches.

First:

SELECT VALUE p
FROM
   Products AS p
WHERE
  p.ProductID = MAX(SELECT VALUE p2.ProductID
                        
FROM   Products AS p2);

This one works because:

a) The SELECT VALUE returns the scalar itself, instead of a projection (rowtype) of the scalar.

b) MAX() operates on the collection of scalars returned by the subquery, returning a single maximum value that will be directly comparable (same type) as ProductID.

Second:

SELECT VALUE p
FROM
   Products AS p
WHERE
  p.ProductID = ANYELEMENT(
          
SELECT VALUE MAX(p2.ProductID)  
           FROM   Products AS p2);

This works because:

a) The subquery will return a single-item collection of a scalar value.

b) ANYELEMENT will retrieve a single element (in this case, the only one) contained in the collection. That element will be directly comparable with ProductID.

In case you are wondering now how efficient this is, don't worry. Entity SQL is still a functional language. So, while understanding the type reasoning is interesting and useful, these queries still express "what you want to get" rather than "how you want the job done".

As a matter of fact, with our current SqlClient implementation, these queries will be translated to some simple, yet unexpected Transact-SQL. But I'll leave that to you as an exercise...

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
  • Bienvenida a bordo

    Pingback from http://oakleafblog.blogspot.com/2007/11/linq-and-entity-framework-posts-for_26.html

    --rj

  • Hi John, I started answering here but end up writting a new post to answer your question. Thanks!

    http://blogs.msdn.com/diego/archive/2007/12/20/some-differences-between-esql-and-linq-to-entities-capabilities.aspx

  • Nice!

    I was going crazy with this issue.

    thx

  • Hello,

    I know this is an old post, but I need some explanation, why I get an exception in my scenario. I have 3 tables, CMDB, Incidents and Organizations. There is a foreign key that points to Organizations from CMDB (via column Organization_ID) and another that points to Organizations from Incidents (via column Customer_ID).

    I try to get all the rows (but not all the columns, I use PROJECTION) from CMDB with Organization_ID matching the given Incident's (ID=4) Customer_ID.

    Query:

    SELECT c.ID,c.Description,c.ModelNumber,c.VersionNumber,c.InventoryRefNumber,c.SerialNumber FROM CMDB AS c  

    WHERE c.Organization.ID = (select value d.Customer.ID from Incidents as d where d.ID = 4)

    Exception:

    The argument types 'Edm.Int16' and 'Transient.collection[Edm.Int16(Nullable=True,DefaultValue=)]' are incompatible for this operation., near WHERE predicate, line 1, column 150.

    So something is wrong with the expression's right side, that is, the sub-select. I don't know why it isn't working (like in your example, where SELECT VALUE returned the scalar itself, what is exactly the same I need). In my final exasperation, I applied the ANYELEMENT operator in my query, that solved the problem, but I don't know why:

    ... c.Organization.ID = ANYELEMENT(select value d.Customer.ID from Incidents as d where d.ID = 4)

    Could you please explain why the exception occured and why the ANYELEMENT operator provided a solution?

    Any help is appreciated!

    Regards,

    Csiga

  • Hi Csiga,

    At first glance, the query that you show here looks a lot like the one in the explanation in the blog post, so I am going to try to explain it again. Hopefully I will get it better this time, as I have more experience now :)

    In the WHERE clause of your query, you are using a singleton subquery, which is a construct supported by ANSI SQL and several dialects of SQL (including SQL Server’s own T-SQL), that returns a scalar value.

    But for Entity SQL, all SELECT queries return collections. Entity SQL will not reason about the fact that in this particular subquery the result is going to be a collection with a single element, and therefore it will not try to convert the result type from a collection to a scalar value.

    Therefore there is a type mismatch between the two operands in the equal comparsion: you have a scalar on the left side, and a collection of scalars on the right side.

    In other words, Entity SQL does not support singleton subqueries.

    ANYELEMENT will do the explicit conversion from a collection to a single scalar value, which is exactly what you need to do in this case.

    Thanks,

    Diego

  • Hi again Csiga,

    Yet another way you can write the same query:

    SELECT c.ID, c.Description, c.ModelNumber, c.VersionNumber, c.InventoryRefNumber, c.SerialNumber

    FROM CMDB AS c  

    WHERE c.Organization.ID IN

    (SELECT VALUE d.Customer.ID FROM Incidents as d WHERE d.ID = 4)

    Hope this helps,

    Diego

  • Thanks for your time (I really appreciate it), everything is clear now!

    Best regards,

    Csiga

  • Can you please help to convert either of this querries in  e sql.I have to give it in entity data source

    select NPI,FirstName,LastName,ProviderType,PrelimChecksLockedBy,City, NPI,FirstName,LastName,ProviderType,PrelimChecksLockedBy,City, from VPaymentYear_1_Details as G1

    join(select NPI_YR,MAX(PaymentYear)as MostRecent from TApp_PaymentYear_1_Detail group by NPI_YR) as G2

    on G2.NPI_YR=G1.NPI and G2.MostRecent=g1.PaymentYear  where g1.KY_Status_Code=1 and g1.PaymentStatus=1 order by g1.NPI or

    select VPaymentYear_1_Details.NPI,VPaymentYear_1_Details.FirstName,VPaymentYear_1_Details.LastName,VPaymentYear_1_Details.ProviderType,VPaymentYear_1_Details.PrelimChecksLockedBy,VPaymentYear_1_Details.City, VPaymentYear_1_Details.PaymentYear from VPaymentYear_1_Details

    where VPaymentYear_1_Details.KY_Status_Code=1 and PaymentYear.PaymentStatus=1 and PaymentYear=(select MAX(TApp_PaymentYear_1_Detail.PaymentYear) from TApp_PaymentYear_1_Detail  where VPaymentYear_1_Details.NPI=TApp_PaymentYear_1_Detail.NPI_YR )order by VPaymentYear_1_Details.NPI

Page 1 of 1 (8 items)