introduce

I haven't updated the article for a long time. I returned to Qingdao for family reasons in the first two months, so I'm busy, so I don't have much time to update and share knowledge with you. I've found a lot of interesting things to tell you in the past two months.

text

Let's start with the following picture. This is the code of an old project of our company. You may be curious why you show me what SQL says?

Look at the part I framed. Here we call an SQL function. Although we all shout not to use stored procedures, functions, triggers and other SQL related things every day in the process of using EF, we actually land on a project with a large enough volume,
We will find that many things are beyond our control. When customers insist on something, we can only find ways to design better. Of course, the above figure is a wrong way of writing.

The query, addition and modification of many entities in this project need to call encryption and decryption functions, so this part of the code is done with native SQL, which greatly destroys the overall code structure of our project.

In fact, EF itself supports us to call SQL functions.

Now give you the code

First, create 3 entities

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");
        }
    }

Mapping methods to custom SQL

First create a custom function

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

Then add the following code in DbContext,

// The body of the CLR method is not important. This method is not called on the client unless EF Core cannot convert its parameters. If parameters can be converted, the EF Core only cares about method signatures.
public int ActivePostCountForBlog(int blogId)
    => throw new NotSupportedException();


// This function definition can now be associated with user-defined functions in the model configuration
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
    .HasName("CommentedPostCountForBlog");

We execute the following code

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

// Corresponding SQL statement

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

Mapping queryable functions to table valued functions

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) }));

Execute the following code

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

// Corresponding SQL statement

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

epilogue

Finally, you are welcome to pay attention to my blog, https://github.com/MrChuJiu/Dppt Welcome, Star

Contact author: Jia Qun: 867095512 @ mrchujiu