C#•2y ago

✅ Get an Id from a table and save it to other table

Hello, I have a Customer table that serves as a masterlist for my customers. I also have a Orders table. Now that the Orders table has a CustomerId column, it is referred to as the Customer table. Initially, I had a GetAllActiveCustomers api that will collect all the customers and match the customer's name from the incoming orders if they match, I will obtain the customerId and save it to the Orders table. Is there any other approach for this? Any suggestions would be greatly appreciated.

18 Replies

Angius•2y ago

For the record, you can use `single backticks` to have inline code blocks in your message Also, do you use plain SQL? EF Core? Something else?

_vegabyte_OP•2y ago

Hi, thanks i'll edit my question Im using EF Core

Angius•2y ago

Okay, so... You have a Customers table, and an Orders table that has a foreign key and a navigation property to Customers to set up the relationship. And you want to get the names of all customers that currently have an active order? So in case of

Customers
id| name
1 | Bob
2 | Matthew
3 | Alice
4 | John
5 | Martha

Customers
id| name
1 | Bob
2 | Matthew
3 | Alice
4 | John
5 | Martha

Orders
id| customerId
1 | 2
2 | 1
3 | 5

Orders
id| customerId
1 | 2
2 | 1
3 | 5

you want to get Matthew, Bob, Martha?

_vegabyte_OP•2y ago

For example Customer1 orders a item. I need to validate if that customer is available on my Customer table. If it is available I save save the order including the Id of that customer in the Orders table.

Angius•2y ago

Well, check if the customer exists with .AnyAsync() and if they do, add the order

_vegabyte_OP•2y ago

What about the id? How do I get it. Since orders have only Customer name?

Angius•2y ago

Why do orders only have customer name and not the ID? Relationships are established with IDs And, in case of EF, nav properties

class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}
class Order
{
    public int Id { get; set; }
    public Customer Customer { get; set; }
    public int CustomerId { get; set; }
}

class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}
class Order
{
    public int Id { get; set; }
    public Customer Customer { get; set; }
    public int CustomerId { get; set; }
}

will create

CREATE TABLE Customers (
    Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL
)
CREATE TABLE Orders (
    Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT NOT NULL,
    CONSTRAINT FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
)

CREATE TABLE Customers (
    Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL
)
CREATE TABLE Orders (
    Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT NOT NULL,
    CONSTRAINT FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
)

Or something of the sort

_vegabyte_OP•2y ago

If customer order a Item. I will get CustomerName, ItemName, Location. etc. So it doesnt have a CustomerId in the order itself. But In my Orders table I have a CustomerId.

phaseshift•2y ago

What if multiple customers have same name...

Angius•2y ago

You're talking about getting the resulting data?

phaseshift•2y ago

That's why the order needs customer id

Angius•2y ago

What you return from the API? Because you seem to refer to two different things as order The table, and whatever it is that has no customer ID but has their name Is the issue that you get the customer name as a parameter to your API, and you need to create an order for that user? In that case, yeah, what if two customers have the same name?

_vegabyte_OP•2y ago

Okay so let me explain more.

order:{
      customerName : "customer1",
      item: "item1",
}

order:{
      customerName : "customer1",
      item: "item1",
}

So this is the sample body i get from a request. As you can see it doesn't have any customerId in it. So I made a

GetAllCustomer

GetAllCustomer

api it return the customer info including the Id, I matched the name from the body into the response of the API. If it matched I will get the Id and save to the Orders table.

order:{
    customerId: 1
    item: "item1"
}

order:{
    customerId: 1
    item: "item1"
}

Orders are usually coming from Excel files. Customers are usually using their Names since they dont know their customerId, And for the Customer name. It is impossible that we will have a same customer name because I have a Customer table. I have also validation for adding customer if it is already exist or not.

phaseshift•2y ago

So what happens when John Smith (number 2) wants to join? 🤔 You can continue with unique name assumption if you want. Its completely unrealistic, though

_vegabyte_OP•2y ago

Well john smith will be add to my Customer table not all user can order. I have only certain customers Like Supplier and Customer

phaseshift•2y ago

You just said before, it's not possible for two John Smiths. Now you say John Smith (2nd) will be added. Which is it?

_vegabyte_OP•2y ago

Oh sorry for the confusion, there will be no John Smith number 2. Customer are shops. If ever it has one more John Smith it will have 2 in the name like Branch 2 (Location)

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

✅ Get an Id from a table and save it to other table