Hi I'm finally trying Linq with the Devexpress XtraGrid control (Happens in server mode //doesn't work as youd expect when servermode=false)

LLBL v2.6 Devexpress 8.1.4

Things look ok BUT

when I try to run it with a prefetch path a lot of strange things happen

the grid has a whole bunch of empty rows (all the row values are null and the number of rows looks about right though) the linqMetaData object has data (but seemingly no prefetch data associated with it [wko not populated])

when I do a group by "column" in the grid, data starts to populate the grid i can see the "group by" names, when i expand the detail view the rows are there... EXCEPT when.. the "group by" is large (looks like ~ >1000) the data disappears again

after ~ 30 secs the data won't appear at all (da must be timing out? / number of rows too large?)

            
            da = new DataAccessAdapter(xStringHelper.XString);
            //da.StartTransaction(System.Data.IsolationLevel.ReadUncommitted, "CreateNoLockAdapter");
            da.KeepConnectionOpen = true;
            linqMetaData = new LinqMetaData(da);
            //object x = linqServerModeSource1.InitializeLifetimeService();
            //x = null;
            linqServerModeSource1.ElementType = typeof(EquEntity);
            linqServerModeSource1.KeyExpression = "TagNumber";
            linqServerModeSource1.QueryableSource = (from equ in linqMetaData.Equ
                                                    select equ)
                                                    .WithPath(p=>p.Prefetch(equ=>equ.Wko));
                                                    //.WithPath(new PathEdge<WkoEntity>(EquEntity.PrefetchPathWko)); 

I wonder whether this is an LLBL or Devexpress issue? Maybe both?

Cheers Andrew PS Created a support request at Devexpress too: http://devexpress.com/Support/Center/p/Q106932.aspx

Please don't post this in general chat, we will likely miss it as this forum isn't monitored.

The prefetch path has to be on the outside of the query. THis means: the prefetch path has to be applied to the projection of the query.

However the linq datasource of devexpress will wrap the queryable with other statements for the grid. THis is logical, and there's nothing wrong with that. The downside is that the query you passed is wrapped inside another query, which makes the prefetch path become a path inside the query and it's ignored/doesn't work as expected.

If you switch on LinqExpressionHandler tracing to level 3 like: <add name="LinqExpressionHandler" value="3" />

you'll get the expression tree as it is sent to the linq provider in text form. That should give you an idea what's going on.

If you don't specify the prefetch path, the query should work properly.

Can you give me an example?

I am a little confused as to which projection I have to add the prefetch path to...

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EntityClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Count()
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EntityClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Take(64)

Thanks Andrew

dioptre wrote:

Can you give me an example?

I am a little confused as to which projection I have to add the prefetch path to...

Thanks Andrew

Can I close the other thread? As there are now 2 threads about this.

Anyway, you do this:

(from equ in linqMetaData.Equ  select equ)
    .WithPath(p=>p.Prefetch(equ=>equ.Wko));

so this is the query and a path specification, which is applied to the projection of the query (select equ)

however as this is a Queryable, I could do:

from x in 
    (from equ in linqMetaData.Equ  select equ)
         .WithPath(p=>p.Prefetch(equ=>equ.Wko))
join o in metaData.SomeEntity on x.SomeId equals o.SomeId
select o;

here, the .WithPath is applied to a projection (select equ) but wrapped inside the main query, and therefore not always performed / ignored as the query's actual projection is select o. There is code in our provider which tries to pull it upwards.

The linq datasource control of devexpress wraps the query in a group by (on constant), which thus makes the prefetch path become a path inside another query. It also can create a new projection and fetch the data into an array. So there aren't any entities fetched, but new anonymous typed objects (if I recall correctly). For the working of the grid, this is perfectly ok. However, if you want to apply entity-oriented prefetch paths to that, that's a different story: that's not going to work in all cases.

It DOES for the query you passed in, but that's the basis of the actual query which is being executed at runtime. That's also why I asked you to enable the LinqExpressionHandler tracing to level 3, so you can see the expression tree in text and see that it doesn't look like the query you've passed in (it's bigger).

So I don't think anyone is to blame here, DevExpress obviously hasn't support for .WithPath, as it's LLBLGen Pro specific. And Linq to LLBLGen Pro doesn't have support for context-based prefetch paths like LoadOptions ala Linq to Sql, as that's not really useful in practise, except perhaps for these situations (although I doubt it will work with linq to sql as well, because the actual types fetched aren't always entities, but could be anonymously typed elements)

