I'm having an issue with a V3 OData service in that expanding more than 1 level returns a number of different errors.

This works: taskapi/wh3/odata/taskdata/Requisition( 440318 )?$expand=FreightCarrier

This does not work: taskapi/wh3/odata/taskdata/Requisition( 440318 )?$expand=FreightCarrier,FreightCarrier/Carrier

Returns: A nested query relies on a correlation filter which refers to the field 'FreightCarrierNumber', however this field wasn't found in the projection of the entity.

However this does work: taskapi/wh3/odata/taskdata/FreightCarrier( 5 )?$expand=Carrier

Which verifies the navigators are correct.

Another example which works: taskapi/wh3/odata/taskdata/Requisition( 440318 )?$expand=RequisitionLines

But a second level expansion fails: taskapi/wh3/odata/taskdataRequisition( 440318 )?$expand=RequisitionLines,RequisitionLines/Product

This time something really strange is happening. In the profiler it shows that the fetch is attempting to load ALL RequisitionLines and ALL products but the query takes too long and some strange OData type conversion error is thrown.

Forgive me if i've done something wrong but I believe the last query above should only fetch the child requisition lines of parent requisition ( 440318 ) and child products of parent requisition lines?

Just to add, I managed to get the final query to return data after adjusting the expand:

taskapi/wh3/odata/taskdata/Requisition( 440318 )?$expand=RequisitionLines/Product

It returned the correct data but the profiler still shows it fetched all RequisitionLines and all Products but returned only the correct child entities. The query execution time was in the region of 10 seconds.

Your last URI is the correct format according to http://www.odata.org/documentation/odata-v2-documentation/uri-conventions/#46_Expand_System_Query_Option_expand

You use comma (,) if you want to add branches, like:

http://.../OData.svc/Order(1009)?$expand=Details/Product,Customer

Now back to your SQL, it might be limiting the resulset in-memory in cases where the query can't emit DISTINCT, like when the fetched table has Text/Image columns.

What if you use normal LLBLGenAPI/QuerySpec/Linq2LLBL queries to return such graph using PrefetchPaths? Does it run the same SQL?

What is your LLBLGen version and runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717 )

I believe my expand usage is correct as per the spec. If i'm including multiple related entities then they will be separated by commas. For a nested (branched) fetch the navigator properties are separated with a /

As for local querying the entity project is well established and in production for about 4 years. I've recently created a WCF Data Service from the same project (re-compiled with V4.1). In any case i've created a Service Operation and this works fine when I step through and check the related properties.

    [WebGet]
    public IQueryable<RequisitionEntity> GetShippedOrder(int requisitionNumber)
    {
        var q = (from o in CurrentDataSource.Requisition
            where o.RequisitionNumber == requisitionNumber
            select o).WithPath(
                new PathEdge<AccountEntity>(RequisitionEntity.PrefetchPathAccount),
                new PathEdge<AccountCustomerEntity>(RequisitionEntity.PrefetchPathAccountCustomer),
                new PathEdge<FreightCarrierEntity>(RequisitionEntity.PrefetchPathFreightCarrier,
                    new PathEdge<CarrierEntity>(FreightCarrierEntity.PrefetchPathCarrier)),
                new PathEdge<RequisitionDeliveryDetailEntity>(RequisitionEntity.PrefetchPathRequisitionDeliveryDetails));

        return q;
    }

This works fine:

GetShippedOrder?requisitionNumber=440318&$expand=Account,AccountCustomer,FreightCarrier&$format=json

but this fails:

GetShippedOrder?requisitionNumber=440318&$expand=FreightCarrier/Carrier&$format=json

OData Error: A nested query relies on a correlation filter which refers to the field 'FreightCarrierNumber', however this field wasn't found in the projection of the entity.

FreightCarrierNumber is PK/FK on FreightCarrier / Requisition.

LLBLGen v4.1 WCF Data Services v5.6.0 .NET 4.5

Could you enable linq tracing at level 3 with:

<add name="LinqExpressionHandler" value="3" />

(not 4!, that will give a truckload of lines in the trace). This trace will give the expression tree in text. It might be necessary to enable a text tracer in the web.config file to be able to obtain the trace info, unless you can run the service in the debugger, it then should give the trace in the output window in vs.net

Though it looks like the odata service from MS creates a linq query which isn't what the provider expects/can handle. The thing is that MS deprecated the expand provider and now uses other logic, which might be the source for this. I'll see if I can reproduce it here too with our own setup.

