CODEWITHSUNDEEP
petapoco
Jon
Jon

Reputation: 40032

Inserting to PetaPoco with entity relationships

I have the following POCO's:

[PetaPoco.TableName("Customer")]
    [PetaPoco.PrimaryKey("ID")]
    public class User
    {
        [Required(ErrorMessage = "Please enter a Forename")]
        [PetaPoco.Column("Name")]
        public string Forename { get; set; }

    }

    public class Product
    {
        public int Id { get { return 1; } }
        public string Name { get { return "TBMaster Licence"; } }
        public decimal Price { get { return 358.00M; } }  //Ex VAT

        [Required(ErrorMessage = "Please enter a valid Machine ID")]
        public string MachineId { get; set; }

        public int ExpiryElement { get; set; }

        public DateTime ExpiryDate
        {
            get
            {
                if (ExpiryElement == 0)
                {
                    return new DateTime(1900, 1, 1);
                }
                else if (ExpiryElement == 1)
                {
                    return DateTime.Now.AddYears(1);
                }
                else if (ExpiryElement == 2)
                {
                    return DateTime.Now.AddYears(2);
                }
                else if (ExpiryElement == 3)
                {
                    return DateTime.MaxValue;
                }
                else
                {
                    return new DateTime(1900, 1, 1);
                }
            }
        }

        public bool BloodTestEnabled { get; set; }
        public string LicenceKey { get; set; }

        public SelectList LicenceOptions
        {
            get;
            set;
        }
    }

    [PetaPoco.TableName("Order")]
    [PetaPoco.PrimaryKey("OrderID")]
    public class Order
    {
        [PetaPoco.Column("TxCode")]
        public string VendorTxCode { get; set; }

        public User WebsiteUser { get; set; }

        public List<Product> Products { get; set; }
    }

I have the below code that tries to do an insert in the respective tables:

        var user = new User();
        user.Forename = "fred";

        var product = new Product();
        product.ExpiryElement = 2;
        product.MachineId = "1234";

        var order = new Order();
        order.VendorTxCode = "1111";
        order.WebsiteUser = user;
        List<Product> prods = new List<Product>();
        prods.Add(product);
        order.Products = prods;


        var db = new PetaPoco.Database("TBMasterDB");

        db.Insert(user);
        db.Insert(order);

The insert on the user works fine but the insert on the order doesn't. How do you do an insert when you have this relationship in your POCO's? This is a simplified approach because where I am trying to do the inserts I only have the Order POCO so how can I insert into the user, order and products table?

Upvotes: 2

Views: 2637

Answers (2)

Typo Johnson
Typo Johnson

Reputation: 6044

I have a similar relationship in my app, ie; one to many. I would manually add the order record first so I have the order id. Then insert each product individually after setting order.productid. As for the database, if you haven't already set it up as such, it looks to me like you need an OrderProducts table that stores the products in an order. You probably don't need to store the whole product details in OrderProduct, just the product id, description and amount at the time of the order

Upvotes: 1

Jon
Jon

Reputation: 40032

After looking at Gareth Elms' blog post I see I can do it like so:

 db.Insert("Order", "OrderID", new { TxCode = order.VendorTxCode, CustomerID = order.WebsiteUser.ID });

Essentially you mapping again in the Insert method. Not very pretty but it does the trick. Hope there is a tidier way of doing this in the future

Upvotes: 3

Related Questions