gistfile1.fs

module SqlServerQueryTranslatorTest

open NUnit.Framework
open FSharp.QueryProvider

open Models

let provider = EmptyQueryProvider.EmptyQueryProvider()

let queryable<'T> = Queryable.Query(provider, None)
//use for test data:
//http://fsprojects.github.io/FSharp.Linq.ComposableQuery/QueryExamples.html
//https://msdn.microsoft.com/en-us/library/vstudio/hh225374.aspx
module QueryGenTest = 

    let AreEqualExpression (expression : System.Linq.Expressions.Expression option) (expectedSql : string) : unit =
        if expression.IsNone then
            failwith "expression was None"

        printfn "%s" (expression.ToString())
        let sqlQuery = QueryTranslator.SqlServer.translate expression.Value
        let actualSql = sqlQuery.Text

        Assert.AreEqual(expectedSql, actualSql)
        printfn "%s" (actualSql)

    let AreEqualQuery (query : 't System.Linq.IQueryable) (expectedSql : string) : unit =
        AreEqualExpression (Some query.Expression) expectedSql

    [<Test>]
    let ``simple select``() =
        let q = query {
            for p in queryable<Person> do
            select p
        }
        
        AreEqualQuery q "SELECT * FROM Person"

    [<Test>]
    let ``simple where``() =
        let q = query {
            for p in queryable<Person> do
            where(p.PersonName = "john")
            select p
        }
        
        AreEqualQuery q "SELECT * FROM Person WHERE (PersonName = 'john')"

    [<Test>]
    let ``where with single or``() =
        let q = query {
            for p in queryable<Person> do
            where(p.PersonName = "john" || p.PersonName = "doe")
            select p
        }
        
        AreEqualQuery q "SELECT * FROM Person WHERE (PersonName = 'john' OR PersonName = 'doe')"

    [<Test>]
    let ``where with two or``() =
        let q = query {
            for p in queryable<Person> do
            where(p.PersonName = "john" || p.PersonName = "doe" || p.PersonName = "james")
            select p
        }
        
        AreEqualQuery q "SELECT * FROM Person WHERE (PersonName = 'john' OR PersonName = 'doe' OR PersonName = 'james')"

    [<Test>]
    let ``where string contains``() =
        let q = query {
            for p in queryable<Person> do
            where(p.PersonName.Contains("john"))
            select p
        }
        
        AreEqualQuery q "SELECT * FROM Person WHERE (PersonName LIKE '%john%')"

    [<Test>]
    let ``partial select``() =
        let q = query {
            for p in queryable<Person> do
            select p.PersonName
        }
        
        AreEqualQuery q "SELECT PersonName FROM Person"

    [<Test>]
    let ``partial select with where``() =
        let q = query {
            for p in queryable<Person> do
            where(p.PersonName = "john")
            select p.PersonName
        }
        
        AreEqualQuery q "SELECT PersonName FROM Person WHERE (PersonName = 'john')"

    [<Test>]
    [<Ignore>]
    let ``partial tuple select``() =
        //This is broken in the fsharp compiler.
        //https://github.com/Microsoft/visualfsharp/issues/47

        let q = query {
            for p in queryable<Person> do
            select (p.PersonName, p.PersonId)
        }
        
        AreEqualQuery q "SELECT T0.PersonName, T0.PersonID FROM Person AS T0"

    [<Test>]
    let ``count``() =
        let q = query {
            for p in queryable<Person> do
            count
        }
        
        let e = provider.lastExpression
        AreEqualExpression e "SELECT COUNT(*) FROM Person"

    [<Test>]
    let ``count where``() =
        let q = query {
            for p in queryable<Person> do
            where(p.PersonName = "john")
            count
        }
        
        let e = provider.lastExpression
        AreEqualExpression e "SELECT COUNT(*) FROM Person WHERE (PersonName = 'john')"