CRITICAL BUG Changing exclude columns or include seems to do nothing, even insert if not exist example dosnt work. (Postgress)

[dagtveit]

As an example i am trying wiithh different ways look in commented code.

             await _context.BulkInsertOrUpdateAsync(newPriceData.ToList(), config =>
                {
                    // config.PropertiesToExcludeOnUpdate = new List<string>
                    // {
                    //     nameof(ImportedPrice.LastAdjustmentDate),
                    //     nameof(ImportedPrice.SellPriceAdjusted),
                    // };
                    // config.PropertiesToIncludeOnCompare =new List<string>
                    // {
                    //     nameof(ImportedPrice.Date),
                    //     // nameof(ImportedPrice.SellPriceAdjusted),
                    // };                    
                    // config.PropertiesToIncludeOnUpdate =new List<string>
                    // {
                    //     nameof(ImportedPrice.Date),
                    //     // nameof(ImportedPrice.SellPriceAdjusted),
                    // };
                    config.PropertiesToIncludeOnUpdate = new List<string> {""};
                    // config.PropertiesToExclude = new List<string>()
                    // {
                    //     nameof(ImportedPrice.LastAdjustmentDate),
                    //     nameof(ImportedPrice.SellPriceAdjusted),
                    //     nameof(ImportedPrice.SellPrice),
                    //     nameof(ImportedPrice.LastAdjustmentDate),
                    //     nameof(ImportedPrice.AvailableInstrumentId),
                    //     nameof(ImportedPrice.Date),
                    // };
                    
                    config.UpdateByProperties = new List<string>
                    {
                        nameof(ImportedPrice.AvailableInstrumentId),
                        nameof(ImportedPrice.Date)
                    };
                });

they all seem to generate te same SQL

INSERT INTO "ImportedPrices" ("AvailableInstrumentId", "Date", "LastAdjustmentDate", "SellPrice", "SellPriceAdjusted") 
(SELECT "AvailableInstrumentId", "Date", "LastAdjustmentDate", "SellPrice", "SellPriceAdjusted" FROM "ImportedPricesTempc7dd30c4")
ON CONFLICT ("AvailableInstrumentId", "Date") DO UPDATE SET "AvailableInstrumentId" = EXCLUDED."AvailableInstrumentId",
                                                            "Date"                  = EXCLUDED."Date",
                                                            "LastAdjustmentDate"    = EXCLUDED."LastAdjustmentDate",
                                                            "SellPrice"             = EXCLUDED."SellPrice",
                                                            "SellPriceAdjusted"     = EXCLUDED."SellPriceAdjusted";

and they are in fact updating te existing row in the database. i keepediting the sellprice column and try to run this. but whatever i doo it gets updated with values from the incoming data set..

[dagtveit]

i change this in sqlquerybuilder to get somethign closer to somethign that works. though i dont know the implications of not doing all the other logic inside that getcolumnlist.

                // var columnsListEquals = GetColumnList(tableInfo, OperationType.Insert);
                var equalsColumns = SqlQueryBuilder.GetCommaSeparatedColumns(tableInfo.PropertyColumnNamesUpdateDict.Values.ToList(), equalsTable: ```
"EXCLUDED").Replace("[", @"""").Replace("]", @"""");

                q = $"INSERT INTO {tableInfo.FullTableName} ({commaSeparatedColumns}) " +
                    $"(SELECT {commaSeparatedColumns} FROM {tableInfo.FullTempTableName}) " +
                    $"ON CONFLICT ({updateByColumns}) ";
                if (tableInfo.PropertyColumnNamesUpdateDict.Values.Count > 0)
                {
                    q = q + $"DO UPDATE SET {equalsColumns}";
                }
                else
                {
                    q = q + "DO NOTHING";
                }
 

[borisdj]

Currently that feature is supported only on SqlServer. Will see to extend it soon for PG as well.

[dagtveit]

I did some hack that seem to work. But not shure if the side effects

Get Outlook for Androidhttps://aka.ms/AAb9ysg

---

From: Boris Djurdjevic @.> Sent: Thursday, April 14, 2022 10:23:33 PM To: borisdj/EFCore.BulkExtensions @.> Cc: dagtveit @.>; Author @.> Subject: Re: [borisdj/EFCore.BulkExtensions] CRITICAL BUG Changing exclude columns or include seems to do nothing, even insert if not exist example dosnt work. (Postgress) (Issue #791)

Currently that is supported only for SqlServer. Will see to extend it soon for PG as well.

— Reply to this email directly, view it on GitHubhttps://github.com/borisdj/EFCore.BulkExtensions/issues/791#issuecomment-1099590343, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AFGVONBWWZV4NLT6A43ESNDVFB5ELANCNFSM5SWBWRSA. You are receiving this because you authored the thread.Message ID: @.***>

[borisdj]

Cleaning now some remaining issues, can you post here that fix you did to check it out. PropertiesToInclude and PropertiesToExclude seem to work now when tested with latest version 7.0.4. Also PropertiesToIncludeOnUpdate / PropertiesToExcludeOnUpdate give expected result.

Only PropertiesToIncludeOnCompare / PropertiesToExcludeOnCompare are not supported. Not sure if this feature is feasible on PG, related info: SE_updating-only-changed

[dagtveit]

its to long ago, cant remember but i asume i pushed it in this fork https://github.com/dagtveit/EFCore.BulkExtensions

[borisdj]

No commits there regarding this. I can't reproduce issues for first 4 configs, and last 2 are not doable on PG (have added that note in the ReadMe). Will close the issue now, if you find any bug you can reopen this or make a new one.