Wrong id generation with Postgres

Question

I am making a web app and as database provider I am using postgresql and Ef Core as ORM. I have two classes:

public class Price
{
    public int PriceId { get; set; }
    public DateTime TimeStamp { get; set; }
    public double Value { get; set; }
    public int CompanyId { get; set; }

    public virtual Company Company { get; set; }
}


public class Company
    {
        public Company()
        {
            Prices = new HashSet<Price>();
        }

        public int CompanyId { get; set; }
        public string Acronym { get; set; }
        public string FullName { get; set; }

        public virtual ICollection<Price> Prices { get; set; }
    }

My issue is that whenever I am trying to add a few records for first company:

var company = Context.Companies.FirstOrDefault(c => c.CompanyId == 1);
company.Prices.Add(new Price {Value = 123.45, TimeStamp = DateTime.Now});

and then for another

var company = Context.Companies.FirstOrDefault(c => c.CompanyId == 2);
company.Prices.Add(new Price {Value = 123.45, TimeStamp = DateTime.Now});

instead of:

CompanyId | PriceId | TimeStamp | Value
-------------------------------------------
1 | 1 | foo | bar
1 | 2 | foo | bar
2 | 1 | foo | bar
2 | 2 | foo | bar

I get:

CompanyId | PriceId | TimeStamp | Value
-------------------------------------------
1 | 1 | foo | bar
1 | 2 | foo | bar
2 | 3 | foo | bar
2 | 4 | foo | bar

My Pgadmin 4 configuration:

Companies table - CompanyId configuration

Prices table - CompanyId and PriceId configuration

That’s expected. The IDs don’t relate in any way to companies so they have to be globally unique. Is there a specific reason you want them to be different? It might be possible with a combination key or custom logic, but usually there’s no need for specific IDs — Sami Kuhmonen, Oct 31 '21 at 15:25
I would like CompanyId and PriceId to form a composite key. I don't know if I have managed to do that so I have placed screenshots of config. I must admit that such enumeration would be really helpful in terms of clarity. I want to parse informations about price from an external API and avoiding situation where all PriceId's are different would be really nice. I woule avoid big unreadable numbers and have nice, easy to read, meaningful PriceId's. — Artur, Oct 31 '21 at 15:29
Such considerations shouldn't play a role in data modelling. Only sound normalization and proper relationships. `PriceId` is a so-called surrogate key and its value shouldn't matter. Even if Price would need to have an [*identifying* relationship](https://stackoverflow.com/q/762937/861716) to Company, `PriceId` doesn't have to be incrementing within `CompanyId` just for the sake of readability. It's also much harder to realize this pattern. — Gert Arnold, Oct 31 '21 at 16:16
Ok, I understand. I must admit that after second thought, incrementing another column just for the sake of readibility sound rather pointless. Thank you for the relatioships thread. I will get read it all of it. Thank you for your precious time, gentlemans :) — Artur, Oct 31 '21 at 16:34

Serge · Answer 1 · 2021-10-31T17:05:07.857

You have a correct result. PriceId should be incrementing, since it is an autoincrementing field. But are using a wrong algorithm to add new items. This algoiritm is usually used if you need to add a new Company and new Prices together. In this case it will automatically assign a new CompanyId to Price. But in your case you dont need this code at all, since it only makes an extra trip to db server and affects performance and network traffic. Remove this code

 var company =Context.Companies.FirstOrDefault(c => c.CompanyId ==1);
//and
var company = Context.Companies.FirstOrDefault(c =>public c.CompanyId ==2);

would be enough just this

Context.Prices.Add(new Price { CompanyId=1, Value = 123.45, TimeStampValue = DateTime.Now});
Context.Prices.Add(new Price { CompanyId=2, Value = 123.45, TimeStampValue = DateTime.Now});

or maybe better

Context.Prices.AddRange( new Price[]
{ 
new Price {CompanyId=1, Value=123.45, TimeStamp= DateTime.Now},
new Price {CompanyId=2, Value=123.45, TimeStamp= DateTime.Now}
});

You have one to many relations but if you need many-to-many for some rasons, you will have to change Company to the List of Companies in your Price class

public class Price
{
    public int PriceId { get; set; }
    public DateTime TimeStamp { get; set; }
    public double Value { get; set; }
  
    public virtual ICollection<Company> Companies { get; set; }
}

if you are using ef core net5+ , ef will create an extra table for you, otherwise you will have to create it manually like this

public class CompanyPrice
{
public int CompanyId {get; set;}
public int PriceId {get; set;}
public virtual Company Company {get; set;}
public  virtual Price Price {get; set;}
}

Unfortunately, it did not help :/ Maybe it is postrges configuration problem? — Artur, Oct 31 '21 at 16:10
It's still the same. Values in PriceId are continuosly incrementing. — Artur, Oct 31 '21 at 16:19
Yes, it is a correct result. PriceId should be incrementing, since it is autoincrementing field. — Serge, Oct 31 '21 at 16:20
@GertArnold Thanks, I just realized it, that his question is not correct. He has one to many but wants many-to-many result for some reason. — Serge, Oct 31 '21 at 16:22
I wrote a comment above but I wanted to yhank you for your precious time again, gentlemans :) — Artur, Oct 31 '21 at 16:35

score 0 · Answer 2 · answered Oct 31 '21 at 17:25

I once had this happen to me as well. When using code first to create a datebase one of the values have to be the ID. As a possible solution, and i dont guarantee that it will work, move

public int CompanyId { get; set; }

to the top of your class and amend as follows:

> using System.ComponentModel.DataAnnotations;
>     public class Price
>     {
>         [Required]
>         public int CompanyId { get; set; }
>         public int PriceId { get; set; }
>         public DateTime TimeStamp { get; set; }
>         public double Value { get; set; }
>         
>     
>         public virtual Company Company { get; set; }
>     }

Hope it works out for you.

Wrong id generation with Postgres

2 Answers2