Querying Entity Framework. Part 22 – Multiple Fields In Join, GroupBy And OrderBy – Andrey Zavadskiy

Now I want to summarize the info about how to use multiple fields in LINQ queries. You know that LINQ to Entities allows to make joins, grouping and sorting based on single field or multiple fields.

Generally speaking, when the query operation is based on a single field, you use this single field alone. When the operation is based on multiple fields, you would use a construct as the following:
new { field1, field2... }

Here are examples of how to do it in C#.

Join

var query41 = ctx.Clients
    .Join(ctx.Phones,
        client => new { Id = client.Id, client.CountryCode },
        phone => new { Id = phone.ClientId, phone.CountryCode },
        (client, phone) => new { client.Name, phone.Number, client.CountryCode }
    );

var query41 = ctx.Clients

.Join(ctx.Phones,

client => new { Id = client.Id, client.CountryCode },

phone => new { Id = phone.ClientId, phone.CountryCode },

(client, phone) => new { client.Name, phone.Number, client.CountryCode }

);

There are identical key selectors in inner and outer parts of the join.

GroupBy

var query221 = ctx.Clients
    .GroupBy(c => new
    {
        FirstLetter = c.Name.Substring(0, 1),
        CountryCode = c.CountryCode
    });

var query221 = ctx.Clients

.GroupBy(c => new

{

FirstLetter = c.Name.Substring(0, 1),

CountryCode = c.CountryCode

});

OrderBy

var query222 = ctx.Clients
    .OrderBy(c => new { c.CountryCode, c.Name });

1 2	var query222 = ctx.Clients .OrderBy(c => new { c.CountryCode, c.Name });

In this case EF will sort the rows by both fields in ascending order. The last row in T-SQL will look like this:
ORDER BY [Extent1].[CountryCode] ASC, [Extent1].[Name] ASC

If you use OrderByDescending() method, so the output will be sorted by both fields, but in descending order.
In a situation when you need different sorting, you should use ThenBy()/ThenByDescending() for the second and consequent fields.

Leave a Reply Cancel reply