(edit) If I try:

http://localhost:3931/NorthwindService.svc/Customer%28%27ALFKI%27%29?$expand=Orders/OrderDetails&$format=json

I get proper data. And if I try:

http://localhost:3931/NorthwindService.svc/Customer%28%27ALFKI%27%29?$expand=Orders/OrderDetails/Products&$format=json

It works too. I recon you use inheritance in the entities?

The thing is that the odata service from MS doesn't use the expand provider anymore, it now inlines the selection, using select N+1, unless the linq provider is clever enough. Luckily we did implement nested query fetching similar to prefetch paths, so this works, but there's no prefetch path injection anymore like there was with the expand provider in the older odata services from MS (the one in the .net framework for example)

Example of expression created by the odata service:

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Northwind.Adapter.EntityClasses.CustomerEntity])
    .Where(element => (element.CustomerId == "ALFKI"))
    .Select(p => new ExpandedWrapper`2() 
                {
                    ExpandedElement = p, 
                    Description = "Orders", 
                    ReferenceDescription = "", 
                    ProjectedProperty0 = p.Orders
                                            .Select(p => new ExpandedWrapper`2() 
                                                        {
                                                            ExpandedElement = p, 
                                                            Description = "OrderDetails", 
                                                            ReferenceDescription = "", 
                                                            ProjectedProperty0 = p.OrderDetails
                                                                        .Select(p => new ExpandedWrapper`2() 
                                                                                {
                                                                                    ExpandedElement = p, 
                                                                                    Description = "Products", 
                                                                                    ReferenceDescription = "Products", 
                                                                                    ProjectedProperty0 = p.Products
                                                                                })
                                                        })
                })

This clearly doesn't use any prefetch paths but uses lame EF compatible select N+1 stuff. What I do notice in the queries executed is that there's no parent filtering, so no correlation predicates are used...

As the expand provider is deprecated, there's no way to specify the paths in our efficient system, it will rely on nested queries in the linq expression which are more limited (inheritance might fail, it requires correlation predicates).

Is there a way to use a webget method instead or do you have the requirement to specify the expands on the url?

It also might be we have a bug somewhere in the linq provider, though that then should be reproducible with a linq query formulated manually as well, as the issue is really what is passed to the linq provider as query... I will look into why the queries don't use parent filtering as they should do that.

There is an issue it seems with when I have 1 level and 2 levels deep of entity fetches nested in a projection: 1 level deep gives proper filtering on parents, 2 levels deep does not. Will check this out, though that's not what you ran into, which is likely inheritance related.

(edit) reproducible with:

LinqMetaData metaData = new LinqMetaData(adapter);
var q = from c in metaData.Customer
        where c.CustomerId == "ALFKI"
        select new
        {
            c.CustomerId,
            OrderData = c.Orders.Select(o => new {  o.OrderId, OrderDetailData = o.OrderDetails})
        };

which doesn't filter on parent. Again, NOT the issue you ran into, an issue I ran into while testing this.

(edit) found a repro for YOUR problem a m:n relationship:

http://localhost:3931/NorthwindService.svc/Order%2810254%29?$expand=Customers/Employees&$format=json

This fails with the same error you got: A nested query relies on a correlation filter which refers to the field 'CustomerId', however this field wasn't found in the projection of the entity.

Expression is:

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Northwind.Adapter.EntityClasses.OrderEntity])
    .Where(element => (element.OrderId == 10254))
    .Select(p => new ExpandedWrapper`2() 
            {
                ExpandedElement = p, 
                Description = "Customers", 
                ReferenceDescription = "Customers", 
                ProjectedProperty0 = new ExpandedWrapper`2() 
                {
                    ExpandedElement = p.Customers, 
                    Description = "Employees", 
                    ReferenceDescription = "", 
                    ProjectedProperty0 = p.Customers.Employees
                }
            })

Which runs into the limitation of the nested queries: it can't relate a m:n related element with the parent in this case as the intermediate element isn't there.

This is a hard problem which I don't think is solveable. THe thing is that I can't transform the tree with a visitor to use prefetch paths, as the layout of the resultset is fixed: the service code expects data to be in this particular shape.

Hi Frans, Thanks for your assistance in this matter. Looks like a lot to get through. Here's a trace for the query:

/Requisition(440318)?$expand=FreightCarrier/Carrier&$format=json

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Next.Logistics.Api.Task.EntityClasses.RequisitionEntity]).Where(element => (element.RequisitionNumber == 440318)).Select(p => new ExpandedWrapper`2() {ExpandedElement = p, Description = "FreightCarrier", ReferenceDescription = "FreightCarrier", ProjectedProperty0 = new ExpandedWrapper`2() {ExpandedElement = p.FreightCarrier, Description = "Carrier", ReferenceDescription = "Carrier", ProjectedProperty0 = p.FreightCarrier.Carrier}})
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.dll
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException' occurred in Microsoft.Data.Services.dll
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException' occurred in Microsoft.Data.Services.dll

The second query does return but fetches all Products and all RequisitionLines:

/Requisition(440318)?$expand=RequisitionLines/Product&$format=json

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Next.Logistics.Api.Task.EntityClasses.RequisitionEntity]).Where(element => (element.RequisitionNumber == 440318)).Select(p => new ExpandedWrapper`2() {ExpandedElement = p, Description = "RequisitionLines", ReferenceDescription = "", ProjectedProperty0 = p.RequisitionLines.Select(p => new ExpandedWrapper`2() {ExpandedElement = p, Description = "Product", ReferenceDescription = "Product", ProjectedProperty0 = p.Product})})

I tried to create a service method as per earlier post but it seems than only the root entity is returned to the client. You still need to use expand to get the related entities. So I don't see a way of getting a full graph set back to the client without using expand.

In the short-term I could normalise the entities but I fear the OData service would just turn in to an RPC service with a bunch of non-reusable GetThis GetThat methods, which defeats the object.

I've attached part of the model for consideration. If you wish I could provide the DB and my service project?

Thank you.

Ok, some small success for now. Using a Service Operation I can at least now return a full graph set with expand.

        [WebGet]
        [SingleResult]
        public IQueryable<RequisitionEntity> GetShippedOrder(int requisitionNumber)
        {
            var q = (from o in CurrentDataSource.Requisition
                where o.RequisitionNumber == requisitionNumber
                select o).WithPath(
                    new PathEdge<AccountEntity>(RequisitionEntity.PrefetchPathAccount),
                    new PathEdge<AccountCustomerEntity>(RequisitionEntity.PrefetchPathAccountCustomer),
                    new PathEdge<FreightCarrierEntity>(RequisitionEntity.PrefetchPathFreightCarrier,
                        new PathEdge<CarrierEntity>(FreightCarrierEntity.PrefetchPathCarrier)),
                    new PathEdge<RequisitionLineEntity>(RequisitionEntity.PrefetchPathRequisitionLines,
                        new PathEdge<ProductEntity>(RequisitionLineEntity.PrefetchPathProduct)),
                    new PathEdge<RequisitionDeliveryDetailEntity>(
                        RequisitionEntity.PrefetchPathRequisitionDeliveryDetails));

            return q.ToList().AsQueryable();
        }

invoked using:

GetShippedOrder?requisitionNumber=440318&$expand=Account,AccountCustomer,FreightCarrier/Carrier,RequisitionLines/Product,RequisitionDeliveryDetails&$format=json

The key was to force the fetch to happen with ToList() otherwise the prefetches would be ignored and the linq/wcf query builder would take over.

caffreys wrote:

Hi Frans, Thanks for your assistance in this matter. Looks like a lot to get through. Here's a trace for the query:

/Requisition(440318)?$expand=FreightCarrier/Carrier&$format=json

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Next.Logistics.Api.Task.EntityClasses.RequisitionEntity]).Where(element => (element.RequisitionNumber == 440318)).Select(p => new ExpandedWrapper`2() {ExpandedElement = p, Description = "FreightCarrier", ReferenceDescription = "FreightCarrier", ProjectedProperty0 = new ExpandedWrapper`2() {ExpandedElement = p.FreightCarrier, Description = "Carrier", ReferenceDescription = "Carrier", ProjectedProperty0 = p.FreightCarrier.Carrier}})
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.dll
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException' occurred in Microsoft.Data.Services.dll
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException' occurred in Microsoft.Data.Services.dll

The second query does return but fetches all Products and all RequisitionLines:

/Requisition(440318)?$expand=RequisitionLines/Product&$format=json

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Next.Logistics.Api.Task.EntityClasses.RequisitionEntity]).Where(element => (element.RequisitionNumber == 440318)).Select(p => new ExpandedWrapper`2() {ExpandedElement = p, Description = "RequisitionLines", ReferenceDescription = "", ProjectedProperty0 = p.RequisitionLines.Select(p => new ExpandedWrapper`2() {ExpandedElement = p, Description = "Product", ReferenceDescription = "Product", ProjectedProperty0 = p.Product})})

Yes, that's the 'other' issue I was talking about. I saw that as well when testing your scenario. We'll look into that. It's a different issue than the one you ran into though.

I tried to create a service method as per earlier post but it seems than only the root entity is returned to the client. You still need to use expand to get the related entities. So I don't see a way of getting a full graph set back to the client without using expand.

Glad you eventually got that working It's indeed not ideal, it's however for this particular case required.

In the short-term I could normalise the entities but I fear the OData service would just turn in to an RPC service with a bunch of non-reusable GetThis GetThat methods, which defeats the object.

It's actually the way to do it, see WebApi, which focuses also more on methods than an open api which can handle all queries. The thing is that if you have a neat API the client can talk against, you control what's going over the wire. With an open api you don't, you rely on what the client does, plus you also have to do extra work to prevent unwanted requests from pulling more data than you want. Though I'm not sure what the use case for the service is, however your query already contains a lot of path edges, so it might be you fetch way more data than you need on the client (so a flat projection might work better, but again, not sure what your client is doing/needs)

The m:n navigator is tough to do. We did find a query which might cut it, but it's likely it fails if there are extra filters padded onto the element through service methods, but alas, it doesn't work now in all cases so it's progress We'll look into this as well to see whether we can accomplish this, and if not we unfortunately have to give up on m:n navigators in odata expand scenarios.

Fixed the filtering issue, now looking in the m:n issue and a small alias issue I ran into when doing 3-level deep nested queries on entities with a relation with self.

Thanks again for the excellent support. In the short-term i'll patch up with some ready made service methods. The main reason for going with odata is to abstract away our old ms access dbs and allow cross domain access to data. Most clients or apps will not talk directly to odata but instead via domain-specific services courtesy of servicestack or webapi. The data layer for these services will be odata which is why i'm hoping i can get close to full access for querying purposes.

So i roll out my odata service providing near 1:1 data access. The domain-specific services connect to the odata service and fetch and map onto clean DTOs which may get cached before returning to the client. If i need a richer UI then i can also go direct to odata. That's the theory anyway.

Once we've migrated to grown-up SQL we can probably optimise and remove the odata layers. But realistically that's a year away minimum.

Ok, sounds indeed that odata could help there, though keep in mind that you likely want to trim down the access point quite a bit as it offers a route to your database you might not want to have exposed.

I have attached the fixed ormsupportclasses dll which fixes the filtering issue on 2+ levels deep.

The other issue, the m:n issue (which is also popping up when you have a m:1 relation) is not fixable. The reason is that the entity to merge doesn't contain a value which is related to the parent entity (Order - Employee - Manager. Employee and Manager are stored in the projection of Order, but Manager isn't related to Order in any way). I could add the OrderId in the query which returns the Manager, but that field is gone when the entity is materialized: employee doesn't have an orderid field. This makes it not fixable.

In EF it 'works' because they create one big query with massive joins which returns 1 resultset from which they materialize the entire graph. Very inefficient, but for these queries it works (as in: it gives results).

I'll do some testing with the new dll and see how it goes. Regarding the m:n I initially thought that there wouldn't be too many of these, but the obvious one for me would be OrderLine with Product. So that wouldn't work? Typically the parent would be Order and so I would retrieve Order with its lines and corresponding products. But as we've discovered the prefetches would result in a lot of data so this would be an argument for a normalised view. But then, for a given order, i'd wish to query the collection of order lines and include the product detail. This would be m:n and so would fail.

..or would it? I think you're saying it is only a problem where the parent has no relationship with the entity in question. So If I fetched order then it has no relation with product, but order line does so its ok?

Just trying to figure out where i'd come up with issues so I can work around them.

caffreys wrote:

I'll do some testing with the new dll and see how it goes. Regarding the m:n I initially thought that there wouldn't be too many of these, but the obvious one for me would be OrderLine with Product. So that wouldn't work? Typically the parent would be Order and so I would retrieve Order with its lines and corresponding products. But as we've discovered the prefetches would result in a lot of data so this would be an argument for a normalised view. But then, for a given order, i'd wish to query the collection of order lines and include the product detail. This would be m:n and so would fail.

..or would it? I think you're saying it is only a problem where the parent has no relationship with the entity in question. So If I fetched order then it has no relation with product, but order line does so its ok?

Just trying to figure out where i'd come up with issues so I can work around them.

The thing which fails is the expand operator to a m:n navigator. So the situation in which you want to fetch Orders and the related Products in a graph using a query on orders and an expand with Products. This thus means the data which is returned as a result of your query, not the data used by your query.

Meaning: if you want to retrieve all orders which contain a given product, you don't need expand, as you're interested in orders to be returned, the query itself is about Orders.Products.Where(p=>p.Id==something)

This is OK, the only thing that fails is the actual retrieval of m:n related entities.

Indeed, fetching (so specifying an expand for these) order - orderlines - product using expand should work, though, some m:1 expands also fail, namely 2 in a row. This is because they want to do a select on the '1' side which of course doesn't work, so they include both in 1 wrapper.

However if you don't use all data of all entities in the graph it might indeed be wiser to setup a projection with the data used.

Additionally, I don't know if you know this, but in case you're migrating away from access, it's rather easy to migrate your llblgen project to sqlserver: - migrate the access db to a sqlserver catalog - load the access project into llblgen pro (v3 or higher) - right-click relational model data and select 'Add relational model data from a database' and walk the wizard, connect to sqlserver to read the catalog with the migrated data from access. - right-click 'Entities' and select 'Auto map unmapped entities'. This should map all entities to the tables from sqlserver. - Verify the mappings by clicking validate. Some errors might pop up due to different types. - if satisfied, generate code. You'll now get one DB generic codebase (entities) and two code bases for dbspecific code: one for access and one for sql server.

This way you can connect to both access and sqlserver and which could make migrating your existing code (which should be db agnostic due to the orm) to sql server easier.

Blast from the past: we ran into this issue with a new feature of v5 and decided to have another go at it, namely the nested m:n fetch and why it breaks.

It turned out to be rather easy as we found an issue with correlation filter construction which caused the problem. We fixed this issue in the next build of v4.2.

Queries which now work:

var q = metaData.Order.Where(o => o.OrderId == 10254).Select(o =>
                            new Customer
                            {
                                CustId = o.Customer.CustomerId,
                                CompanyNme = o.Customer.CompanyName,
                                Employees = o.Customer.EmployeeCollectionViaOrder.Select(e => new Employee()
                                {
                                    EmployeeId = e.EmployeeId,
                                    FirstName = e.FirstName,
                                    LastName = e.LastName
                                })
                            });

and

LinqMetaData metaData = new LinqMetaData(adapter);
var q = from od in metaData.OrderDetail
        where od.OrderId == 10254
        select new
        {
            Employees = od.Order.Customer.EmployeeCollectionViaOrder.Select(e=>new { EmpId = e.EmployeeId}).ToList()
        };
var r = q.ToList();

I haven't tried it yet in your particular situation but reading this thread it looks like it's the problem the queries above had as well.

(edit). Ok, this doesn't fix your particular OData problem, as OData wraps related elements and fetches entity queries. A projected set query does work.

I'll look to see whether I can fix it for entity queries as well. As illustration:

// succeeds:

LinqMetaData metaData = new LinqMetaData(adapter);
var q = metaData.Order
        .Where(element => (element.OrderId == 10254))
        .Select(p => new
                {
                    ExpandedElement = p,
                    Description = "Customers",
                    ReferenceDescription = "Customers",
                    ProjectedProperty0 = new
                    {
                        ExpandedElement = p.Customer,
                        Description = "Employees",
                        ReferenceDescription = "",
                        ProjectedProperty0 = p.Customer.EmployeeCollectionViaOrder.Select(e => new { EmpId = e.EmployeeId}).ToList()
                    }
                });
var r = q.ToList();

// fails

LinqMetaData metaData = new LinqMetaData(adapter);
var q = metaData.Order
        .Where(element => (element.OrderId == 10254))
        .Select(p => new
                {
                    ExpandedElement = p,
                    Description = "Customers",
                    ReferenceDescription = "Customers",
                    ProjectedProperty0 = new
                    {
                        ExpandedElement = p.Customer,
                        Description = "Employees",
                        ReferenceDescription = "",
                        ProjectedProperty0 = p.Customer.EmployeeCollectionViaOrder
                    }
                });

Fails with entity fetch because it has to add the field of the correlation predicate to the projection and entity fetches don't have that ability currently, so it can't adjust the fetch, and therefore it fails. It's progress, but still something that's not completely fixed yet. It's not easy to adjust in v4 as the query pipeline has to be adjusted to make this work, so we can't fix it in v4 without making changes in how entities are fetched.