8
$\begingroup$

I have an MicrosoftSQL Server database connected to entity framework as follows:

schema=RelationalDatabase[reference]
store=EntityStore[schema]
EntityRegister[store]

Now I have a table dataset in which I have a column Issue Date and I want to create another column which contains quarters from this date as string. So I try the following:

derivedClass=ExtendedEntityClass["dataset",
    "Issue Quarter"->EntityFunction[c,
        (* attempt 1 *)
        (* attempt 2 *)
        (* attempt 3 *)         
    ]
];

where attempts for EntityFunction are as follows:

(* attempt 1 : use DateObject functionality *)

DateObject[c[EntityProperty["dataset","Issue Date"]],"Quarter"]

(* attempt 2 : extract date components and then join *)

StringJoin@Riffle[ToString/@DateValue[c[EntityProperty["dataset","Issue Date"]],{"Year","Quarter"}],"-Q"]

(* attempt 3 : convert to string and then transform it *)

StringReplace[ToString[c[EntityProperty["dataset","Issue Date"]]],RegularExpression["(\\d{4})-(\\d{2})-(\\d{2})"]:>"$1-Q"<>ToString[Ceiling[ToExpression@"$2"/3]]]

Then extract 2 rows to see the result:

derivedClass=SampledEntityClass[derivedClass,2]

EntityValue[
    derivedClass,
    "EntityPropertyAssociation"
]

All of them failed! Can someone guide me about how to do this in Entity Framework.

$\endgroup$
9
$\begingroup$

The issue here is that there are restrictions upon the form of EntityFunction definitions when an entity store is backed by a relational data source. In order to support efficient querying via the entity framework, such functions must be compilable to SQL. The Details section of the EntityFunction documentation lists the forms that the databases framework supports. Unfortunately, this subset of expressions does not include any direct capability for extracting the quarter from a date object.

To work around this limitation we must confine ourselves to the compilable subset of operators. For example, we could determine the quarter number by inspecting the month name from the string representation of each date by means of the compilable operators Which, MemberQ, StringTake and ToString:

$derived =
  ExtendedEntityClass["dataset"
  , "issue_quarter" -> EntityFunction[e
    , Which[
        MemberQ[{"Jan", "Feb", "Mar"}, StringTake[ToString[e["issue_date"]], 3]], 1
      , MemberQ[{"Apr", "May", "Jun"}, StringTake[ToString[e["issue_date"]], 3]], 2
      , MemberQ[{"Jul", "Aug", "Sep"}, StringTake[ToString[e["issue_date"]], 3]], 3
      , MemberQ[{"Oct", "Nov", "Dec"}, StringTake[ToString[e["issue_date"]], 3]], 4
      , True, -999
      ]
    ]
  ];

EntityValue[$derived, "EntityPropertyAssociation"]

association screenshot

This technique hinges on the precise date string format used by the SQL server instance:

EntityValue["dataset", EntityFunction[e, ToString[e["issue_date"]]]]

result screenshot

If the regional settings of your SQL Server give a different format then the function will need to be adjusted accordingly.

Why Are These Restrictions In Place?

In the earliest implementations of the entity framework, there was little ability to filter results. The onus was upon the user to post-process the data after downloading it. For small datasets this is viable, but for large datasets the download time can dominate the query.

As the framework evolved, additional querying features were added. Sometimes a filter condition could be pushed to the server so response times were good. But at other times, the condition could only be evaluated locally so again a full download was necessary. The net result was that it was hard for a user to predict whether adding a filter condition was going to cause a massive query slowdown.

Relational stores are typically used for large datasets. So large, in fact, that downloading the whole dataset for post-processing can be completely impractical. It is therefore critically important for filter and join conditions to be passed to the server so that the query can be resolved server-sde -- the very thing for which relational databases were invented.

Alas, commercial relational servers cannot evaluate WL expressions. Therefore, expressions sent to the server must be translated into a dialect that the servers understand, namely SQL. It is true that SQL is Turing-complete, but query writers around the world sometimes look longingly at the expressiveness of a Turing tape ;)

There are companies whose sole purpose in life is to find clever ways to stitch query and business logic together into an efficient mixture of SQL and conventional code. WRI has chosen a pragmatic approach of implementing a generous set of SQL-compilable query operators. A set that can support multiple SQL back-ends and that will likely grow over time. The idea is that the user can use these operators to reduce the size of the working dataset to be small enough to post-process locally. More elaborate computation can then be done with the full power of WL applied to the in-memory dataset.

By and large, one should probably avoid writing abominations like the one I exhibit in in this post -- unless it is critical to query performance to do so. Perform computations upon in-memory data as far as is feasible so as to enjoy the full expressiveness of WL.


