Difference Between Entity SQL and Transact-SQL

Download Article (PDF)

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. Transact-SQL is extension to SQL created for communicating with SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

Both sounds similar but there are many differences between these two. Following table represents those differences.

Entity SQL	Transact-SQL
Is a language similar to SQL used to work with an Entity Data Model (EDM).	Is an ANSI SQL language used for stored procedures and functions.
Is used to work with conceptual entities by supporting features like inheritance and relationships.	Is used to write stored procedures in relational databases.
Supports collections and considers them as first class entities.	Does not support collections but does support subqueries and expressions.
Interprets all subqueries to be multiset subqueries, irrespective of the context in which they are used.	Interprets subqueries as per the context in which they are used.
Allows expressions defined in a from clause to reference earlier expressions defined to the left in the same clause.	Does not allow expressions to reference earlier expressions defined in the same scope, such as in a select clause.
Table columns can only be referenced by qualifying them with a table alias, except in from clauses where table aliases are optional.	Table columns are referenced directly.
Uses the “.” notation to navigate through the properties of an object.	Uses the “.” notation to reference columns.
Does not support the * construct.	Supports the * construct.
Supports SQL-style aggregates and collection-based aggregates.	Supports only SQL-based aggregates.
Allows nested ORDER By expressions anywhere in the queries.	Allows ORDER By clauses only in the top most SELCT, FROM, and WHERE clauses of the queries.
Identifiers are compared according to the case and accent.	Identifiers are compared according to the current database collation.
Supports a subset of Transact-SQL’s built in functions and operators.	Supports additional functionalities that are not supported in Entity SQL including: Analytic Functions Builtin Functions, Operators DDL DML -insert, update, delete.