[Solved] distinct cancels orderby?

[Kevin Cabritit]

Hi,

Thank you for your help in advance!

I have the following query:

var sortedResult = from element in scope.Extent<ORM.Movieinstancecinema>()
                     where NowShowingList.Contains(element.Movieinstance)
                     && element.CinemaId == cinemaId
                     orderby element.AdjustedReleaseDate descending
                     select element.Movieinstance;

This query works fine. When I databind this to say, a grid, everything is as it should be:

GridView1.DataSource = sortedResult.Take(6);

Now, when I put in a distinct clause into the mix, like so:

GridView1.DataSource = sortedResult.Distinct().Take(6);

I lose the orderby from the original query. A comment on this article explains that if a distinct clause is used after an orderby clause, then the orderby is ignored.

Can someone please tell me how I can implement both the orderby and distinct in this situation?

Cheers,
-alex

[Petko_I]

Hi Alex Daniel,

Since I do not have the same database you use, I will give you a similar example from the Northwind database and tell you how to modify your query to achieve the same results.

using (IObjectScope scope = NorthwindOAProvider.GetNewObjectScope())
{

    var query = from order in scope.Extent<Order>()
                group order by order.ShipCountry into g
                select new { ShipCountry = g.Key,
                             LatestDate = g.Max(o => o.OrderDate)
                           };

    var orderedQuery = query.OrderByDescending(e => e.LatestDate);

    foreach (var pair in orderedQuery)
    {
        Console.WriteLine("{0} {1}",
                        pair.LatestDate.Value.ToShortDateString(),
                        pair.ShipCountry);
    }
}

One thing we should note here is that a distinct clause can be replaced with a group by clause to achieve the same results. What we will do here is group by the values we want to be distinct (element.Movieinstance in your case and ShipCountry in the example) and for each group take the latest date (element.AdjustedReleaseDate). Now we have enough information to order our help query by date in descending order and finally project only the needed values with a Select(). In this example I have omitted the last projection step for testing purposes.

I hope this example is what you are looking for. Do not hesitate to contact us for further questions.

Kind regards,
Petko_I
the Telerik team