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:

Eamon · Accepted Answer · 2020-05-19 09:56:44Z

4

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

answered May 19, 2020 at 9:56

Eamon

711 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JamesMatson Over a year ago

Thanks. I'll update my post with what I added, but still nothing is logged in debug output. I didn't update appsettings.json as I don't have one. I'm testing this in a Lambda function (AWS) but am using .NET Core. Is there a way to set this value in code just for testing?

Eamon Over a year ago

Try showing output from webserver and not debug. See if that helps? Just use the drop down. Though if the logger isn't configured to log ef core commands i don't see it working this way. where to you specify connection strings or stuff like that? There should be some config i'm guessing?

JamesMatson Over a year ago

Because it's a Lambda function, I can add configuration as environment variables. There's no appSettings.json to speak of. Is there a way to hard code the config just for testing purposes? Or an alternate method to output the queries generated?

Eamon Over a year ago

Try adding this to your env variables KEY: 'Logging:LogLevel:Microsoft.EntityFrameworkCore.Database.Command' VALUE: 'Information'

prinkpan Over a year ago

Thank you @Eamon, I cannot believe it is so difficult to find this simple solution on Microsoft's documentation

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related