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)?
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