Hi Frans,

Following our discussion in the General Chat forum (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=12680&StartAtMessage=0&#70687) I thought I'd have a go at implementing lambda-based prefetch paths.

Currently I have this working:

var query = metaData.Customers.WithPath(c => c.Orders)

It'd be really helpful if the LinqUtils class could be made public...there are some very useful methods in there which I'd like to be able to make use of.

Thanks

Cool Let's open the discussion in this thread. . I'm open for different approaches, as the current implementation isn't great eyecandy either, fully admitted. So new ideas, or overloads for methods which offer different approaches, I'm all ears

I'll make the Linq utils public in the next build.

Basically, this is the method I've implemented:

public static IQueryable<T> WithPath<T>(this IQueryable<T> query, params Expression<Func<T, object>>[] lambdas)

Which allows me to do this:

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    int staff = 20070166;
    var metaData = new LinqMetaData(adapter).StaffAbsence;
    var results = metaData.Where(s => s.StaffRef == staff)
                .WithPath(
                    s => s.Category,
                    s => s.TakenByStaff,
                    s => s.ApprovedByStaff
                )
                .ToList();
}

And it seems to be working correctly (woo hoo!)

I haven't got support for filtering/sorting/subpaths yet...thats the next stage

Looks good!

But it gets nasty really quickly when you want to add filters etc. as you have to cram it all in one line while the graph can be multiple branches... So you need a new object, which can hold one node's information.

I don't see this as a replacement for PathEdge, but rather an alternative syntax for dealing with simple situations.

I now have support for single subpaths:

//q is a query on the Customers table
q.WithPath(c => c.Orders[0].OrderDetail)

Which prefetches orders and order details.

Having to specify the index in the Orders collection isn't the most elegant solution, but it works

Next stage: multiple subpaths

I now have multiple subpaths working like so:

//q is a query on the Customers table
q.WithPath(
    c => c.Orders[0].OrderDetail,
    c => c.Orders[0].Employee
);

... which prefetches Orders, each order's OrderDetails and each order's Employee.

With a bit more work I think it would be possible to get filtering working like this:

q.WithPath(c => c.Orders.Filtered(o => o.EmployeeId == 1)[0].OrderDetail);

What do you think?

Jez wrote:

I now have multiple subpaths working like so:

//q is a query on the Customers table
q.WithPath(
    c => c.Orders[0].OrderDetail,
    c => c.Orders[0].Employee
);

... which prefetches Orders, each order's OrderDetails and each order's Employee.

With a bit more work I think it would be possible to get filtering working like this:

q.WithPath(c => c.Orders.Filtered(o => o.EmployeeId == 1)[0].OrderDetail);

What do you think?

It's logical that to have orderdetails fetched, you need orders. So: c => c.Orders[0].OrderDetail suggests: 'fetching orders and orderdetail'. However as you also said: the indexer is a bit awkward. It can also suggest to fetch for the 1st order the orderdetails. It's cumbersome to come up with a proper api for this, so I don't blame you for anything, you did great work I think due to the nature of extension methods, it's easy to share code like you wrote with others through 3rd party downloads, if we decide not to include your proposed api syntaxis. The first proposed thing, like the 1st level below the entity, that looks good.

(totally not tested) -> q.WithPath(c=>c.Orders.WithPath(o=>o.OrderDetails)) ? Probably won't work due to the generics which aren't inferrable(?) through the method I think. So EntityCollection<T> also should get an extension method, but it then gets awkward as well, as that one will popup in normal code as well...

Otis wrote:

However as you also said: the indexer is a bit awkward. It can also suggest to fetch for the 1st order the orderdetails

Agreed. One of my colleagues mentioned the same thing I really don't like that indexer, but I can't think of a better way of doing it without sticking type-parameters all over the place.

Otis wrote:

due to the nature of extension methods, it's easy to share code like you wrote with others through 3rd party downloads, if we decide not to include your proposed api syntaxis.

Yep...thats one great thing about extension methods. If only there were extension properties too...

Even if you don't include a lambda-based prefetch syntax in the final release, its great that the framework is extensible enough to allow me to add this for my own use

Otis wrote:

Probably won't work due to the generics which aren't inferrable(?) through the method I think.

I think it'd need to include some extra type parameters, which makes it a bit more cumbersome.

Otis wrote:

So EntityCollection<T> also should get an extension method, but it then gets awkward as well, as that one will popup in normal code as well...

This is one of the reasons that I was trying to fit everything on line line...this way, there's only 1 extension method on IQueryable<T>, and you don't have to pollute EntityCollection or EntityBase with extra extension methods, which seems messy.

