Include error detail in the connection string

Повторяющееся значение ключа нарушает уникальное ограничение «PK_Users». Ошибка в EntityFramework при попытке вставить объект с отношением 1: N.

Я создаю приложение веб-API ASP.NET, и у меня есть две сущности: пользователь и устройство. У пользователя есть отношения «один ко многим» с устройствами (у пользователя несколько устройств). Проблема в том, что когда я вставляю новое устройство с определенным идентификатором пользователя, я получаю неприятную ошибку из базы данных Posgres, которую я использую. Я начну с показа своих сущностей:

Нет необходимости показывать метод контроллера, который перехватывает почтовый запрос для вставки устройства, этот метод просто вызывает следующую служебную функцию:

Я использую шаблон репозитория с единицей работы. Общий метод Insert в репозитории очень прост и хорошо работает с другими объектами:

Позвольте мне теперь объяснить детали моей проблемы. Например, у меня в базе данных есть пользователь с идентификатором 1. В Swagger я хочу вставить следующее устройство, например:

Я говорю, что это устройство принадлежит пользователю с идентификатором 1. Код ответа на запрос, который я получаю, — это внутренняя ошибка сервера 500 и следующая ошибка:

Когда я вставляю устройство с идентификатором пользователя, которого еще нет в базе данных, например, например, 10, оно создает нового пользователя с идентификатором 10 со всеми пустыми полями. Эта ошибка возникает при вызове _context.SaveChangesAsync (). Если мне нужно вставить устройство в базу данных с данными, представленными выше, с использованием простого SQL непосредственно в Postgres, оно будет работать нормально. EntityFramework делает что-то не так, или я что-то делаю не так. В чем может быть причина моей проблемы? Если вам потребуется дополнительная информация, я с радостью ее предложу, мне срочно нужно решить эту проблему. Спасибо!

Изменить: Моя первая миграция выглядит так:

Я добавил свойство навигации, как сказал @Thyselius, и это не решило проблему. Это миграция после добавления:

Источник

Name already in use

doc / conceptual / Npgsql / connection-string-parameters.md

• Go to file T
• Go to line L
• Copy path
• Copy permalink

Copy raw contents

Copy raw contents

Connection String Parameters

To connect to a database, the application provides a connection string which specifies parameters such as the host, the username, the password, etc. Connection strings have the form keyword1=value; keyword2=value; and are case-insensitive. Values containing special characters (e.g. semicolons) can be double-quoted. For more information, see the official doc page on connection strings.

Below are the connection string parameters which Npgsql understands, as well as some standard PostgreSQL environment variables.

Security and encryption

Timeouts and keepalive

Failover and load balancing

1 The Options connection string parameter is essentially the string of command line options that get passed to the postgres program when the process is started. It is most commonly used to set named run-time parameters via the -c option but other options can be used too (although not all of them make sense in that context). Setting multiple options is possible by separating them with a space character. Space and backslash characters in option values need to be escaped by prefixing a backslash character. Example: Options=-c default_transaction_isolation=serializable -c default_transaction_deferrable=on -c foo.bar=My\ Famous\\Thing

In addition to the connection string parameters above, Npgsql also recognizes the standard PostgreSQL environment variables below. This helps Npgsql-based applications behave similar to other, non-.NET PostgreSQL client applications. The PostgreSQL doc page on environment variables recognized by libpq can be found here.

Источник

newly created Oracle connection fails, visual studio code #137

Describe the bug
When trying to connect to oracle database with new connection, notification continues to say «You need «oracledb@3.1.1″ to connect to database.» When I click install i get no response with no connection or a «Request DependencyInstaller/install failed unexpectadly without providing any details» Error with no connection. FYI I have already installed node js and node-oracledb was already installed. I also set the node runtime variable in vscode settings.json. Is there another step I am missing?

To Reproduce
Steps to reproduce the behavior:

1. Create a new connection with Oracle
2. Enter necessary db details to connection page
3. click create
4. See Notification then bug

