C#EF框架调用数据库的函数

C#EF框架调用数据库的函数
1、在数据库中创建一个自定义函数：
REATE FUNCTION [dbo].[f_IsOriginsDisabled]
(
@origins varchar(50),
@needPPTV bit
)
RETURNS bit
AS
BEGIN
if @origins=''
begin
RETURN 1;
end
declare @disables varchar(50);
set @disables='';
if @needPPTV=0
begin
set @disables=@disables +',6';
end
select @disables=@disables+','+CAST(Id AS varchar(50))+',' from Video_Origin
where IsDel=0 and IsEnabled=0

DECLARE @ix int,@pos int,@str varchar(1000);
SET @pos=1;
SET @ix=1;
WHITE @ix>0
BEGIN
SET @ix=charindex(',',@origins,@pos)
IF @ix>0
SET @str=substring(@origins,@pos,@ix-@pos);
ELSE
SET @str=substring(@origins,@pos,len(@origins));
SET @str=ltrim(rtrim(@str));
if charindex(','+@str+',',@disables)<=0
return 0;
SET @pos=@ix+1;
END
RETURN 1;
END
GO
2、创建EF的数据库上下文
引用EntityFramework.Functions程序集
public class MediaDBContext:DbContext,IDisposable
{
pubic MediaDBContext():base("MediaDBContext"){}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Add(new FunctionConvention<MediaDBContext>());
}
[Function(FunctionType.ComposableScalarValuedFunction,"f_IsOriginsDisabled",Schema="dbo")]
[return:Parameter(DbType="bit")]
public bool IsOriginsDisabled([Parameter(Dbtype="varchar")]string origins,[Parameter (DbType="bit")]bool needPPTV)
{
return Function.CallNotSupported<bool>();
}
}
3、调用方式
var query=context.Movies.Where(m=> !context.IsOriginsDisabled(m.Origins,hasPPTV);