(edit)

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EntityClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Take(64)

here, you see what's happening: the query you passed:

(from equ in linqMetaData.Equ  select equ)
    .WithPath(p=>p.Prefetch(equ=>equ.Wko));

is wrapped inside an OrderBy and a Take, two commands added by the grid, because the grid needs these (and therefore correctly added them). This would be no problem at all, however the .WithPath() call is now wrapped inside another query, as the OrderBy is applied to a query with a projection and therefore wraps the query.

it's the same as this:

var q = from c in metaData.Customers select c;
var q = q.OrderBy(c=>c.Country);

this results in:

select ...
from (select ... ) a
order by a.Country asc

as the order by doesn't check whether it can be merged with the query it has to order, it might not be possible to do so, hence it wraps the query. ... and the path. The code in our provider tries to pull up the path to the final projection, but that's not always possible (i.e. because the final projection is incompatible, or it's not decidable where to pull the path from)

Normally one wouldn't run into this, unless the .OrderBy is appended to the query executed by code not written by the developer.

(edit).

I tried:

var q = (from o in metaData.Order
         select o).WithPath<OrderEntity>(p => p.Prefetch(o => o.Customer));
q = q.OrderBy(o=>o.OrderDate).Take(64);

and this works fine (orders are fetched, sorted and limited, and the prefetch path is fetched too).

Though the grid executes more queries if I'm not mistaken, especially when grouping is enabled or paging -> it will try to group on a constant, with anonymous types if I'm not mistaken .

(edit) like:

var q = (from o in metaData.Order
         select o).WithPath<OrderEntity>(p => p.Prefetch(o => o.Customer));

var q2 = q.OrderBy(o => o.OrderDate).Take(64).Select(o => new object[] { o.OrderId, o.CustomerId });

If I now run q2, the path is completely ignored. It's not compatible with the projection, as what should it do? so it will return a list of object arrays with 2 values.

So I'm getting the feeling that this won't work then?!

(the reverse joins like the customer/order one above killed my machines cpu)

There must be something to force it to keep the prefetches at least? Stored in an array you mentioned? I wonder if we can insert them back in afterwards? What would be required to implement LoadOptions?

Thanks Ps pls kill the other thread

dioptre wrote:

So I'm getting the feeling that this won't work then?!

that depends on what exact queries the grid is executing. It executs more than 1 query, so enable just 1 tracer (the LinqExpressionHandler) at level 3 (NOT 4!) and check the various queries executed by the grid.

(the reverse joins like the customer/order one above killed my machines cpu)

Hmm. As it would fetch 1 query with 818 entities and 1 with 89, here it takes a couple of ms to do that.

There must be something to force it to keep the prefetches at least? Stored in an array you mentioned? I wonder if we can insert them back in afterwards?

I don't think they're insertable afterwards as the projection of the query is different.

But let's first look into all the queries the grid executes, as there are more than 1. So enable the tracer I mentioned above and paste the query here (as you've done above as well). No SQL, just the expression tree in text. There should be a couple, also one with a groupby

Thanks Ps pls kill the other thread

Will do

Using:

            linqMetaData = new LinqMetaData(da);
            linqServerModeSource1.ElementType = typeof(EquEntity);
            linqServerModeSource1.KeyExpression = "TagNumber";                                                  
            var q = (from equ in linqMetaData.Equ
                     select equ);
            q = q.WithPath<EquEntity>(p => p.Prefetch(equ => equ.Wko));;
             //Tried with this, does nothing really
            //q = q.OrderBy(equ => equ.TagNumber);
            linqServerModeSource1.QueryableSource = q;

I got the following....

--Start (select all, no rows visible, although number of rows looks accurate)--

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Count()
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Take(64)

--Group By TagNumber (PK, (rows minimised, all group descriptions visible))--

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).GroupBy( => .TagNumber).OrderBy( => .Key).Select( => new [] {Convert(.Key), Convert(.Count())})

--Expanded Group By TagNumber(PK), (Row visible)--

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Where( => (.TagNumber = "10007789"))

--Remove Group By (No rows visible, although number of rows looks accurate)--

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Take(64)