Expected behavior
Connect to database to begin writing queries

Desktop (please complete the following information):

Additional context
first time user of sqltools

The text was updated successfully, but these errors were encountered:

Hey @diggerydoo68 .
Please post output from «Output» view (Ctrl + Shift +U) — channel «SQLTools Language Server» (right corner dropdown) just after the error happens, And also from channell «SQLTools».
That would be really helpful. Thanks

@mickeypearce I’m assigning this one to you, so you can verify and work on it.

If confirmed, let me in case we need to publish it.

I realized after looking at the output(thanks for that info) why it was having trouble installing the oracledb extension. Now the extension is installed but I am getting a «ORA-12154: TNS: could not resolve the connect identifier specified» error. Does SQLtools use a ODBC driver? How does sqltools locate the tsnames.ora file?

SQLTools uses node-oracledb driver that uses Oracle native client (Instant Client, for ex.) for connecting to Oracle DB.

Service name should be specified in «database» property of connections setting:

For example if you have tnsnames.ora file with service name defined or any other alternative connection strings:

<
«dialect»: «OracleDB»,
«name»: «oracledb2»,
«username»: «hr»,
«password»: «welcome»,
«askForPassword»: false,
«connectionTimeout»: 15,
«database»: «oraclpdb»
>

If that wouldn’t work you can include the whole connection string directly in «database» property:
«database: «hostname:port/service_name» , including alternative types, etc

Property «port» and «server should be excluded! in this case.

@diggerydoo68 did you get it running?

Let us know so we could close this issue.

Still working on it. Where is it writing the connections details to? I don’t see it in the settings.json file nor my oracle files (tnsnames.ora or oracle instant client folder). I created a connection through the regular «Setup a new connection» interface and I added the whole connection string to the the «database field» and excluded the port field and it still gave me the above Oracle error after trying to connect.

a follow up question does sqltools work with LDAP?

In the workspace settings. Try manually adding connection to settings.json. maybe there is a problem if ./.vscode/settings.json doesn’t exist already.

Maybe we should print actual connection string to output for easier debugging or even leave out port and server option completely.

I’m having the same problem as first described. In my case, the installation fails because of the proxy authentication settings.
It seems that the installation command does not use the .npmrc settings on my user profile nor the «http.proxy» from vscode?.
Do I need to add those settings somewhere else?

Hmmm. I’ll take a look. I haven’t tested with a proxy. Should work seamlessly, but I better to take a second look at this.

@rogenaro if you have JS skills and wants to try, take a shot please.

There is a link in the comment with proxy instructions.

@mtxr, where libs get installed to try this first manually? In the ext folder or somewhere else?

@rogenaro can you provide some information about your setup?
Which OS, npm/node version are you using? Where is you npmrc file? Workspace dir or home dir?

no dice. I manually added the connection details to the settings.json file. I ran it and it still gave me the TNS error. How do I know that it is using that settings file? If I go and create the connection through the extension interface it does not change my settings.json file. Should it? I also tried leaving out the server and port option. It still gives me the TNS oracle error. I’m a little fuzzy on how I can print the connection string to the output interface. How would that work with this extension?

@mtxr , do you perhaps have any idea why it wouldn’t add/create settings file? I tried with no folder open, but it prints an error about not opened workspace. 👍