Jez wrote:

Otis wrote:

However as you also said: the indexer is a bit awkward. It can also suggest to fetch for the 1st order the orderdetails

Agreed. One of my colleagues mentioned the same thing I really don't like that indexer, but I can't think of a better way of doing it without sticking type-parameters all over the place.

True... The problem is the multi-branched paths inside a single line, and you have just 1 direction of extensibility: the params parameter, but should that contain new path nodes of the parent, or new path nodes of the path specified? . That's why I added PathEdge, as that introduces the second direction of extensibility inside the methods.

Otis wrote:

due to the nature of extension methods, it's easy to share code like you wrote with others through 3rd party downloads, if we decide not to include your proposed api syntaxis.

Yep...thats one great thing about extension methods. If only there were extension properties too...

yes... The last MVP summit in march 2007 we asked this to the c# team and they agreed it could be useful, but then started to babble about irrelevant things, so it came down to: THEY didn't need it for linq so it's not in the language. Which is sad, because they didn't look at the language and added what was necessay to get the language forward, they added things to get linq implemented.

Otis wrote:

Probably won't work due to the generics which aren't inferrable(?) through the method I think.

I think it'd need to include some extra type parameters, which makes it a bit more cumbersome.

Yes I agree. In my initial design I thought these types were inferred by the compiler but that wasn't the case unfortunately, I couldn't get it compiled, as a type used inside a lambda isn't inferring the type of the method.:

public void Foo<T, U>(Func<T, U> func, ...)

here, if you specify a lambda which defines T and U, like c=>c.Orders, it doesn't work, as a lambda apparently can't define the T and U types, you have to specify them... At least I couldn't get it to work, I had to specify at least U.

Otis wrote:

So EntityCollection<T> also should get an extension method, but it then gets awkward as well, as that one will popup in normal code as well...

This is one of the reasons that I was trying to fit everything on line line...this way, there's only 1 extension method on IQueryable<T>, and you don't have to pollute EntityCollection or EntityBase with extra extension methods, which seems messy.

Yes I agree.

Though, isn't there a way to make PathEdge better perhaps, so your concerns are solved with a better PathEdge design? As I said earlier, the current design of PathEdge isn't my favorite but it's the one which worked, and as you were the one who said if there wasn't a better alternative, perhaps a better PathEdge is possible...

Otis wrote:

True... The problem is the multi-branched paths inside a single line, and you have just 1 direction of extensibility: the params parameter, but should that contain new path nodes of the parent, or new path nodes of the path specified?

My current approach supports both.

Otis wrote:

Though, isn't there a way to make PathEdge better perhaps, so your concerns are solved with a better PathEdge design?

So I had some more time this afternoon to play with this, and have come up with another syntax (this time using a modified PathEdge).

Both these examples do the same thing (from customers, prefetch orders, from orders prefetch orderdetail and employee)

What I came up with earlier in the week:

//q is a query on the Customers table
q.WithPath(
    c => c.Orders[0].OrderDetail,
    c => c.Orders[0].Employee
);

Today's experimenting resulted in:

//q is a query on the Customers table
q.WithPath2(
    path => 
        path.Including<Order>(c => c.Orders)
        .SubPath(subpath => subpath.Including<OrderDetail>(o => o.OrderDetail))
        .SubPath(subpath => subpath.Including<Employee>(o => o.Employee))   
);

and now with added filtering/sorting support

q.WithPath2(
    path => 
        path.Including<Order>(c => c.Orders).Filtered(o => o.EmployeeId == 2).Sorted(o => o.OrderDate)
        .SubPath(subpath => subpath.Including<OrderDetail>(o => o.OrderDetail).Filtered(od => od.UnitPrice > 10))
        .SubPath(subpath => subpath.Including<Employee>(o => o.Employee))   
);

Its a bit more verbose than my previous syntax, but at least it gets rid of that indexer

What do you think?

And with multiple branches in the graph, you simply call .SubPath one or more times and if you need a node along the same path, you place that .SubPath inside the .SubPath() method?

Correct.

Right, I now have support for pretty much everything that the current PathEdge does.

'Basic' prefetch paths don't require type parameters:

var query = from c in metaData.Customer
        select c;

query = query.WithPath2(path => path.Prefetch(c => c.Order));

You have to specify the type of the end-node if you want to do anything more advanced. For example, subpaths:

var query = from c in metaData.Customer
        select c;

query = query.WithPath2(path => 
        path.Prefetch<OrderEntity>(c => c.Order)
            .SubPath(subpath => subpath.Prefetch(o => o.OrderDetail))
);

