Possible Bug when Auto-Paging on a single Table?

Reply

DanM Posted: 01-30-2008 3:39 AM

rated by 0 users

Hi,

I noticed what seems to be an issue with your esDataSource AutoPaging yesterday. I wondered if you could confirm if it is a bug or not. Basically, In the DataSource's esSelect event handler, I set the DataSource's query with

protected void coursesDataSource_esSelect(object sender, esDataSourceSelectEventArgs e)
{
   e.Collection = new CourseCollection();
   e.Query = GetSearchQuery();
}

Initially, GetSearchQuery() ran a select on just one table

private CourseQuery GetSearchQuery()
{
   CourseQuery course = new CourseQuery("course");

      course.Select(
         course.CourseId,
         course.Date.As("CourseDate"),
         course.CustomerId.As("CustomerName"),
         course.ProductCode.As("ProductName"),
         course.AddressPostCode.As("Location"),
         course.ExpectedNumberOfDelegates.As("ExpectedDelegates"),
         course.Cost.As("ConfirmedDelegates"),
         course.StatusId.As("Status"));

   return course;
}

But when bound into the GridView, ASP.NET returned a lot of errors saying that many multiple.valued identifiers could not be bound to the GridView. Doing a quick check, this was because ES was generating the follwoing invalid command

WITH [withStatement] AS 
(SELECT course.[CourseId],
	course.[Date] AS 'CourseDate',
	course.[CustomerId] AS 'CustomerName',
	course.[ProductCode] AS 'ProductName',
	course.[AddressPostCode] AS 'Location',
	course.[ExpectedNumberOfDelegates] AS 'ExpectedDelegates',
	course.[Cost] AS 'ConfirmedDelegates',
	course.[StatusId] AS 'Status' , 
	ROW_NUMBER() OVER( ORDER BY course.Date ASC) AS ESRN 
FROM [Course] course) 
SELECT course.[CourseId],
	course.[Date] AS 'CourseDate',
	course.[CustomerId] AS 'CustomerName',
	course.[ProductCode] AS 'ProductName',	
	course.[AddressPostCode] AS 'Location',
	course.[ExpectedNumberOfDelegates] AS 'ExpectedDelegates',
	course.[Cost] AS 'ConfirmedDelegates',
	course.[StatusId] AS 'Status'  
FROM [withStatement] WHERE ESRN BETWEEN 1 AND 15

However, if I added the first of (many) joins to the search query like so

private CourseQuery GetSearchQuery()
{
   CourseQuery course = new CourseQuery("course");
   UserCustomerQuery customer = new UserCustomerQuery("customer");

      course.Select(
         course.CourseId,
         course.Date.As("CourseDate"),
         customer.Name.As("CustomerName"),
         course.ProductCode.As("ProductName"),
         course.AddressPostCode.As("Location"),
         course.ExpectedNumberOfDelegates.As("ExpectedDelegates"),
         course.Cost.As("ConfirmedDelegates"),
         course.StatusId.As("Status"));
      course.InnerJoin(customer).On(course.CustomerId == customer.UserId);

   return course;
}

Then all the binding works fine and the GridView is populated correctly. And the SQL that ES generates looks like this

WITH [withStatement] AS 
(SELECT course.[CourseId],
	course.[Date] AS 'CourseDate',
	customer.[Name] AS 'CustomerName',
	course.[ProductCode] AS 'ProductName',
	course.[AddressPostCode] AS 'Location',
	course.[ExpectedNumberOfDelegates] AS 'ExpectedDelegates',
	course.[Cost] AS 'ConfirmedDelegates',
	course.[StatusId] AS 'Status' , 
	ROW_NUMBER() OVER( ORDER BY course.Date ASC) AS ESRN 
FROM [Course] course 
	INNER JOIN [UserCustomer] customer ON course.[CustomerId] = customer.[UserId]) 
SELECT * FROM [withStatement] WHERE ESRN BETWEEN 1 AND 15

Which is pretty different from the first SQL generated without the join. The first SQL statement definitely doesn't work. Thought you should know.

Reply

Mike.Griffin replied on 01-30-2008 4:37 AM

rated by 0 users

On your first example you are using the syntax that is used for joins, however, you are not using a join. Basically, on the first example you should be setting the collection's "Query" to the GetSearchQuery() by passing the Query in and filling it in, then not setting the "e.Query" variable.

This is our fault for not having better documentation. There are two samples of esDataSource installed with EntitySpaces, you can find them under the EntitySpaces folder. There is one page for joins and one without that show you how to use the esDataSource.

I hope this helps. We are going to improve esDataSource quite a bit even on ES2008 although it works quite well now. 

Reply

DanM replied on 01-30-2008 8:37 AM

rated by 0 users

Hi Mike,

Thanks for clarifying this.

I gotta think though that if you're planning to do joins as I was but do a single table query first, it's mighty inconvenient that this approach of doing a no-table join just doesn't work. If the SQL generated included SELECT * from [withStatement] as it does when one table is being joined to another, everything would work fine. Looking forward to ES2008 to see what changes you've made

Dan

Reply

LBowers replied on 01-30-2008 11:40 AM

rated by 0 users

I agree with Dan. It would be much nicer if this approach worked for both single table queries and join queries.

Reply

LBowers replied on 05-05-2008 9:24 AM

rated by 0 users

Nice one Mike. I'm looking forward to the ES 2008 final release.