Entity Framework Lazy Loading With contraint

Question

I would like to know if there is a way to include a constraint with lazy loading. My object that would be loaded has a property 'isDeleted'. I would only like to load results that are not deleted. So, for example.

allCustomers = db.customers.include("documents").toList

I would like this to return all customers and only any documents where isDeleted = false. I know I can qualify this on the return set, but I'd rather not even get them back from the database. Any way to do it?

Answer 1

Better not use Include(string) if you only want to query values

Apparently there is a one-to-many relation between Customers and his Documents: every Customer has zero or more Documents, every Document belongs to exactly one Customer.

(Alternatively it could be a many-to-many relationship, but the principle remains the same.)

So if you've designed your classes according to the entity framework code first conventions, you would have had something similar to:

class Customer
{
    public int Id {get; set;}

    // every Customer has zero or more Documents:
    public virtual ICollection<Document> Documents {get; set;}

    ... // other properties
}
class Document
{
    public int Id {get; set;}

    // every Document belongs to exactly one Customer using foreign key:
    public int CustomerId {get; set;}
    public virtual Customer Customer {get; set;}

    ... // other properties
}

This is all that entity framework needs to know to detect that you designed a one-to-many relation between Customers and their Documents. It might be that your class names or your properties have different identifiers. You'll probably have solved this using attributes or fluent API, but the principle remains the same.

Use include only if you plan to update the fetched items

One of the slower parts of your database queries is the transport of the data from the DBMS to your process. Hence it is wise to limit the data to only the data you really plan to use.

If you ask for non-deleted Customers with all their documents you are transferring way more data than needed:

• Every Customer will have a field IsDeleted. You already know these values will be false
• Every Customer will have a primary key Id. Every of his 100 Documents will have a foreign key CustomerId with the same value as Customer.Id. What a waste to query them!

Therefore, unless you plan to update the queried values it is always wiser to use Select instead of Include. That way you can adapt the query to fetch only the data you actually plan to use in your use case.

back to your question

You want to get all non-deleted Customers with (all or some of) their non-deleted Documents.

var result = myDbContext.Customers
    // I only want the non-deleted Customers:
    .Where(customer => !customer.IsDeleted)
    .Select(customer => new
    {
        // select only the properties you plan to use:
        Id = customer.Id,
        Name = customer.Name,
        ...

        Documents = customer.Documents
            // I only want the non-deleted Documents:
            .Where(document => !document.IsDeleted) 
            .Select(document => new
            {
                // again select only the properties you plan to use:
                Title = document.Title,
                Date = document.PublicationDate,
                Version = document.Version,
                ...

                // not needed:
                // CustomerId = document.CustomerId,
            })
            .ToList(),
    });

Because entity framework knows about your one-to-many relationship, it knows the primary and foreign keys and will perform the proper Join for you.

Only if you really need to update all Documents of all non-deleted customers it is wise to use Include. My advise would be not to use the version that takes a string parameter, but the one that takes a property expression. See IQueryable Include. That version is type safe. Your compiler will complain if you use non-existing properties:

var itemsToUpdate = myDbContext.Customers
    .Include(customer.Documents)
    .Where(customer => !customer.IsDeleted);

But IMHO it is seldom that you want to update all Documents of all Customers at once. Usually you'll update one Document or one Customer.

Therefore you'll usually need Include if you need to update a one-to-one relation: a Customer with his one and only HomeAddress

var customerToUpdate = myDbContext.Customers
   .Include(customer => customer.HomeAddress)
   .Where(customer => customer.Id == customerId)
   .SingleOrDefault();

customer.HomeAddress.Street = "Downing street",
customer.HomeAddress.Number = 10,
customer.HomeAddress.City = "London",
myDbContext.SaveChanges();