F#查询串联

问题描述

我在F#中使用SqlDataConnection数据提供程序来迁移一些行，这种迁移的一部分是在3个表之间建立联接，将其视为表A，B，C其中B和C继承自A，所以我需要得到的是(类似于Linq):

I am using SqlDataConnection data provider in F# to migrate some rows, part of that migration is to make a join between 3 tables like this, think of it as an inheritance of tables A, B, C where B and C inherit from A so the thing is I need to get is (in Linq-like):

        Bs.Join(As, b.PK, a.FK).Select(new {...})
.Concat(Cs.Join(As, c.PK, a.FK).Select(new {...})

在F#中，我最接近的是:

 let result = seq {
     yield! query { ... }
     yield! query { ... }
 }

但是我被告知这将产生2条SQL查询，并且整体结果将在内存中.问题是:是否有一种方法可以在不使用seq的情况下将这种串联"作为query计算表达式，从而使所有操作都在单个SQL查询中发生?

but I've been told this will produce 2 SQL queries and the overall result will be on-memory. The question being: is there a way to make this "concatenation" as a query computation expression without using seq so that all happens in a single SQL query?

推荐答案

下面是一些示例代码，这些示例使用SQL UNION或UNION ALL组合了两个查询.

Here's some example code that combines two queries using SQL UNION or UNION ALL.

首先，设置.请注意，我已将日志记录添加到dbContext中，以便您了解幕后情况.

First, the setup. Note that I've added logging to dbContext so you can see what happens behind the scenes.

#r "System.Data.dll"
#r "System.Data.Linq.dll"
#r "FSharp.Data.TypeProviders.dll"

open System
open System.Linq
open Microsoft.FSharp.Data.TypeProviders

type sql = SqlDataConnection<connStr>

let createDbContext() =
    let dbContext = sql.GetDataContext()
    // add logging to console
    dbContext.DataContext.Log <- System.Console.Out
    dbContext

let db = createDbContext()
let products = db.Product

let q1 = query { for x in products do select x }
let q2 = query { for y in products do select y }

Union扩展方法使用UNION

let qUnion = q1.Union(q2)
qUnion.ToList() |> Seq.toList

这是记录的输出:

SELECT [t2].[Id], [t2].[Name]
FROM (
    SELECT [t0].[Id], [t0].[Name]
    FROM [dbo].[Product] AS [t0]
    UNION
    SELECT [t1].[Id], [t1].[Name]
    FROM [dbo].[Product] AS [t1]
    ) AS [t2]

Concat扩展方法使用UNION ALL

let qConcat = q1.Concat(q2)
qConcat.ToList() |> Seq.toList

这是记录的输出:

SELECT [t2].[Id], [t2].[Name]
FROM (
    SELECT [t0].[Id], [t0].[Name]
    FROM [dbo].[Product] AS [t0]
    UNION ALL
    SELECT [t1].[Id], [t1].[Name]
    FROM [dbo].[Product] AS [t1]
    ) AS [t2]

query表达式中的并集(AFIAK)没有特殊的语法.

There's no special syntax for unions in query expressions, AFAIK.

这篇关于F#查询串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！