Database Reference
In-Depth Information
Console.WriteLine("More complex SQL...");
var query2 = from reservation in context.Reservations
let dateMatches = searchDate == null || reservation.ResDate == searchDate
let nameMatches = searchName == string.Empty || reservation.Name.Contains(searchName)
where dateMatches && nameMatches
select reservation;
foreach (var reservation in query2)
{
Console.WriteLine("Found reservation for {0} on {1}", reservation.Name,
reservation.ResDate.ToShortDateString());
}
Console.WriteLine("Cleaner SQL...");
var query1 = from reservation in context.Reservations
where (searchDate == null || reservation.ResDate == searchDate)
&&
(searchName == string.Empty || reservation.Name.Contains(searchName))
select reservation;
foreach (var reservation in query1)
{
Console.WriteLine("Found reservation for {0} on {1}", reservation.Name,
reservation.ResDate.ToShortDateString());
}
}
Following is the output of the code in Listing 13-13:
More complex SQL...
Found reservation for James Jordan on 4/18/2010
Found reservation for James Jordan on 5/12/2010
Found reservation for James Jordan on 6/22/2010
Cleaner SQL...
Found reservation for James Jordan on 4/18/2010
Found reservation for James Jordan on 5/12/2010
Found reservation for James Jordan on 6/22/2010
How It Works
Writing conditions inline, as we did in the
second
query in Listing 13-13, is not very good for readability or
maintainability. Typically, we would use the
let
keyword to make the code cleaner and more readable. In some cases,
however, this leads to more complex and often less efficient SQL code.
Let's take a look at the SQL generated by both approaches. Listing 13-14 shows the SQL generated for the
first
query. Notice that the
where
clause contains a
case
statement with quite a bit of
cast
'ing going on. If we had more
parameters in our search query beyond just name and reservation date, the resulting SQL statement would get
even more complicated.
