F# SQLClient typeProvider减少重复代码

Question

我正在尝试使用FSharp.Data.SqlClient类型提供程序在F#中创建一个类型安全的数据访问层，以便可以从C#调用。我有非常复杂的SQL查询，这些查询对性能非常关键，但也有几个变体。给定以下内容(明显简化的模式)：

CREATE TABLE [dbo].[company] (
    [id]                                INT           IDENTITY (1, 1) NOT NULL,
    [name]                              VARCHAR (50)  NOT NULL)

我有以下F#代码：

module CompanyDAL =
    open FSharp.Data // requires FSharp.Data.SqlClient package
    [<Literal>]
    let conn = "Data Source=(localdb)\ProjectsV13;Initial Catalog=TESTDB;Integrated Security=True;Pooling=False;Connect Timeout=30"
    [<Literal>]
    let baseQuery       = "select id, name from company where 1 = 1 "
    [<Literal>]
    let filterById      = "and id = @id"
    [<Literal>]
    let filterByName    = "and name = @name"
    [<Literal>]
    let queryFilteredById = baseQuery + filterById
    type GetCompanyById = SqlCommandProvider<queryFilteredById, conn>
    [<Literal>]
    let queryFilterbyName = baseQuery + filterByName
    type GetCompanyByName = SqlCommandProvider<queryFilterbyName, conn>
    type GetAllCompanies = SqlCommandProvider<baseQuery, conn>
    type Company = {
        Name    : string
        id      : int
    }

    let getCompanyById (runtimeConn:string) = 
        async {
            use query = new GetCompanyById(runtimeConn)
            let! result = query.AsyncExecute(id = 10) 
            return result
            |> Seq.map (fun x -> 
              { Name = x.name
                id = x.id })
            |> Seq.toArray
        } |> Async.StartAsTask

    let getCompanyByName (runtimeConn:string) = 
        async {
            use query = new GetCompanyByName(runtimeConn)
            let! result = query.AsyncExecute(name = "abc" )
            return result
            |> Seq.map (fun x -> 
              { Name = x.name
                id = x.id })
            |> Seq.toArray
        } |> Async.StartAsTask

    let getAllCompanies (runtimeConn:string) = 
        async {
            use query = new GetAllCompanies(runtimeConn)
            let! result = query.AsyncExecute() 
            return result
            |> Seq.map (fun x -> 
              { Name = x.name
                id = x.id }) 
            |> Seq.toArray
        } |> Async.StartAsTask

有没有办法在保持类型安全raw查询的原始性能的同时减少重复的次数？

Answer 1

我试过用

 type GetCompany = SqlEnumProvider<baseQuery, conn, provider>

然后我就可以写这段代码了

GetCompany.Items |> Seq.where(fun (id, name) -> name = "xxx") |> printfn "%A"

因此，我可以避免重复，并将泛型谓词fun (id, name) ->传递给唯一的GetCompany类型。

类似地，

GetCompany.Items |> Seq.where(fun (id, name) -> id = "1")

也行得通。

备注

无论如何，请注意上面的代码将在内存中过滤。实际上，这个库是Dynamic SQL is not permitted的，因为它只依赖于编译时的静态分析。如果需要动态构造查询，可以恢复到原始的ADO.NET或其他轻量级的对象关系映射。