--Add Non PK Group By (Group by descriptions visible)--

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .Facility).ThenBy( => .TagNumber).GroupBy( => .Facility).OrderBy( => .Key).Select( => new [] {Convert(.Key), Convert(.Count())})

--Expand Group (Only a few rows but all visible)--

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .Facility).ThenBy( => .TagNumber).Where( => (.Facility = "XXXCNX"))

--Expand Group By (Large dataset, NO ROWS VISIBLE)--

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .Facility).ThenBy( => .TagNumber).Where( => (.Facility = "XXXIPX")).Take(64)

--Sorted Column (non pk), Same rows visible (a few from XXXCNX, none from XXXIPX)--

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .Facility).ThenBy( => .Descriptn).ThenBy( => .TagNumber).GroupBy( => .Facility).OrderBy( => .Key).Select( => new [] {Convert(.Key), Convert(.Count())})
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .Facility).ThenBy( => .Descriptn).ThenBy( => .TagNumber).Where( => (.Facility = "XXXCNX"))
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .Facility).ThenBy( => .Descriptn).ThenBy( => .TagNumber).Where( => (.Facility = "XXXIPX")).Take(64)

(edit)

Thought this might also work but no

               var q = from w in linqMetaData.Wko
                group w by w.Equ into e
                select e;

Ran this (no rows):

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).GroupBy(w => w.Equ).Select(e => e).OrderBy( => .Key).Count()

If I use the following and I scroll past 64, the initial count, I cause recursion:

            var q = (from wko in linqMetaData.Wko
                     select wko).WithPath<WkoEntity>(p => p.Prefetch(wko => wko.Equ));

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Where( => (.TagNumber = "10007789"))
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Select( => .TagNumber).Take(256)
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Count()
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Take(64)
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Where( => (.TagNumber = "10007789"))
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Select( => .TagNumber).Take(256)
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy( => .TagNumber).Count()

If I add

            q = q.OrderBy(wko=>wko.TagNumber);

I get nothing but blanks and...

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy(wko => wko.TagNumber).OrderBy( => .TagNumber).Count()
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.WkoEntity]).Select(wko => wko).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy(wko => wko.TagNumber).OrderBy( => .TagNumber).Take(64)

Thanks Andrew

Thanks for the queries. Some are indeed selecting entities, others are selecting object arrays.

The ones with the entities should work ok with the path, the others don't have the path as I described in my previous post (because path works with entities, and the resultset is a list of object arrays).

This query:

        var q = from w in linqMetaData.Wko
                group w by w.Equ into e
                select e;

is a special query. 'e' is a hierarchy with a key (w.Equ) and all Wko's matching that. This is executed in the projection as a nested query. This is actually a special case query so a grouped result is returned as a complete hierarchy. However, if select e is wrapped into a subquery, this of course doesn't work, because the hierarchy doesn't exist INSIDE the database, only inside memory, as it's a Linq specific construct.

(edit) I verified it, it returns data normally:

[Test]
public void GetOrdersWithCustomerUsingPathAndAppendedOrderBy()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = (from o in metaData.Order
                 select o).WithPath<OrderEntity>(p => p.Prefetch(o => o.Customer));

        q = q.OrderBy(o=>o.OrderDate).Take(64);
        int count = 0;
        foreach(var v in q)
        {
            count++;
            Assert.IsNotNull(v.Customer);
            Console.WriteLine("{0}, {1}, {2}", v.OrderId, v.CustomerId, v.Customer.CompanyName);
        }

        Assert.AreEqual(64, count);
    }
}

query:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NW26.Adapter.EntityClasses.OrderEntity]).Select(o => o).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy(o => o.OrderDate).Take(64)

gives: 10248, VINET, Vins et alcools Chevalier 10249, TOMSP, Toms Spezialitäten 10250, HANAR, Hanari Carnes 10251, VICTE, Victuailles en stock 10252, SUPRD, Suprêmes délices 10253, HANAR, Hanari Carnes 10255, RICSU, Richter Supermarkt 10256, WELLI, Wellington Importadora 10257, HILAA, HILARION-Abastos etc...

The ones with the entities should work ok with the path,

