Show database query generated by Entity Framework Core for PostgreSQL when SaveChanges() called

Question

I have created a database first implementation of entity framework core for postgreSQL. I have tried a simple item creation and save, but am getting a syntax error. I'd like to be able to see the generated SQL statement that might give me a clue as to the issue. How can I enable this type of output when debugging? I am new to EF in general, so for answers, please be specific and clear about the requirement.

Below is my test function:

public async Task<string> FunctionHandler(string input, ILambdaContext context)
{
    try
    {

        using (var db = new loyaltyContext())
        {
            var customer = new Customer
            {
                BusinessPartnerId = 4332482,
                StoreJoined = "chadstone",
                CreatedDateTime = DateTime.Now,
                CustomerType = "STANDARD",
                DateOfBith = DateTime.Now.Date,
                StoreJoinedDate = DateTime.Now.Date,
                UpdatedDateTime = DateTime.Now,
                FirstName = "Joe",
                LastName = "Test",
                Gender = "M",
                OrganisationName = "Australian Pharmaceutical Industries"
            };

            customer.Membership.Add(new Membership { BusinessPartnerId = 4332482, DateCreated = DateTime.Now, LoyaltyCustomerId = 1234, McaId = "2701000" });
            db.Customer.Add(customer);
            var count = db.SaveChanges();
            Console.WriteLine("${count} changes");
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }

    return input?.ToUpper();
}

Current error:

Npgsql.PostgresException (0x80004005): 42601: syntax error at end of input

   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 973

--- End of stack trace from previous location where exception was thrown ---

   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 407

   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 298

   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1195

   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1076

   at Npgsql.NpgsqlCommand.ExecuteReader() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1067

   at AWSLambdaLCSTest.Function.GetAll(NpgsqlConnection connection, String schema, String table) in C:\Users\JMatson\source\repos\AWSLambdaLCSTest\AWSLambdaLCSTest\Function.cs:line 109

   at AWSLambdaLCSTest.Function.FunctionHandler(String input, ILambdaContext context) in C:\Users\JMatson\source\repos\AWSLambdaLCSTest\AWSLambdaLCSTest\Function.cs:line 53

  Exception data:

    Severity: ERROR

    SqlState: 42601

    MessageText: syntax error at end of input

    Position: 22

    File: scan.l

    Line: 1126

    Routine: scanner_yyerror

EDIT: Updated by trying the below setting as per advice:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql("xxxxxxx").EnableSensitiveDataLogging(true);
}

However debug output still doesn't show anything:

Answer 1

I wrote a blog post on this very recently. Are you using .net core?

https://eamonkeane.dev/how-to-view-sql-generated-by-entity-framework-core-using-logging/

Enable sensitive data logging in your startup.cs class and in the ConfigureServices method.

 services.AddDbContext<DutchContext>(
            cfg =>
            {
                cfg.UseSqlServer(_config.GetConnectionString("DutchConnectionString")).EnableSensitiveDataLogging();
            }
            );

Next configure the logging level to include ef in your config file. Usually appsettings.json

"Logging": {
    "LogLevel": {
        "Default": "Information",
        "Microsoft": "Warning",
        "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
}

Now run the application and you can view the sql in your output window if you select the web server as source of the output.

output window example