returning incorrect rows

[ms666]

Summary of the issue

returning incorrect rows in this specific case - or my error :(

Environment

Visual Studio 2017 Community .NET Core 2.0 PostgreSQL 10, Windows, 64bit Win 10 Pro 64bit

Example code/Steps to reproduce:

table:

 CREATE TABLE dict.payment_type_tr (
   id SERIAL,
   id_payment_type INTEGER,
   def BOOLEAN DEFAULT false,
   language VARCHAR(11),
   value VARCHAR(25),
   mod_date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
   mod_id_user BIGINT NOT NULL,
   CONSTRAINT payment_type_tr_pkey PRIMARY KEY(id),
 ) 
 WITH (oids = false);

 CREATE UNIQUE INDEX payment_type_tr_idx ON dict.payment_type_tr
   USING btree (id_payment_type, language COLLATE pg_catalog."default");

example data:

DBContext:

 modelBuilder.Entity<PaymentTypeTr>(entity =>
            {
                entity.ToTable("payment_type_tr", "dict");
                entity.HasIndex(e => new { e.IdPaymentType, e.Language }).HasName("payment_type_tr_idx").IsUnique();
                entity.Property(e => e.Id).HasColumnName("id").HasDefaultValueSql("nextval('dict.payment_type_tr_id_seq'::regclass)");
                entity.Property(e => e.Def).HasColumnName("def");
                entity.Property(e => e.IdPaymentType).HasColumnName("id_payment_type");
                entity.Property(e => e.Language).HasColumnName("language");
                entity.Property(e => e.ModDate).HasColumnName("mod_date").HasColumnType("timestamptz");
                entity.Property(e => e.ModIdUser).HasColumnName("mod_id_user");
                entity.Property(e => e.Value).HasColumnName("value");
            });

Getting data:

public class TranslateHelper : ITranslateHelper, IRepository
    {       
        public async Task<T> GetTranslate<T>(DbSet<T> dbSet, int IdObject, string language) where T : class, ITranslate
        => await dbSet
                  .Where(u => u.IdMainObject == IdObject && u.Language.Equals(language))
                  .Cacheable()
                  .SingleOrDefaultAsync();

Output:

In this case, IdMainObject, IdObject point to the id_payment_type in the array Change of the second parameter (language) causes returning the correct line, changing the IdObject with the constant language returns one row all the time: first call:

IdObject = 1, language = en - the row returned with id 1 is correct

another with diffrent IdObject:

IdObject = 2, language = en => id 1 incorrect IdObject = 3, language = en => id 1 incorrect

and change language:

IdObject = 3, language = pl => id 6. correct

and again another calls with diffrent IdObject:

IdObject = 2, language = pl => id 6. incorrect IdObject = 1, language = pl => id 6. incorrect

I tried to modify the table - the main key was IdPaymentType and language but the effect was identical ...

But the downloading of lines immediately after the ID works fine

   public async Task<PaymentTypeTr> GetAsync(int id)
            => await _context.PaymentTypeTr
                        .Where(u => u.Id == id)
                        .Cacheable()
                        .FirstOrDefaultAsync();

[VahidN]

I created a sample based on your data and I couldn't reproduce your results. Give it a try.

[ms666]

I've added the TransactionHelper class - as in the description - and everything works fine. In the example code. In my project, it works properly with a direct call. Using the TransactionHelper class causes errors - I need to investigate it more carefully. Anyway, I have a mistake, your code is OK.

I apologize for the trouble and greetings

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related problems.