Git information is in a separate document.
Databases that you should need for development:
- Demo-data database scripts are at: /src/DatabaseScripts/
- Access database is at: /docs/files/msaccess/Northwind.MDB
- SQLite database is at: /tests/SqlProvider.Tests/db/northwindEF.db
Even though our test run will run modifications to the test databases, don't check in these
*.db files with your commit, to avoid bad merge-cases.
We use Fake and Paket. You have to run
build.cmd on Windows (or
sh ./build.sh on Mac/Linux) before opening the solutions.
The main source solution is
The unit tests are located in another one,
SQLProvider.Tests.sln, and when you open the solution, it will lock the
bin\FSharp.Data.SqlProvider.dll, and after that you can't build the main solution.
- To debug design-time features you "Attach to process" the main solution debugger to another instance of Visual Studio running the tests solution.
- To debug runtime you attach it to e.g. fsi.exe and run the code in interactive.
- Debugging execution: Have all the test-files closed in your test-project when you open it with VS. Then you can run tests from the tests from Tests Explorer window (and even debug them if you open the files to that instance of VS from src\SqlProvider).
- Or you can open tests with some other editor than Visual Studio 2015
- Documentation is located at SQLProvider/docs/content/core and it's converted directly to
*.htmlhelp files by the build-script.
src/Code/ExpressionOptimizer.fsare coming from other repositoried restored by first build. Location is at packet.dependencies. Don't edit them manually.
There are database specific test files as scripts in the test solution, /tests/SqlProvider.Tests/scripts/, but also one generic /tests/SqlProvider.Tests/QueryTests.fs which is running all the SQLite tests in the build script.
You have a source code like:
What will first happen in the design-time, is that this will call
createTypes of SqlDesignTime.fs and create (as lazily as possible) the database schema types (the shape of the database). These methods are added to the
sql.datacontext and are stored to concurrent dictionaries. Visual Studio will do a lot of background processing so thread-safety is important here.
GetDataContext() will return a dynamic class called dataContext which will on design-time call class SqlDataContext in file SqlRuntime.DataContext.fs through interface
ISqlDataContext. SqlDataContext uses ProviderBuilder to create database specific providers, fairly well documented
ISqlProvider in file SqlRuntime.Common.fs.
The entity-items themselves are rows in the database data and they are modelled as dynamic sub-classes of
SqlEntity, base-class in file SqlRuntime.Common.fs which can be basically think of as wrapper for
Dictionary<string,obj> (a column name, and the value). SqlEntity is used for all-kind of result-data actually, so the data columns may not correspond to the actual data values. Mostly the results of the data are shaped as
SqlQueryable<'T> which is a SQLProvider's class for
1: 2: 3: 4: 5:
This query is translated to a LINQ-expression-tree through Microsoft.FSharp.Linq.QueryFSharpBuilder. That will call
IQueryable<'T>'s member Provider to execute two things for the LINQ-expression-tree: first
CreateQuery and later
CreateQuery will hit our
SqlQueryable<...>'s Provider (IQueryProvider) property. LINQ-expression-trees can be kind of recursive type structures, so we it will call CreateQuery for each linq-method. We get the expression-tree as parameter, and parse that with (multi-layer-) active patterns.
Our example the LINQ-expression tree is:
1: 2: 3: 4: 5: 6:
so it would hit this in SqlRuntime.Linq.fs:
because the LINQ-expression-tree has
ExpressionType.Call named "Where" with source of IWithSqlService (which is the SqlQueryable
Condition). If the conditions are having
SqlColumnGets, a pattern that says that it's
SqlEntity with method
GetColumn, we know that it has to be part of SQL-clause.
We collect all the known patterns to
IWithSqlServices field SqlExpression, being a type
SqlExp, our non-complete known recursive model-tree of SQL clauses.
Eventually there also comes the call
executeQueryScalar for SQL-queries that will return a single value like count), either by enumeration of our IQueryable or at the end of LINQ-expression-tree. That will call
QueryExpressionTransformer.convertExpression. What happens there (in
- We create a projection-lambda. This is described in detail below.
- We convert our
SqlExpto real SQL-clause with
GenerateQueryText-method. Each provider may have some differences in their SQL-syntax.
- We gather the results as
IEnumerable<SqlEntity>(or a single return value like count).
- We execute the projection-lambda to the results.
In our example the whole cust object was selected.
For security reasons we don't do
SELECT * but we actually list the columns that are there at compile time.
TupleIndex of IWithSqlService is a way to collect joined tables to match the sql-aliasses, here the
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15:
Now, if the select-clause would have been complex:
1: 2: 3: 4:
We don't know the function of DayOfYear for each different SQL-providers (Oracle/MSSQL/Odbc/...), but we still want tihs code to work. The LINQ-expression-tree for this query is:
1: 2: 3: 4: 5: 6:
What happens now, is that in SqlRuntime.QueryExpression.fs we parse the whole LINQ-expression-tree, and find the parts that we do know to belong to SQL:
emp.GetColumn("BirthDate"), and create a lambda-expression where this is replaced with a parameter:
Now when we get the empBirthDate from the SQL result, we can execute this lambda for the parameter, in .NET-side, not SQL, and then we get the correct result. This is done with
for e in results -> projector.DynamicInvoke(e) in SqlRuntime.Linq.fs.
- SQLProvider also runs ExpressionOptimizer functions to simplify the LINQ-expression-trees
If you do IN-query (LINQ-Contains) to IEnumerable, it's as normal IN-query, but if the source collection is SqlQueryable
, then the query is serialized as a nested query, where we have to check that the parameter names won't collide (i.e. param1 can be used only once).
This documentation was written on 2017-04-11.