浅讲EF高级用法之自定义函数

介绍

好久没给大家更新文章了，前2个月因家庭原因回到青岛，比较忙所以没有什么时间给大家更新知识分享，这2个月在和同事一起做项目，发现了很多好意思的东西拿出来给大家讲一讲。

正文

大家先来下面这幅图，这是我司一个老项目的代码，你可能会好奇为啥给我看SQL说好的讲EF哪？

大家看这个我框出来的部分，这里调用了一个SQL的函数，虽然我们都在使用EF的过程中每天喊着不要使用存储过程、函数、触发器等SQL相关的东西，但是其实真实落地到体积足够庞大的项目后，
我们会发现，很多东西不是我们能够左右的。当客户执意一些东西的时候我们只能想办法设计的更好，当然上面的图是一个错误的写法。

这个项目中有很多实体的查询、添加、修改需要调用加密、解密函数，所以这部分代码都采用原生SQL来做的，这非常破坏我们项目整体的代码结构。

其实EF本身是支持我们调用SQL函数的。

现在就给大家上代码

首先新建3个实体

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int? Rating { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int Rating { get; set; }
    public int BlogId { get; set; }

    public Blog Blog { get; set; }
    public List<Comment> Comments { get; set; }
}

public class Comment
{
    public int CommentId { get; set; }
    public string Text { get; set; }
    public int Likes { get; set; }
    public int PostId { get; set; }

    public Post Post { get; set; }
}

  public class ApplicationDbContext : DbContext
    {

        public DbSet<Blog> Blog { get; set; }

        public DbSet<Post> Post { get; set; }

        public DbSet<Comment> Comment { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Blog>()
                .HasMany(b => b.Posts)
                .WithOne(p => p.Blog);

            modelBuilder.Entity<Post>()
                .HasMany(p => p.Comments)
                .WithOne(c => c.Post);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCoreDbFunction.Disconnected;Trusted_Connection=True;ConnectRetryCount=0");
        }
    }

将方法映射到自定义 SQL

先创建一个自定义函数

CREATE FUNCTION dbo.CommentedPostCountForBlog(@id int)
RETURNS int
AS
BEGIN
    RETURN (SELECT COUNT(*)
        FROM [Post] AS [p]
        WHERE ([p].[BlogId] = @id) AND ((
            SELECT COUNT(*)
            FROM [Comment] AS [c]
            WHERE [p].[PostId] = [c].[PostId]) > 0));
END

然后在DbContext中新增下面代码,

// CLR 方法的主体并不重要。 不会在客户端调用此方法，除非 EF Core 不能转换其参数。 如果可以转换参数，EF Core 只关心方法签名。
public int ActivePostCountForBlog(int blogId)
    => throw new NotSupportedException();


// 此函数定义现在可以与模型配置中用户定义的函数关联
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
    .HasName("CommentedPostCountForBlog");

我们执行下面代码

var query1 = from b in context.Blogs
             where context.ActivePostCountForBlog(b.BlogId) > 1
             select b;

// 对应SQL语句

SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1

将可查询函数映射到表值函数

CREATE FUNCTION dbo.PostsWithPopularComments(@likeThreshold int)
RETURNS TABLE
AS
RETURN
(
    SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
    FROM [Posts] AS [p]
    WHERE (
        SELECT COUNT(*)
        FROM [Comments] AS [c]
        WHERE ([p].[PostId] = [c].[PostId]) AND ([c].[Likes] >= @likeThreshold)) > 0
)

public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
    => FromExpression(() => PostsWithPopularComments(likeThreshold));


modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), new[] { typeof(int) }));

执行下面代码

var likeThreshold = 3;
var query1 = from p in context.PostsWithPopularComments(likeThreshold)
             orderby p.Rating
             select p;

// 对应SQL语句

SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]

结语

最后欢迎各位读者关注我的博客， https://github.com/MrChuJiu/Dppt 欢迎大家Star

联系作者：加群：867095512 @MrChuJiu