@diggerydoo68 , yes, it should be added to your workspace setting file. Do you see your connection in connection explorer? Can you please check your user settings if it was added there for some reason. (%USERPROFILE%AppDataRoamingCodeUsersettings.json or «Preferences: Open User Settings» command.

ok so whenever I create a new connection it shows up in the connection explorer and the connection details are written to the settings.json from the workspace directory and nothing gets written to the settings.json for the user settings (%USERPROFILE%AppDataRoamingCodeUsersettings.json ). I need to keep my settings.json files straight. So usually I am connecting via sqldeveloper to the database using an LDAP connection.

About LDAP connection: oracle/node-oracledb#287 (comment)
There is a link in the comment that could be helpful. Unfortunately you need some extra steps.

@mickeypearce it’s a bug. We should be able to create connections for workspaces, or just in user settings.

I’ll fix this tonight. Thanks!

I’ve fixed the add connection issue. I’m keeping the default behavior but I added an option to always save to globalSettings.

I’ll close this issue since @mickeypearce comment seems to resolve the LDAP connection.

@mickeypearce or @diggerydoo68 would be great if you could write a guide to get LDAP running with the extension.

@diggerydoo68 if it doesn’t solve your problem, feel free to reopen this issue for further investigation.

I tried to use this plugin to connect to my work database but no luck.
Still using Datagrip with no problem.

Источник

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (197ms) [Parameters=[@p0='?', @p1='?', @p2='?' (DbType = DateTimeOffset), @p3='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "FileInfos" ("FileId", "FileName", "LastModifiedDateTime", "Path")
      VALUES (@p0, @p1, @p2, @p3);
fail: Microsoft.EntityFrameworkCore.Update[10000]
  An exception occurred in the database while saving changes for context type 'PostgreSQLConnect.ContextModels.WebhookContext'.
  Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
   ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_FileInfos 
    Severity: FEHLER
      SqlState: 23505
      MessageText: double key value violates unique constraint »PK_FileInfos«
      Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
      SchemaName: public
      TableName: FileInfos
      ConstraintName: PK_FileInfos
      File: d:pginstaller_12.autopostgres.windows-x64srcbackendaccessnbtreenbtinsert.c
      Line: 570
      Routine: _bt_check_unique

    private async Task SaveFileInfos(FileInfo fileInfo)
    {
        var foundFileInfo = _context.FileInfos.
        Where(f => f.FileId == fileInfo.FileId).FirstOrDefault();

        if (foundFileInfo == null)
        {
            await _context.FileInfos.AddAsync(fileInfo);
        }
        else
        {
            foundFileInfo.FileName = fileInfo.FileName;
            foundFileInfo.LastModifiedDateTime = fileInfo.LastModifiedDateTime;
            foundFileInfo.Path = fileInfo.Path;
        }

        await _context.SaveChangesAsync();
    }
     

    public class FileInfo : IFileInfo
    {

    [Key]
    public string FileId {get; set;}

    public string FileName {get; set;}

    public DateTimeOffset? LastModifiedDateTime {get; set;}

    public string Path {get; set;}
    }

   public class WebhookContext : DbContext
   {
    public WebhookContext(DbContextOptions<WebhookContext> options) : base(options) { }

    public DbSet<FileInfo> FileInfos { get; set; }
    }

 private async Task ConvertAndSaveFiles(IDriveItemDeltaCollectionPage files)
  { 

    foreach (var file in files)
    {
         await SaveFileInfos(file.Name, file.Id, file.LastModifiedDateTime,
                    file.ParentReference.Path);
    }
           
  }

2 ответа

Лучший ответ

    private async Task SaveFileInfos(FileInfo fileInfo)
    {
        //update your code to use FirstOrDefaultAsync
        var foundFileInfo = await _context.FileInfos
        .FirstOrDefaultAsync(f => f.FileId == fileInfo.FileId);

        if (foundFileInfo == null)
        {
            await _context.FileInfos.AddAsync(fileInfo);
        }
        else
        {
            foundFileInfo.FileName = fileInfo.FileName;
            foundFileInfo.LastModifiedDateTime = fileInfo.LastModifiedDateTime;
            foundFileInfo.Path = fileInfo.Path;
        }

       // move this outside the for loop.
       // this will round trip to Db in EVERY fileInfo, not an optimal solution.
        await _context.SaveChangesAsync(); 
    }

 private async Task ConvertAndSaveFiles(IDriveItemDeltaCollectionPage files)
  { 

    foreach (var file in files)
    {
         await SaveFileInfos(file.Name, file.Id, file.LastModifiedDateTime,
                    file.ParentReference.Path);
    }

    // this will save everything to Db in just 1 round trip
    await _context.SaveChangesAsync(); 
}