Wrong id generation with Postgres-CodePudding

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

CodePudding user response：

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;}
}

CodePudding user response：

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.