EF Core FromSQL query does not get executed immediately (PostgreSQL)

.net-core entity-framework-core function postgresql stored-procedures

Question

I have written a function in PostgreSQL for insertion as follows:

CREATE OR REPLACE FUNCTION public.insert_blog("Url" character)
  RETURNS void AS
$BODY$Begin
    Insert Into "Blogs"("Url") Values("Url");
End$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.insert_blog(character)
  OWNER TO postgres;

The above function adds an entry into the Blogs table (Url is a parameter).

I am trying to use this function in .Net Core (Npgsql.EntityFrameworkCore.PostgreSQL) as follows:

    [HttpPost]
    [ValidateAntiForgeryToken]
    public IActionResult Create(Blog blog)
    {
        if (ModelState.IsValid)
        {
            //This works fine
            var count = _context.Blogs.FromSql("Select insert_blog({0})", blog.Url).Count(); 

            //This doesn't work -- it gives an error of "42601: syntax error at or near "insert_blog""
            //var count = _context.Blogs.FromSql("insert_blog @Url={0}", blog.Url).Count();                

            return RedirectToAction("Index");
        }

        return View(blog);
    } 

Can someone tell me why the second command is not working? Also, even if the first command is working, is it the right way?

Since I have to write .FromSql(...).Count() in order for it to work, if I remove .Count() item doesn't get inserted. Can someone tell me why this is happening?

Is there any good article on using .FromSql() or "Using Postgres functions in entity framework core" (I'd guess that this is a new feature and that that's why I couldn't find much data on this)?

1
4
9/10/2018 7:46:08 AM

Popular Answer

Can some one tell me why the second command is not working? Also even if the first command is working, is it the right way?

It's simply just not the way PostgreSQL syntax works. Select insert_blog({0}) is indeed the right way.

Since I have to write .FromSql(...).Count() in order for it to work. If I remove ".Count()" item doesn't get inserted. Can someone tell me why this is happening?

FromSql behaves just like Where and other functions on an IQueryable. Execution is postponed until the results are requested, because it will try to do everything in one database query.

To make sure your query actually gets executed, you need to call a method that returns something other than IQueryable such as .Count() or .ToList(). More info can be found here: https://docs.microsoft.com/en-us/ef/core/querying/overview#when-queries-are-executed

1
9/20/2018 10:38:42 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow