BLToolkit : Table-value functions

来源：互联网发布：在淘宝哪里看手机评估编辑：程序博客网时间：2024/05/16 11:56

Is it possible to use SQL Server table-value functions by using the BLToolkit library?

I would like to use it within the Linq query, but I couldn't find anything regarding this on the library wiki.

1 Answers

answered 2011-07-14 14:06:10#1

Define your function in your data context class as the following:

[TableFunction(Name="GetParentByID")]public Table<Parent> GetParentByID(int? id){    return GetTable<Parent>(this, (MethodInfo)MethodBase.GetCurrentMethod(), id);}

Usage:

[Test]public void Func2(){    using (var db = new TestDbManager())    {        var q =            from c in db.Child            from p in db.GetParentByID(2)            select p;        q.ToList();    }}

SQL:

SELECT    [t2].[ParentID],    [t2].[Value1]FROM    [Child] [t1], [GetParentByID](2) [t2]

Also you can define it outside of the data context:

public class Functions{    private readonly IDataContext _ctx;    public Functions(IDataContext ctx)    {        _ctx = ctx;    }    [TableFunction]    public Table<Parent> GetParentByID(int? id)    {        return _ctx.GetTable<Parent>(this, (MethodInfo)(MethodBase.GetCurrentMethod()), id);    }    [TableExpression("{0} {1} WITH (TABLOCK)")]    public Table<T> WithTabLock<T>()        where T : class     {        return _ctx.GetTable<T>(this, ((MethodInfo)(MethodBase.GetCurrentMethod())).MakeGenericMethod(typeof(T)));    }}[Test]public void Func1(){    using (var db = new TestDbManager())    {        var q =            from p in new Functions(db).GetParentByID(1)            select p;        q.ToList();    }}[Test]public void WithTabLock(){    using (var db = new TestDbManager())    {        var q =            from p in new Functions(db).WithTabLock<Parent>()            select p;        q.ToList();    }}

SQL:

SELECT    [p].[ParentID],    [p].[Value1]FROM    [GetParentByID](1) [p]SELECT    [p].[ParentID],    [p].[Value1]FROM    [Parent] [p] WITH (TABLOCK)

0 0