Problem is it just doesn't work because of what the xtragrid is wrapping around it by the looks of it.....even when you turn server mode off (I'm getting the following extra...)

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy(wko => wko.TagNumber).OrderBy( => .TagNumber).Count()
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[MyClasses.EquEntity]).Select(equ => equ).WithPath(value(SD.LLBLGen.Pro.LinqSupportClasses.IPathEdge[])).OrderBy(wko => wko.TagNumber).OrderBy( => .TagNumber).Take(64)

Looks like I'm either going to have to investigate LoadOptions or change/fix the detail/prefetch after the initial projection.....

doh

(edit)

If you get a chance to have a bash on xtragrid, can you let us know if you succeed!?

Thanks Andrew

The interesting conclusion is that it looks like anything with Take(64) that uses WithPath gets improperly parsed for some reason....

Not sure why though?!

Though my test queries have that orderby and Take() call... (see above)

If you run your query without the grid (so foreach over it), and add .OrderBy(...) and .Take(64), does it work then?

Anyone else who may be interested, is a bug and is being fixed.

Still wondering if the grid now also shows all rows and not empty rows? (build will be released in the coming week)

Anyone else who may be interested, is a bug and is being fixed.

a DevX bug?

arschr wrote:

Anyone else who may be interested, is a bug and is being fixed.

a DevX bug?

It was at least also an issue with prefetch paths referring to wrapped queries. A sort of edge case in linq queries, which could pop up with queries where a prefetch path was defined inside a query (so not at the outside), and it was dealing with renamed fields and it would use subqueries for related node filtering. It was a bug in the runtime. For clarity, the fixed dll is attached.

The empty rows being reported, I don't know if that's our fault, as the data either would be returned, or an exception would be thrown.

Almost certain the bug (now fixed) fixes the empty rows!

No worries people!

Andrew

OK

Actually the missing rows have come back to haunt me....

For a very specific case:

When I want to use a distinct ordered selection Eg.

            //Serial (search)
            LinqMetaData lmdSerial = new LinqMetaData(TechPersistence.ProvisionBoundLinqAdapter());
            linqSMSSerial.KeyExpression = "SerialNumber";
            linqSMSSerial.QueryableSource = (from equ in lmdSerial.Equ
                                             where (equ.SerialNum != null && equ.SerialNum != string.Empty)
                                             select new { equ.SerialNum }).Distinct();

The ordered selection is injected by the XtraGrid (as it can not be done twice in SQL server).

Now the PK of the table IS NOT SerialNumber.

But this should still work.

In fact - mostly it does.....BUT.... sometime it doesn't!

This isn't an issue if I add the PK to the "select new{" and the KeyExpression.

Now this is the interesting part.... If I enumerate over the entire list (Ie. I hit the filter button on the column) the grid comes back....

Which I believe this can only mean it is a paging issue....(Now the paging for the tables with PKs in the XtraGrid works... which makes me think this is an LLBL problem)

But why it only works/stuffs up sometimes?!!?!

Anyway would appreciate if someone could take a look aswell!

Cheers Andrew

I think, but I'm guessing, is that the count query is different from the actual fetch query (use the LinqExpressionHandler tracer at level 3 to check the expression trees in text). As the value you're fetching isn't the PK, you obviously have duplicates.

Paging over rows with duplicates can be problematic, as duplicates are filtered out. So it might be the grid expects the duplicate values, but the paging logic obviously doesn't allow duplicates so filters them out. By adding the PK, you don't have duplicate rows, so the # of rows reported by the count query is the same as the # of rows returned by the paging logic. Does that sound plausible?

I'm using the

.Distinct();

So I would expect this wouldn't be a problem.

Cheers Andrew

PS Masses of chunks of rows are missing (intermittently too) but where they go missing is sporadic...

dioptre wrote:

I'm using the

.Distinct();

So I would expect this wouldn't be a problem.

Cheers Andrew

PS Masses of chunks of rows are missing (intermittently too) but where they go missing is sporadic...

I asked a question to inspect the queries of count and the normal queries. PLease answer my questions, I don't have your code in front of me.

Sorry mate,

I really can't be backdating changes from the beginning of the day as I am extremely busy...

The queries are no different from the ones previously mentioned. Devexpress wraps the count, the table is unchanged the entire time. The distinct part of the query should work.....

When I have time, I will try and revisit this.... but in the meantime....

I'll close the thread for now, post back when you have the time, it will re-open. You use a different query now, so I want to see what the exact expressions are and what SQL is generated for the paging/query. At the moment I can only guess what it looks like.