Usually we write LINQ queries as a single statement. But C# allows to assemble a query by multiple statements.
|
1 2 3 |
var query141 = ctx.Clients.AsQueryable(); query141 = query141.Where(c => c.CountryCode == "US"); query141 = query141.Where(c => c.Phones.All(p => p.CountryCode == "US")); |
This gives an excellent possibility to create a dynamic Where clause. In the most simple way just put a line with a Where assignment in if {…} block like this.
|
1 2 3 4 |
if (!String.IsNullOrEmpty(clientCountryCode)) { query141 = query141.Where(c => c.CountryCode == clientCountryCode); } |
Entity Framework would generate a SQL query at the last moment when you start to retrieve data from database. So it would generate a particular SQL code for a given set of Where conditions.
If you need to select a particular set of fields after assembling a dynamic Where clause, this can be done with a second LINQ query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
var query142a = ctx.Clients .Join(ctx.Phones, client => client.Id, phone => phone.ClientId, (client, phone) => new { client, phone } ); query142a = query142a.Where(c => c.client.CountryCode == "US"); query142a = query142a.Where(c => c.phone.CountryCode == "US"); var query142b = query142a.Select(c => new { Name = c.client.Name, Number = c.phone.Number }); |
Pay attention that I don’t use any ToList() or similar functions in query142a that would cause immediate execution. When Entity Framework retrieves data from query142b, it combines both queries and creates a single SQL query.
|
1 2 3 4 5 6 7 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent2].[Number] AS [Number] FROM [dbo].[Clients] AS [Extent1] INNER JOIN [dbo].[Phones] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ClientId] WHERE (N'US' = [Extent1].[CountryCode]) AND (N'US' = [Extent2].[CountryCode]) |