[Solved] Sorting on field which does not exist?

[Ahmed Ilyas]

Thought I would catch your attention!

So I have a telerik grid (not RAD grid) and it is doing AJAX calls for sorting/filtering etc...
The data I am showing 99% of the columns exist in my view model, except for 1 which is "Username". we have a UserId.
I am also doing late binding using IQueryable.

I want to display Username. Username is held in a different table and I know I can obtain that value easily but it will fail because when the sorting etc.. is being applied, then I do a .ToList(), it states that "Username" does not exist / not supported.

its difficult to explain but here is the code:

private IQueryable<DeviceHistoryViewModel> GetAllDeviceHistory(GridCommand command, out int count)
        {
            var myData = _deviceService.FindAllDevicesHistory().ToDeviceHistoryViewModel();

            myData = myData.ApplyFiltering(command.FilterDescriptors);
            count = myData.Count();

            myData = myData.ApplySorting(command.GroupDescriptors, command.SortDescriptors);
            myData = myData.ApplyPaging(command.Page, command.PageSize);

            return myData;
        }


public static IQueryable<DeviceHistoryViewModel> ToDeviceHistoryViewModel(this IQueryable<IDeviceHistory> source)
        {
            if (source == null)
                return null;

            return source.Select(x => new DeviceHistoryListViewModel
            {
                Id = x.Id,
                CreatedAt = x.CreatedAt,
                CreatorTypeEnum = x.CreatorType,
                DeviceId = x.DeviceId,
                MessageTypeEnum = x.MessageType,
                ValueNum1 = x.ValueNum1,
                ValueText1 = x.ValueText1,
                ValueText2 = x.ValueText2,
                UserId = x.UserId
            });
// yes, I also DO have a Username property but not being used/populated
        }

Apply filtering:

public static IQueryable<DeviceHistoryViewModel> ApplyFiltering(this IQueryable<DeviceHistoryViewModel> data, IList<IFilterDescriptor> filterDescriptors)
        {
            if (filterDescriptors.Any())
            {
                data = data.Where(ExpressionBuilder.Expression<DeviceHistoryViewModel>(filterDescriptors));
            }
            return data;
        }

The rest is obviously. nothing fancy but calling the LINQ extension methods, such as OrderBy(...) when calling AddSortExpression.

now the problem is, because "Username" does not exist when you are doing the filtering/sorting - how is it possible to obtain Username when performing the sorting/filtering, since everything is IQueryable until the last moment?

not even sure if I am making sense!

[Rosen]

Hi Ahmed Ilyas,

The code you have pasted looks ok to me. How the UserName is loaded? It should work if the projection is similar to the following:

return source.Select(x => new DeviceHistoryListViewModel
            {
                Id = x.Id,
                CreatedAt = x.CreatedAt,
                CreatorTypeEnum = x.CreatorType,
                DeviceId = x.DeviceId,
                MessageTypeEnum = x.MessageType,
                ValueNum1 = x.ValueNum1,
                ValueText1 = x.ValueText1,
                ValueText2 = x.ValueText2,
                UserId = x.UserId,
                UserName = x.User.Name
            });

However, if it is loaded through some external method most probably the Linq provider will not be able translate it to SQL.

Also I have noticed that you are using similar code as to the one from the example. In this case you should make sure that the type used in the expression building routines does have same properties as the type used in the select projection.

All the best,
Rosen
the Telerik team

[Ahmed Ilyas]

Hi, thanks for your response and sorry in the delay in replying.

the Username comes from another object, just as I had shown and you quoted.
initially, it does some explicit call to the User table to get the username. But then when we do a sort/filter, I get a LINQ to SQL Exception saying that there is no translation support for User/Username.

what should I do from here?

Solution Layout:

several projects. One of which contains the LinqToSql entities and DBML. Other projects throughout the solution reference this solution.

This particular area - I dont want it to do a ToList() then return that back, because I want the sorting/filtering etc... to happen at the SQL end (hence the IQueryable). But because it references other entities (in this case, User) ... it cannot translate that. So im not sure what to do here. I need for it to some how load and apply the filtering/sorting against not just the primary entity, but the other related entity in question.

[Rosen]

Hello Ahmed Ilyas,

Could you please provide a small sample which demonstrates your scenario? This will allow us to get better understanding of your implementation and if possible provide workaround. 

Regards,
Rosen
the Telerik team

[Ahmed Ilyas]

as much as I would love to, unfortunately i cannot provide a sample because of the solution.... its very large and a "confidential"/"private" software.
is there anything else or another way I can explain/show the problem?

the code snips I provided are pretty much the core snippets.
I am working against interfaces, if that helps. But no matter what I do - I always get the circular reference exception now when doing the sorting. I have modified the repository to include "Device" object with "DeviceHistory" using loadwith. this does help a little but I get the circular reference problem when results are being sorted and to be displayed on the grid

[Rosen]

Hi Ahmed Ilyas,

There is no need to send us your actual project, but just a small sample which illustrates the problem you are having. 
Regarding the circular references error you may check this blog post.

Regards,
Rosen
the Telerik team

[Ahmed Ilyas]

Thanks - unfortunately even to send a small sample will cause me to loose some time but I know it would help you alot.
Regarding the blog post, I've read that before but still no go unfortuantely.

[Ahmed Ilyas]

is there any chance doing a webex type session? Really, I am behind a week on this and it does not look good.

[Ahmed Ilyas]

I have managed to cut the solution down from a large project to somewhat medium sized.
how can I send over the solution/DB to you?

[Rosen]

Hi Ahmed Ilyas,

You may attach it here or open a support ticket. Regarding the webex session, I'm afraid that generally we do not have such practice.

Greetings,
Rosen
the Telerik team

[Ahmed Ilyas]

Thankyou.

well this sucks, once thing after another. I cannot submit a support ticket because I dont have any packages/subscription associated with my account. But I am using the client's purchased subscription - and as a contractor/external consultant, I cannot ask/request for their licence to add to my account.

[Atanas Korchev]

Hello Ahmed Ilyas,

 If attaching the project to this forum thread is not an option you may the owner of the license to add your Telerik account as a licensed developer. If this is also not an option you may consider a project which will not disclose any private data by using dummy database values.

Regards,
Atanas Korchev
the Telerik team