Filtering works...

var query = from c in metaData.Customer
        select c;

query = query.WithPath2(path => 
            path.Prefetch<OrderEntity>(c => c.Order)
            .Filtered(o => o.EmployeeId == 2)
);

...and so does sorting (with multiple sort-fields)

var query = from c in metaData.Customer
        select c;

query = query.WithPath2(path =>
            path.Prefetch<OrderEntity>(c => c.Order)
            .OrderBy(o => o.Freight).OrderByDescending(o => o.OrderDate)
);

...and limiting

var query = from c in metaData.Customer
        select c;

query = query.WithPath2(path =>
            path.Prefetch<OrderEntity>(c => c.Order)
            .OrderBy(o => o.Freight).OrderByDescending(o => o.OrderDate)
            .Limit(3)
);

Subpath/Filtered/OrderBy/Limit all work on nested subpaths too, so you can go as deep as you like:

var query = from c in metaData.Customer
        select c;

query = query.WithPath2(path =>
            path.Prefetch<OrderEntity>(c => c.Order)
            .SubPath(subpath => 
                subpath.Prefetch<OrderDetailEntity>(o => o.OrderDetail)
                .Filtered(od => od.UnitPrice > 10)
                .OrderBy(od => od.Quantity)
                .SubPath(anotherSubPath =>
                    anotherSubPath.Prefetch(od => od.Product)
                )
            )
            .OrderBy(o => o.Freight).OrderByDescending(o => o.OrderDate)
            .Limit(3)
);

I notice that IPathEdge has a property for an ExcludeIncludeFieldList. I tried to get this working but couldn't...maybe I'm missing something but as far as I can tell this is never actually used? I couldn't find any references to it in the LinqSupportClasses code.

Looks great!

The only issue (small) I have with it is the multi-branch vs. multi-node-same-branch names for the methods... It gets confusing pretty quickly, HOWEVER that's also the case with the pathedge nodes (which node belongs to which path edge?). Or am I overlooking something? As I said: it is likely a small thing, as one way or the other: deep, complex paths will be a bit complicated to specify. I'm thinking of adding some sort of support for a way to specify a pre-fabricated path as well through an overload of WithPath as a lot of people have written methods which produce prefetch path objects ready to use.

I'll look into the ExcludedFields stuff.

Excluded fields weren't passed to the PathEdgeExpression indeed, and from there not added to the path element etc. I saw an .Add() overload was missing from the IPrefetchPath interface as well, so I've correct that as well.

Fixed in next build. I'll fix the filter issue as well and will then attach a new build to that thread so you can check it out.

Thanks, I just tried the new build and everything seems to be working

Updated syntax with including/excluding support:

var query = from c in metaData.Customer
        select c;

query = query.WithPath2(path =>
            path.Prefetch<OrderEntity>(c => c.Order)
            .Excluding(o => o.RequiredDate, o => o.ShippedDate)
);

...and I think thats it. I can't think of anything else to add

Otis wrote:

It gets confusing pretty quickly, HOWEVER that's also the case with the pathedge nodes... [snip]...as one way or the other: deep, complex paths will be a bit complicated to specify...

I agree. If you've got a complex prefetch path, then no matter which syntax you use (PathEdge, lambdas or PrefetchPath objects) its always going to be fairly complicated to construct the path. Although, good use of indenting can make both the PathEdge and the lambda approaches easier to read.

Otis wrote:

I'm thinking of adding some sort of support for a way to specify a pre-fabricated path as well through an overload of WithPath

Sounds like a good idea.

Jez wrote:

Thanks, I just tried the new build and everything seems to be working

Updated syntax with including/excluding support:

var query = from c in metaData.Customer
        select c;

query = query.WithPath2(path =>
            path.Prefetch<OrderEntity>(c => c.Order)
            .Excluding(o => o.RequiredDate, o => o.ShippedDate)
);

...and I think thats it. I can't think of anything else to add

It's indeed complete Perhaps a method for Including fields as well, but I find that abit nittpicking (similar to 'Filtered' which should probably be something like 'WithFilter' or 'FilterOn', as it then matches 'OrderBy' in naming. 'Excluding' then should probably be 'Exclude', all methods express an action that way).

I must say, great work!

Otis wrote:

It gets confusing pretty quickly, HOWEVER that's also the case with the pathedge nodes... [snip]...as one way or the other: deep, complex paths will be a bit complicated to specify...

I agree. If you've got a complex prefetch path, then no matter which syntax you use (PathEdge, lambdas or PrefetchPath objects) its always going to be fairly complicated to construct the path. Although, good use of indenting can make both the PathEdge and the lambda approaches easier to read.

