EntitySpaces.ORM.SqlServer 2019.7.22

A Very Simple Example

The sample below demonstrates a self join on the Employees table which is looking for all employees with an 'a' in their last name who have people reporting to them. Kind of silly but it shows off the syntax.

Query and Load

Notice we're creating a "fullName" column on the fly and will be in our JSON as we expect as shown below

EmployeesQuery e = new EmployeesQuery("e");
e.Select(e.EmployeeID, e.LastName, e.FirstName, (e.LastName + ", " + e.FirstName).As("fullName"))
    .OrderBy(e.LastName.Descending);

EmployeesCollection coll = new EmployeesCollection();
if (coll.Load(e))
{
    // We loaded at least one record
}

Notice that the SQL is extremely lean.

NOTE: InnerJoin, RightJoin, LeftJoin, CrossJoin, and FullJoin are all supported, basically the entire SQL API is supported.

SQL Generated and executed from the Query Above

SELECT e.[EmployeeID], e.[LastName], e.[FirstName], (e.[LastName] + ', ' + e.[FirstName]) AS 'fullName'  
FROM [Employees] e 
ORDER BY e.[LastName] DESC

Compare that SQL to the SQL generated by a Entity Framework query which does the same thing and you'll be shocked.

JSON resulting from Serialization.

Notice the "fullName" is present, no need to new an anonymous object extra columns just magically appear

[
  {
    "EmployeeID": 6,
    "LastName": "Suyama",
    "FirstName": "Michael",
    "fullName": "Suyama, Michael"
  },
  {
    "EmployeeID": 33,
    "LastName": "Smith",
    "FirstName": "Joe",
    "fullName": "Smith, Joe"
  },
  {
    "EmployeeID": 31,
    "LastName": "Smith",
    "FirstName": "Frank",
    "fullName": "Smith, Frank"
  }
]