CODEWITHSUNDEEP
typesf#functional-programmingtype-providers
Richard Griffiths
Richard Griffiths

Reputation: 838

F# type confusion - I'm sure I'm getting the value I need but the compiler, at run time, does not agree

I've got a nice QC testing tool that I access via VB from an existing setup. This tool give me strong typed access to the tables without making new classes etc. However one of the tables, sadly, uses a commalist instead of child tables.

This commalist is causing me issues. The code below, in theory, should split the row.commalist and then tell me if a given number lies within.

I was able to output the value from the CommaListValues bit - it's bool. I feed it the parameters as given and the compiler seems happy.

Why won't it, at run time, use the boolean as criteria? I've got a mental block I guess - I don't use F# routinely but I'm very much wanting to - unfortunately these niggles eat the time.

Can anyone help?

Edited as requested. The typical values for the commalist sample would be:"234,132,554,6344,243,677"

module quality_control

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

type dbschema = SqlDataConnection<"Data Source=SomeServer;Initial Catalog=SQLDB;User ID=sa;Password=######">

let db = dbschema.GetDataContext()

type QualityChecks(SingleIDToMatch) =
  let questionList ( q : string, SingleIDToMatch: string) = q.Split [| ','|] |> fun x -> x.Equals(SingleIDToMatch) 

  member x.MatchID=
      query{
       for row in db.SomeTableWithCommaListColumn do
       where (questionList(row.CommaListValue, System.Convert.ToString(SingleIDToMatch)))
       select row}

I get the following error:

System.ArgumentException occurred HResult=-2147024809 Message=The argument 'value' was the wrong type. Expected 'System.Func2[System.Tuple2[System.String,System.String],System.Boolean]'. Actual 'System.Boolean'. Source=System.Data.Linq StackTrace: at System.Data.Linq.SqlClient.SqlMethodCall.set_Object(SqlExpression value) at System.Data.Linq.SqlClient.SqlMethodCall..ctor(Type clrType, ProviderType sqlType, MethodInfo method, SqlExpression obj, IEnumerable1 args, Expression sourceExpression) at System.Data.Linq.SqlClient.SqlFactory.MethodCall(MethodInfo method, SqlExpression obj, SqlExpression[] args, Expression sourceExpression) at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression exp) at System.Data.Linq.SqlClient.QueryConverter.VisitWhere(Expression sequence, LambdaExpression predicate) at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Collections.Generic.IEnumerable.GetEnumerator() at REMOVED PROJECT DETAILS InnerException:

Upvotes: 0

Views: 314

Answers (1)

Squimmy
Squimmy

Reputation: 587

The type provider attempts to convert the query expression into SQL, but it isn't able to translate the questionList() call into a WHERE clause. The simple solution is to change MatchID() to look like this:

member x.MatchID=
    let idString = singleIDToMatch.ToString()
    query {
        for row in db.SomeTableWithCommaListColumn do
        where (row.CommaListValue.EndsWith("," + idString)
            || row.CommaListValue.StartsWith(idString + ",")
            || row.CommaListValue.Contains("," + idString + ","))
        select row }

This executes the following SQL:

SELECT [t0].[CommaListValue]
FROM   [dbo].[SomeTableWithCommaListColumn] AS [t0]
WHERE  ([t0].[CommaListValue] LIKE @p0)
       OR ([t0].[CommaListValue] LIKE @p1)
       OR ([t0].[CommaListValue] LIKE @p2)

(the parameters include the appropriate wildcards to give results equivalent to the F# code in the query expression)

Composition

If you need to compose multiple WHERE clauses, you can chain query expressions, like this:

let withAtLeast10Characters (q : Linq.IQueryable<dbschema.ServiceTypes.SomeTableWithCommaListColumn>) =
    query {
        for row in q do
        where (row.CommaListValue.Length >= 10)
        select row }

let result = qualityChecks.MatchID |> withAtLeast10Characters

When this is evaluated, you'll get SQL that looks like this:

SELECT [t0].[CommaListValue]
FROM   [dbo].[SomeTableWithCommaListColumn] AS [t0]
WHERE  (DATALENGTH([t0].[CommaListValue]) >= @p0)
       AND (([t0].[CommaListValue] LIKE @p1)
            OR ([t0].[CommaListValue] LIKE @p2)
            OR ([t0].[CommaListValue] LIKE @p3))

Upvotes: 3

Related Questions