ASP.NET MVC + Dynamics NAV odata web services - how do I access related models?

Question

I'm building an asp.net mvc application using Dynamics NAV Odata web services. Evertyhing is working fine and I created a controller for Service Orders using Linq queries. Then I got to the next step: accessing related models, and I'm stuck.

Lets take an example using page 5900 - Service Order and page 5903 - Service Item Lines:

Getting Service Orders or any other single model works great:

var query = from c in nav.ServiceOrder 
            select c;

But accessing related data fails:

var query = nav.ServiceOrder
            .Expand(x => x.ServiceOrderServItemLines)
            .Where(x => x.No == "SO000008");

I can access ServiceOrderServItemLines with the following url:

/DynamicsNAV71/OData/Company('the company')/ServiceOrder(Document_Type='Order',No='SO000008')/ServiceOrderServItemLines

But using expand does not seem to work.

Im not sure what the problem is. Are there no relations between the models?

If so, is there a way for me to add my own models with relations, and connect them to the odata service?

Or is it just a matter of expand not being supported in the service?

Any input would be much appreciated.

Answer 1

It seems that your serviceOrder has two keys. one is the Document_Type and one is the No. So please try

var query = nav.ServiceOrder
    .Expand(x => x.ServiceOrderServItemLines)
    .Where(x => x.Document_Type == "Order" && x.No == "SO000008");

If it doesn't solve your problem, Could you please provide the url sent by the expand query?

Answer 2

So after beating my head against this for some time I think I have an answer. I'll post my findings here, and hopefully it will save someone some trouble in the future.

Dynamics NAV (2013 R2) have relationships between header and list items as described here: http://blogs.msdn.com/b/freddyk/archive/2009/05/28/handling-sales-orders-from-page-based-web-services-in-nav-2009sp1-and-rtm.aspx

In my case I want to create and access Service Item Lines for a Service Order.

Using SOAP to create Service Orders (Service Header table) and Service Item Lines can be done like this:

    static void Main(string[] args)
    {
        ServiceOrder_Binding ctx = new ServiceOrder_Binding();
        ctx.UseDefaultCredentials = true;

        //Create a new Service Order
        ServiceOrder so = NewSo(ctx);

        //Add a couple of Service Item Lines to the Service Order
        for (int i = 0; i < 5; i++)
            NewSil(ctx, so.No);
    }

    private static ServiceOrder NewSo(ServiceOrder_Binding ctx)
    {
        ServiceOrder so = new ServiceOrder();
        so.Customer_No = "50000";
        so.Description = "New Service Order";
        ctx.Create(ref so);
        return so;
    }

    private static void NewSil(ServiceOrder_Binding ctx, string documentNo)
    {
        ServiceOrder so = ctx.Read(documentNo);
        List<Service_Order_Line> SilList = so.ServItemLines.ToList();
        Service_Order_Line Sil = new Service_Order_Line();
        Sil.ServiceItemNo = "20";
        Sil.Description = "New Service Item Line";
        SilList.Add(Sil);
        so.ServItemLines = SilList.ToArray();
        ctx.Update(ref so);
    }

Using Odata to read Service Orders (Service Header table) and Service Item Lines can be done like this:

    static void Main(string[] args)
    {
        NAV ctx = new NAV(new Uri("http://localhost:7048/DynamicsNAV71/OData/Company('CRONUS Sverige AB')"));
        ctx.UseDefaultCredentials = true;

        //Eager loading - DOES NOT WORK!
        var so = from s in ctx.ServiceOrder.Expand("ServiceOrderServItemLines")
                 where s.No == "SO000016"
                 select s;

        //Lazy loading - WORKS!
        var so2 = from s in ctx.ServiceOrder
                 where s.No == "SO000016"
                 select s;

        ctx.LoadProperty(so2.First(), "ServiceOrderServItemLines");
    }

Notice that lazy loading works, but eager loading doesn't.

For other related data, like Service Items for Service Item Lines, there doesn't seem to be any relationships. I will return with an update if I find something else, but what I ended up doing for now is passing related items in the ViewBag like this:

In the Controller Action:

    var service_items = from s in ctx.ServiceItemList
                        where s.Customer_No.Equals(customerNo)
                        select s;

    var serviceItemList = service_items.ToList();

    ViewBag.serviceItemList = new SelectList(serviceItemList, "No", "Description");

In the View:

    @Html.DropDownListFor(model => model.Service_Item_No, (IEnumerable<SelectListItem>)ViewBag.serviceItemList)

Hope this helps someone who like me is new to working with Dynamics NAV and Web Services :).