How to do a raw sql insert query with EF Core that needs to return rows?

user_1987258 0
Viewed 2701

I use EF Core for PostgreSQL as explained here. I want to execute some raw sql queries that return rows.

The most simple is this one (it may look strange as it returns exactly what is inserted but it is simple so good to be used as an example):

INSERT INTO public."TodoItems" ("Id", "Todo") VALUES ('915714b6-1c67-444d-8106-d778fcb4bb1a', 'Hi') RETURNING "Id", "Todo"

Here is my context class:

public class DoContext : DbContext
{
    public DoContext(DbContextOptions<DoContext> options) : base(options)
    {
        Console.WriteLine("Context created");
    }

    public DbSet<TodoItem> TodoItems { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        Console.WriteLine("OnModelCreating");
    }
}

Here is the entity class.

public class TodoItem
{
    public Guid Id { get; set; }

    public string Todo { get; set; }
}

Here is my code to execute the query:

string query = "INSERT INTO public.\"TodoItems\" (\"Id\", \"Todo\") VALUES ('915714b6-1c67-444d-8106-d778fcb4bb1a', 'Hi') RETURNING \"Id\", \"Todo\"";
var result = context.TodoItems.FromSqlRaw(query).First();

My query is fine. It works when the table is empty and the query executed it in DataGrip. However, when running my code, The last line causes the following exception:

Npgsql.PostgresException 42601: syntax error at or near "INTO"

What I understood from the documentation is that FromSqlRaw is the right method to use when rows are expected to be returned. However, this method does not seem to understand my (tested and valid) query.

So how can I execute this query with EF core? What is important here is that I really what to use EF Core as I need the mapping between the sql world and the entities I have. I use .NET 5.

entity-framework-core postgresql
1 Answers

Avoid using query limiting operators like First and similar, because EF Core will try to compose over provided SQL - basically wrapping it inside outer SELECT (SELECT * FROM (Your_SQL) in order to apply TOP N or similar operator. Which leads to invalid SQL syntax.

So it might seem strange, but use materializing operator instead - ToList(), ToArray() or AsEnumerable() to stop the IQueryable processing and execute the database query at that point, e.g.

var result = context.TodoItems.FromSqlRaw(query)
    .AsEnumerable() // <-- database query ends here
    .First(); // <-- this runs in LINQ to Objects context
user_52025630
Related