ToDataSourceResult distinct values

[Joe]

How can the results from ToDataSourceResult be reduced down to distinct unique values?

An example of what we are doing is that we have a dropdownlist that lets a user choose a car make.

We then render the users who have ever owned a car of that make in a datagrid.

We accomplish this by adding a filter to the datasource for the car make that was selected. Similar to:

filter.push({ field: "CarMake", operator: "equals", value: document.getElementById("fieldCarMake").value });

 

The call to the controller is essentially:

public JsonResult ResourceLookupGet([DataSourceRequest] DataSourceRequest request)
{
    return Json(DataContext.UserCars.Select(s => new { Id = s,Id, Name = s.Name}).ToDataSourceResult(request));
}

 

Where the view "UserCars" has a record with the Users Id, Name, and the Car Make and Model they have driven. Example:

10, Bob Smith, Ford, Mustang
10, Bob Smith, Chevrolet, Corvette
10, Bob Smith, Ford, Pinto
11, Fred Jones, Chevrolet, Corvette
11, Fred Jones, Chrysler, Avenger
12, Jane Doe, Ford, Mustang
12, Jane Doe, Ford, Escort

 

So, if someone selects "Ford", the filter ["CarMake" EQ "Ford"] is in the "request" instance, and ToDataSourceResults(request) will return:

10, Bob Smith, Ford, Mustang
10, Bob Smith, Ford, Pinto
12, Jane Doe, Ford, Mustang
12, Jane Doe, Ford, Escort

 

What we want to return in the Json is:

10, Bob Smith
12, Jane Doe

 

What is the recommended method to accomplish this?

[Dimiter Topalov]

Hello Joe,

There is no specific Kendo UI - related method that provides the desired functionality, but you can use the C# fluent API Distinct() method, or GroupBy() to group the data set by the field that needs to be unique, and use another Select() afterwards.

You can find a more detailed explanation, and sample code in the following thread:

http://stackoverflow.com/a/28173969/7009923

Alternatively, you can apply some custom logic on the client (for example in the DataSource schema.parse method), but in general the better approach in terms of performance is to handle these data operations on the server.

I hope this helps.

Regards,
Dimiter Topalov
Telerik by Progress

[Joe]

Unfortunately, this does not seem to help.

Neither "DataSourceResult" nor "DataSourceResult.Data" implments IEnumerable or whatever interface someone would need to have Distinct(), GroupBy(), or Select() available.

What am I missing?

[Dimiter Topalov]

Hi Joe,

I have prepared two code snippets, based on the controller, providing the data for the Grid remote data binding demo:

public ActionResult Orders_Read([DataSourceRequest]DataSourceRequest request)
        {
            return Json(GetOrders().ToDataSourceResult(request));
        }

private static IEnumerable<OrderViewModel> GetOrders()
        {
            var northwind = new SampleEntities();

            // DistinctBy() approach: (returning items with distinct ShipCity)
            return northwind.Orders.DistinctBy(o => o.ShipCity).Select(o => new OrderViewModel
            {
                ContactName = o.Customer.ContactName,
                Freight = o.Freight,
                OrderDate = o.OrderDate,
                ShippedDate = o.ShippedDate,
                OrderID = o.OrderID,
                ShipAddress = o.ShipAddress,
                ShipCountry = o.ShipCountry,
                ShipName = o.ShipName,
                ShipCity = o.ShipCity,
                EmployeeID = o.EmployeeID,
                CustomerID = o.CustomerID
            });

            // GroupBy approach: (returning items with distinct ShipCity)
            //return northwind.Orders.GroupBy(x => x.ShipCity).Select(o => o.FirstOrDefault()).Select(o => new OrderViewModel
            //{
            //    ContactName = o.Customer.ContactName,
            //    Freight = o.Freight,
            //    OrderDate = o.OrderDate,
            //    ShippedDate = o.ShippedDate,
            //    OrderID = o.OrderID,
            //    ShipAddress = o.ShipAddress,
            //    ShipCountry = o.ShipCountry,
            //    ShipName = o.ShipName,
            //    ShipCity = o.ShipCity,
            //    EmployeeID = o.EmployeeID,
            //    CustomerID = o.CustomerID
            //});
        }

Both approaches are implemented, as described in the following thread:

http://stackoverflow.com/questions/489258/linqs-distinct-on-a-particular-property

Here is the sample code for the extensions class, containing the DistinctBy() method:

public static class Extensions
    {
        public static IEnumerable<TSource> DistinctBy<TSource, TKey>
    (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
        {
            HashSet<TKey> seenKeys = new HashSet<TKey>();
            foreach (TSource element in source)
            {
                if (seenKeys.Add(keySelector(element)))
                {
                    yield return element;
                }
            }
        }
    }

The results from the database query are first manipulated with LINQ fluent syntax expressions, and then ToDataSourceResult() is called on the returned collection.

Regards,
Dimiter Topalov
Telerik by Progress

[Joe]

As stated earlier, we must call the ToDataSourceResult first, since it has all the CarMake data in it that needs to be present in order for the Filters to be executed by the ToDataSourceResult call.

We can't do the Distinct before ToDataSourceResult, because there would be no CarMake data to filter on.

We need to know how to take the results AFTER a call to ToDataSourceResult, and then get distinct values on what it returns.

 

Here is an example of what doing it the way you suggest will not work:

We send a request to filter on "CarMake eq 'Ford'"  Below is our underlying data, with a UserId, User Name, CarMake, and CarModel:

10, Bob Smith, Ford, Mustang
10, Bob Smith, Chevrolet, Corvette
10, Bob Smith, Ford, Pinto
11, Fred Jones, Chevrolet, Corvette
11, Fred Jones, Chrysler, Avenger
12, Jane Doe, Ford, Mustang
12, Jane Doe, Ford, Escort

If we do DistinctBy "UserId, UserName" FIRST, our data becomes:

10, Bob Smith
11, Fred Jones
12, Jane Doe

As we can see, if we ask ToDataSourceResult to apply the "CarMake EQ 'Ford'", it won't work.

 

 

[Dimiter Topalov]

Hello Joe,

Thank you for the further details provided. In this scenario, you can either further manipulate the data in the controller, and return a new object, e.g.:

public ActionResult Orders_Read([DataSourceRequest]DataSourceRequest request)
{
var processedResult = GetOrders().ToDataSourceResult(request);
var processedResultData = processedResult.Data;
var distinctData = processedResultData.OfType<OrderViewModel>().DistinctBy(o => o.ShipCity);
return Json(new { Data = distinctData, Total = distinctData.Count() });
}

... or manipulate the incoming data on the client via a custom function, provided in the schema.parse configuration, e.g.:

http://dojo.telerik.com/IcUHa

Regards,
Dimiter Topalov
Telerik by Progress