3

I have a SQL table-valued function like this:

CREATE FUNCTION [Foo] (@bar VARCHAR(255))
RETURNS @result TABLE 
(
    [Column1] INT,
    [Column2] INT,
    [Column3] INT,
    [Column4] INT
)
..

I'd like to be able to perform queries against it in SqlKata, by calling the function with a proper parameter.

Is it possible? If so how?

Note: This question is somehow related to is there any way to create query including function in projection and outer apply in sqlkata? , but I need to pass a parameter too.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Starnuto di topo
  • 3,215
  • 5
  • 32
  • 66

2 Answers2

4

The simplest way is to use FromRaw

new Query().FromRaw("[Foo](?)", bar).Select("Column")

Side note: using the QueryFactory is way easier than executing the query by yourself.

var result = db.Query().FromRaw("[Foo](?)", bar).Select("Column").Get();
amd
  • 20,637
  • 6
  • 49
  • 67
  • 1
    Can I change the parameter name in the query condition? query.WhereIn(Product, Value) this will become, WHERE ([Product].[Name] = @p0) but I need proper parameter name like, WHERE ([Product].[Name] = @productName) – Madhan kumar D Feb 24 '20 at 07:26
2

I managed to get the result accessing the my function like a CTE, i.e.: using WithRaw and an alias:

            var query = new Query("MyAlias")
                .WithRaw("MyAlias", "Select Column1 from [Foo](@bar)" )
                .Select("Column1");
            var compiled = _compiler.Compile(query);
            var result1 = connection.Query(compiled.Sql, new {bar = bar})
                .ToList();
Starnuto di topo
  • 3,215
  • 5
  • 32
  • 66