Otis wrote:

I'm thinking of adding some sort of support for a way to specify a pre-fabricated path as well through an overload of WithPath

Sounds like a good idea.

I'll probably do that indeed.

Ok, how to go from here? What I want to avoid is that there are two different api systems for WithPath where the one we added and documented is hardly used .

I assume you've written your code based on PathEdge, so am I correct that if we remove our WithPath method, your code doesn't work anymore?

Do you want to share the code with us or do you want to keep it apart? If you share it with us, you have to sign a document that you hand over the specific code to us, as we'd otherwise be liable to lawsuits (not that we don't trust you, but it's never a good idea to have code you don't own inside your own library ). We can discuss this via email if you want.

Otis wrote:

Perhaps a method for Including fields as well

Already have that

I'll do your suggestion and rename the methods to make them more consistent.

Otis wrote:

I assume you've written your code based on PathEdge, so am I correct that if we remove our WithPath method, your code doesn't work anymore?

Correct...currently there's a class (PathEdgeParser) that generates a List of IPathEdge objects. A custom WithPath extension method instantiates one of these and then hands the result to your WithPath method.

Otis wrote:

Do you want to share the code with us or do you want to keep it apart?

I'm happy to share it - there's not much of it (about 300 LOC). It could probably do with some better error handling, and I guess it also needs testing with VB (afaik VB has crippled lambda support).

Otis wrote:

If you share it with us, you have to sign a document that you hand over the specific code to us, as we'd otherwise be liable to lawsuits

Sounds good to me. Alternatively I could just open source it, then you could do what you like with it Whichever approach you prefer I'm happy with.

Jez wrote:

Otis wrote:

Perhaps a method for Including fields as well

Already have that

hehe

I'll do your suggestion and rename the methods to make them more consistent.

Otis wrote:

I assume you've written your code based on PathEdge, so am I correct that if we remove our WithPath method, your code doesn't work anymore?

Correct...currently there's a class (PathEdgeParser) that generates a List of IPathEdge objects. A custom WithPath extension method instantiates one of these and then hands the result to your WithPath method.

Ah ok, so if I simply leave the method undocumented in teh userdocumentation (just reference manual) people will have the choice to go lowlevel or use your approach. (making it internal is another option but that closes the door for people who want yet another approach)

Otis wrote:

Do you want to share the code with us or do you want to keep it apart?

I'm happy to share it - there's not much of it (about 300 LOC). It could probably do with some better error handling, and I guess it also needs testing with VB (afaik VB has crippled lambda support).

That should be fine VB doesn't have lambda statements (so a full statement inside a lambda) but that's not used anyway in your or our api, all what's used are lambda expressions which are supported by VB.

I like your API so I'd like to include that as the main prefetch path api.

Otis wrote:

If you share it with us, you have to sign a document that you hand over the specific code to us, as we'd otherwise be liable to lawsuits

Sounds good to me. Alternatively I could just open source it, then you could do what you like with it Whichever approach you prefer I'm happy with.

Ok, could you send us an email (support AT llblgen DOT com) with the code, the license you want to release it under and if that's flexible enough we can leave it at that (e.g. bsd2 or 'use it, I don't care') otherwise we've to do officially license your code.

BSD2 sounds fine

I'll package it up and email it in a few minutes. Currently, I've only tested it with Adapter so I'm not sure if anything needs to be changed to work with selfservicing...I've tried to make it as generic as possible.

Jez wrote:

BSD2 sounds fine

I'll package it up and email it in a few minutes. Currently, I've only tested it with Adapter so I'm not sure if anything needs to be changed to work with selfservicing...I've tried to make it as generic as possible.

Ok. You've to formulate the copyright clause in the BSD2 license so we can add that to the docs.

Thanks again

~~Urgh, I realised that it definately won't work with selfservicing as I'm explicitly using IEntityCollection2 in a couple of places.

Would you like me to email it to you in its current state or to try and change it to work with SelfServicing first?~~

Edit: Nevermind, it was a quick fix (although selfservicing hasn't been fully tested). Email has been sent.

Got it! Thanks a million I'll go over the code tomorrow and will hopefully be able to include it in tomorrows build

Glad I could be of service

I'm sure there's some stuff in there that could be optimised/done better. I'd be interested to see what changes you make to it

I had to refactor (and basicly rewrite core logic) of the projection pipeline in the linq provider, as it was unnecessarily complex and couldn't handle all situations. See: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=12867

so I'll work on your code on monday