CODEWITHSUNDEEP
c#linq-to-entitiessql-order-bynavigation-properties
Ryan
Ryan

Reputation: 263

Order by a field which is a Navigation Property to an Entity - Linq to Entity

I've got a scenario where I will need to order by on a column which is a navigation property for the Users entity inside my EF model.

The entities: Users --> Countries 1:n relationship

A simple SQL query would be as follows:

SELECT UserId, u.Name, c.Name
FROM users u join countries c on u.CountryId = c.CountryId
ORDER BY c.Name asc;

So then I tried to replicate the above SQL query using Linq to Entities as follows - (Lazy Loading is enabled)

entities.users.OrderBy(field => field.country.Name).ToList();

But this query does not return my countries sorted by their name as the native SQL query above does.

However I continued a bit more and did the following:

var enumeratedUsers = entities.users.AsEnumerable();
users = enumeratedUsers.OrderBy(fields => fields.country.Name).ToList();

But ordering on the enumeratedUser object for about 50 records took approx. 7seconds

Is there a better way how to omit the Enumerable and without returning an anonymous type?

Thanks

EDIT

I just forgot to say that the EF provider is a MySQL one not a MS SQL. In fact I just tried the same query on a replicated database in MS SQL and the query works fine i.e. the country name is ordered correctly, so it looks like I have no other option apart from getting the result set from MySQL and execute the order by from the memory on the enumerable object

Upvotes: 3

Views: 9165

Answers (4)

Ryan
Ryan

Reputation: 263

SOLUTION

Since I had both columns named Name in both Countries and Users table MySQL Connector was generating this output when order by country.Name was executed:

SELECT `Extent1`.`Username`,  `Extent1`.`Name`,  `Extent1`.`Surname`, `Extent1`.`CountryId`
FROM `users` AS `Extent1` INNER JOIN `countries` AS `Extent2` ON `Extent1`.`CountryId` = `Extent2`.`CountryId`
ORDER BY `Name` ASC

therefore this will result in ordering on the users.Name rather countries.Name

However MySQL have release version 6.4.3 .NET connector which has resolved a bunch of issues one of them being:

We are also including some SQL generation improvements related to our entity framework provider. Source: http://forums.mysql.com/read.php?3,425992

Thank you for all your input. I tried to be clear as much as possible to help others which might encounter my same issue.

Upvotes: 0

Eranga
Eranga

Reputation: 32447

var enumeratedUsers = entities.users.AsEnumerable();
users = enumeratedUsers.OrderBy(fields => fields.country.Name).ToList();

This is LINQ to Objects not LINQ to Entities.

Above Order By clause will call OrderBy defined in Enumerable

That is ordering will be done in memory. Hence it will take long time

Edit

It looks like a MySQL related issue

You may try something like this.

        var users = from user in entities.users
                          join country in entities.Country on user.CountryId equals country.Id
                          orderby country.Name
                          select user;

Upvotes: 6

Jin-Wook Chung
Jin-Wook Chung

Reputation: 4344

entities.users.OrderBy(field => field.country.Name).ToList();

But this query does not return my countries sorted by their name as the native SQL query above does.

Yes, it does not return Countries but only Users sorted by the name of country. When this query is executed, the following sql is sent to DB.

SELECT u.*
FROM users u join countries c on u.CountryId = c.CountryId
ORDER BY c.Name asc;

As you can see, the result does not include any fields of countries. As you mentioned the lazy loading, countires are loaded through it when needed. At this time, countries are ordered as the order you call it through the lazy loading. You can access countries through the Local property of a entity set.

This point tells you that if you want user sorted by the name of country and also countires sorted by the name, you need the eagerly loading as @Dennis mentioned like:

entities.users.Include["country"].OrderBy(field => field.country.Name).ToList();

This is converted to the following sql.

SELECT u.*, c.*
FROM users u join countries c on u.CountryId = c.CountryId
ORDER BY c.Name asc;

Upvotes: 3

Dennis Traub
Dennis Traub

Reputation: 51654

Have you tried using Include?

entities.users.Include["country"].OrderBy(field => field.country.Name).ToList();

Upvotes: 0

Related Questions