How to do unit testing on my code when using SQLProvider?

[Thorium]

What are the best practices to do unit testing with SQLProvider?

Let's say that I have a method:

let myLogic email = 
    let usr = 
        query {
            for u in dbContext.MyProject.Useraccount do
            where (u.Email = email && u.Disabled.IsNone)
            select (u)
        } |> Seq.head
    usr.Accessed <- DateTime.UtcNow
    dbContext.SubmitUpdates()
    usr

How would you unit-test this?
Should we have a way to mock data to produce just some noise out of LINQ-expressions and dbContext?
Like a separate nuget-package as SQLProvider.Unittest.Datagenerator or something?

E.g. FsCheck is producing some random noise over types, could you use FsCheck to mock dbContext?

Or as conversation, how would you like to test that if you could decide?

One example: You can have dbContext creation in separate fsproj and in unit-test time you use instead a mock like:

open System
open System.Linq

type MockUser = {
    Email: string;
    Disabled: bool Option;
    mutable Accessed: DateTime
}

type MockDatabase = {
    Useraccount: System.Linq.IQueryable<MockUser>
}
type MockContext = {
    MyProject: MockDatabase
} with
    member __.SubmitUpdates() =
        Console.WriteLine("Saved")

let dbContext = 
    { MyProject =
        { Useraccount = 
            [{ 
                Email = "adsf@asdf.com"; 
                Disabled = Some false; 
                Accessed = DateTime.MinValue 
            }].AsQueryable()
        }
    }
// ...and now the previous LINQ is perfectly valid without database.

[<Fact>]
let ``my unit test`` =
    let res = myLogic "adsf@asdf.com"
    Assert.Equal("adsf@asdf.com", res.Email)
    Assert.True(res.Accessed > DateTime.MinValue)

...but we could also generate these mocks automatically via calling FSharp-compiler on runtime to analyse the source code, like @pezipink's metaprovider, right? A unit-test-typeprovider?

[pezipink]

probably not the answer you are looking for - I personally would never unit test this kind of stuff. Since you are entirely reliant on the response of the actual ado.net provider, you would only be testing your happy path using some mock - I would leave this for integration tests.

[Thorium]

I think this is increasing problem when SQLProvider is getting better to handle more complex queries when your business logics starts to locate more and more in the SQL query, which is good and efficient, but then when you have a lot of LINQ-queries (e.g. where-condition can have more than one happy path), it's not easy to modify those being sure that all the other business scenarios still work.

[Thorium]

Also I think this would be an advantage over e.g. FSharp.Data.SqlClient and many others, in an enterprise environment, you could actually unit-test your database query logics.

[jr-jesse123]

@Thorium , hey thorium, what did you find about it? I´m in the same spot now... no confidence in using it without good unit tests.

[JordanMarr]

If you need to test, you should just wrap your SQLProvider queries in functions and then you can mock those functions.

Edit: I was assuming you wanted to isolate your business logic from SQLProvider and unit test it.
If you are talking about testing SQLProvider itself, then I would guess your options are limited to one of the following:
A) Creating integration tests against a test database instance
B) Adding to the existing SQLProvider automated test suite
C) Maybe you could do some gymnastics to write tests against the generated SQL by wiring up to the SqlQueryEvent:

#if DEBUG
    // Print generated SQL to console
    FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent |> Event.add (printfn "Executing SQL: %O")
#endif

[jr-jesse123]

@JordanMarr yes, that´s what i´m aiming for... thank you for the tips!!

[Thorium]

I went for having a test-database. That wasn't what I wanted back in 2017 when I asked this, but it was enough. I can just play with any code by sending it to FSI and executing against a test-database, it gives me lot more focus to the actual core logics than unit-testing "API". I came from mainly-C#-enterprise-background and valued unit-tests too much. Now the few I have are more burden to maintain than a benefit.

[Thorium]

Revisiting this old topic, with one more option:
There are now 2 new methods to help you with your LINQ-logic unit-tests:

• FSharp.Data.Sql.Common.OfflineTools.CreateMockEntities<'T> - With this you can mock a single table.
• FSharp.Data.Sql.Common.OfflineTools.CreateMockSqlDataContext<'T> - With this you can mock a context with multiple tables

Typical scenario is that you have a LINQ-clause (will be translated to SQL) and someone added a new or-condition in the end of your where, and you'd like to verify still getting expected results, with testing some sample data, for example:

let myQuery ctx =
   query {
      for x in ctx.myTable do
      where (x.Col1 > 1 && x.Col2 < 3 || x.Col4 is null)
      select (x)
   }
// Which way should (s)he inserted the parenthesis to ensure wanted behaviour? 
 ((x.Col1 > 1 && x.Col2 < 3) || x.Col3 is null) // or 
  (x.Col1 > 1 && (x.Col2 < 3 || x.Col3 is null)) // ?

Test with anonymous records having only columns you need, like this:

[ {| Col1 = 4; Col2 = 2; Col3 = null |};
  {| Col1 = 4; Col2 = 2; Col4 = null |}] 
|> CreateMockEntities "myTable" |> myQuery |> ``eval with some asserts``

You can of course add custom columns to items, like a Description = "this one should be filtered out" too.
For proper usage example, see this:

SQLProvider/tests/SqlProvider.Tests/QueryTests.fs

Line 2538 in c22880b

let test_querylogic (customers:IQueryable<sqlValueOption.dataContext.``main.CustomersEntity``>) =

...and this:

SQLProvider/tests/SqlProvider.Tests/QueryTests.fs

Line 2564 in c22880b

let test_querylogic_cont (ctx:sqlValueOption.dataContext) =

It will emulate your real objects (hopefully with your anonymous records having corresponding names with your DB-columns, including case-sensitivity), but the mock-context will not use connect the DB and you can't save modifications. So if you are running off-line solution like SSDT or ContextSchemaPath, you should be able to run also these unit-tests with your CI.