Asp.Net WebApi OData v4 - Saving Entity along with related entities

Question

Consider following Entities

public class Supplier
{
    public int Id { get; set; }
    public string Name { get; set; }

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

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Category { get; set; }  

    public int? SupplierId { get; set; }
    public virtual Supplier Supplier { get; set; }
}

As per the code above a Supplier entity can have zero or more products. In above entities the ID's are auto-generated by Entity Framework.

I'm using Odata V4 Client code generator.

Client Code:

Supplier s = new Supplier{...}
OdataContext.AddToSuppliers(s);

Product p = new Product{...}
p.SupplierId = s.Id;
OdataContext.AddToProduct (p);

OdataContext.SaveChanges();

Supplier Controller:

public async Task<IHttpActionResult> Post(Supplier supplier)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    db.Supplier.Add(supplier);
    await db.SaveChangesAsync();
    return Created(supplier);
}

Product Controller:

public async Task<IHttpActionResult> Post(Product product)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    db.Products.Add(product);
    await db.SaveChangesAsync();
    return Created(product);
}

When I saving the changes I'm getting an error message:

The INSERT statement conflicted with the FOREIGN KEY constraint.

The reason for the error is the Product entity doesn't have the SupplierId yet as it Database generated key.

So how can I add SupplierId to the Product entity while saving the record? Can anyone help me to solve this issue?

Answer 1

OK, thanks for clarifying. Assuming you are using SQL Server, your Int Id columns will be identity columns by convention. A Product can have 0 or 1 supplier so approach from that perspective.

To add a new product to a new supplier you can do:

Supplier s = new Supplier
{
    Name = "mySupplier"
}

Product p = new Product
{
    Name = "myname",
    Price = 123.45,
    Category = "myCategory",
    Supplier = s
}

OdataContext.Products.Add(p);
OdataContext.SaveChanges();

To add an existing supplier to a product:

// select a supplierId or search by name
var supplier = OdataContext.Suppliers.FirstOrDefault(s => s.Name == nameToFind);
if (supplier == null) // Handle no match

Product p = new Product
{
    Name = "myname",
    Price = 123.45,
    Category = "myCategory",
    SupplierId = supplier.Id
}

OdataContext.Products.Add(p);
OdataContext.SaveChanges();

Answer 2

With OData V4, You may try the deep insert approach. Deep Insert allows creating a tree of related entities in one request. http://docs.oasis-open.org/odata/odata/v4.0/os/part1-protocol/odata-v4.0-os-part1-protocol.html#_Toc372793718

Answer 3

I think OData Client won't support the deep insert yet. So another option is to use WebClient and JSON serializer for request body, like below. Both parent and child entities will be created in single http request and foreign key will get updated accordingly -

var product = new ProductService.Models.Product()
        {
            Name = "My Car",
            Category = "Auto",
            Price = 4.85M
        };

        var supplier = new ProductService.Models.Supplier()
        {
            Name = "My Name"
        };

        //Add product to supplier
        supplier.Products.Add(product);

        WebClient client = new WebClient();
        client.BaseAddress = "http://example.com/OData";
        client.Headers.Add("Content-Type", "application/json");

        //Serialize supplier object
        var serializer = new JavaScriptSerializer();
        var serializedResult = serializer.Serialize(supplier);

        //Call the service
        var result = client.UploadString("http://example.com/OData/Suppliers", serializedResult.ToString());

Post Action in Suppliers Controller -

public async Task<IHttpActionResult> Post(Supplier supplier)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }
        db.Suppliers.Add(supplier);
        await db.SaveChangesAsync();
        return Created(supplier);
    }