CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entities
Slauma
Slauma

Reputation: 177153

Improving a LINQ query so that it executes completely in the database

I have an Address model (simplified)...

public class Address
{
    public int AddressId { get; set; }
    public string City { get; set; }
}

...and a DbContext derived class that contains a set of Addresses:

public DbSet<Address> Addresses { get; set; }

Then I have this query which is supposed to retrieve one or no address (_context is an instance of my database context class):

public Address GetAddress(string city, int addressId)
{
    Address address = null;

    // this is a database query
    var addresses = _context.Addresses.Where(a => a.City == city).ToList();

    // the rest queries in memory
    if (addresses.Count <= 1)
        address = addresses.FirstOrDefault();
    else
    {
        address = addresses.FirstOrDefault(a => a.AddressId == addressId);
        if (address == null)
            address = addresses.FirstOrDefault();
    }

    return address;
}

The query is a bit weird. The logic is simply:

Disturbing is that the .ToList() call potentially loads a lot of addresses into memory I'm not interested in. In the end I filter only one of the loaded addresses in memory as the final result.

Is there a way to rewrite this query (with LINQ-to-Entities) so that it runs completely in the database and returns only one or no address (with a single database roundtrip)?

Upvotes: 2

Views: 81

Answers (1)

usr
usr

Reputation: 171178

Your logic can be interpreted as preferring the address with a given ID and if none matches jut picking any at all. Your query does not enforce any order for that case.

var addressesInCity = _context.Addresses.Where(a => a.City == city);
var addrByID = addressesInCity.Where(a => a.AddressId == addressId);
var anyAddr = addressesInCity.Take(1);

You can write this with two queries:

addrByID.FirstOrDefault() ?? anyAddr.FirstOrDefault();

You can combine those into a single query:

addrByID.Select(a => new { Priority = 1, a })
.Concat(anyAddr.Select(a => new { Priority = 2, a }))
.OrderBy(x => x.Priority)
.Take(1)
.Select(x => x.a)
.FirstOrDefault();

This saves a roundtrip and SQL Server understands the sorting by a constant. It will run efficiently. Not necessarily more efficiently than the first form but not significantly worse.

Note, that the order of results returned by UNION (ALL) is undefined. We need to enforce order by introducing a Priority field.

Upvotes: 5

Related Questions