Self-Contained Example

For those who wish to play along at home, what follows is a self-contained example assuming that a functioning SQL Server database named test is available.

If you have Docker on your machine, you can use it to start a SQL Server instance and create a database:

Import[
  "!docker run -e \"ACCEPT_EULA=Y\" -e \"SA_PASSWORD=secret!123\" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest 2>&1"
, "Text"
]

Import[
  "!docker exec testsql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P \"secret!123\" -Q \"CREATE DATABASE test\" 2>&1"
, "Text"
]

Create a reference for this database:

$ref = 
  <| "Backend" -> "MicrosoftSQL"
   , "Host" -> "localhost"
   , "Port" -> 1433
   , "Name" -> "test"
   , "Username" -> "sa"
   , "Password" -> "secret!123"
  |> // DatabaseReference;

Load the schema:

$db = $ref // RelationalDatabase;

Create a table and load it with some sample data:

"
CREATE TABLE dataset
( id INT IDENTITY PRIMARY KEY
, issue_date DATETIME
, value FLOAT
)
" // Databases`Database`DBResultSet[#, $db]&

"
INSERT INTO dataset(issue_date, value)
          SELECT '2000-01-01', 111
UNION ALL SELECT '2001-01-01', 222
UNION ALL SELECT '2001-04-01', 333
UNION ALL SELECT '2001-07-01', 444
UNION ALL SELECT '2001-10-01', 555
" // Databases`Database`DBResultSet[#, $db]&

Reload the schema and display the data:

$db = $ref // RelationalDatabase;

Databases`Database`DBResultSet["select * from dataset", $db]["Dataset"]

table screenshot

Create and register the entity store:

$store = EntityStore[$db];
EntityRegister[$store]

Create our derived entity class:

$derived =
  ExtendedEntityClass["dataset"
  , "issue_quarter" -> EntityFunction[e
    , Which[
        MemberQ[{"Jan", "Feb", "Mar"}, StringTake[ToString[e["issue_date"]], 3]], 1
      , MemberQ[{"Apr", "May", "Jun"}, StringTake[ToString[e["issue_date"]], 3]], 2
      , MemberQ[{"Jul", "Aug", "Sep"}, StringTake[ToString[e["issue_date"]], 3]], 3
      , MemberQ[{"Oct", "Nov", "Dec"}, StringTake[ToString[e["issue_date"]], 3]], 4
      , True, -999
      ]
    ]
  ];

And now, after much ado, we can get our desired results:

EntityValue[$derived, "EntityPropertyAssociation"]

association screenshot

$\endgroup$
  • $\begingroup$ What an amazing answer! I would vote for it 100 times if I could. Thanks! As a side note, one very useful feature that we were hoping to get into 12.1 is embedded SQL, which would allow users to embed SQL strings into symbolic entity queries. That would've served as a fall-back solution for many if not most shortcomings of the current compilation procedure. Unfortunately, it will have to wait until 12.2 for a number of reasons. $\endgroup$ – Leonid Shifrin Dec 27 '19 at 22:41
  • 3
    $\begingroup$ As another side note for the interested, one can set Databases`Database`$DBQueryLogger = Echo to see the SQL generated by the framework / compiler. $\endgroup$ – Leonid Shifrin Dec 27 '19 at 22:44
  • 1
    $\begingroup$ @user13892 That's a good suggestion, thanks - I will add this to our list of desired features. But actually, this particular thing is not hard to implement on the user side as a query preprocessor. If you are interested, I may add a self-answered question for that, some time soon - but it does not involve any specific knowledge of the relational database / entities framework, just the standard WL evaluation control / held expressions manipulations. $\endgroup$ – Leonid Shifrin Dec 27 '19 at 23:10
  • 3
    $\begingroup$ Perhaps my last comment here is about not setting an equal sign between entity framework and the new relational databases framework. The latter uses the former as a front-end, compiling entity framework queries to a certain intermediate representation, which is then compiled to (backend-specific) SQL. We have intentionally decoupled the design so that we could in principle support other front-ends (such as Dataset, for instance - for tabular data, although that would require certain extensions for the current Dataset query language), or even use intermediate representations directly. $\endgroup$ – Leonid Shifrin Dec 27 '19 at 23:22
  • 2
    $\begingroup$ @user13892 I agree, the simple CASE in SQL is similar to Switch in WL, so we could implement this form too. One possible counter-argument could be that WL Switch accepts patterns, so claiming that we support Switch in EntityFunction might be somewhat confusing. But I would agree still that supporting Switch (under the condition that it is not used with patterns) in EntityFunction can be useful. $\endgroup$ – Leonid Shifrin Dec 28 '19 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.