CODEWITHSUNDEEP
linq-to-entities
John Miner
John Miner

Reputation: 893

LINQ-To-Entities query - what happens on the database and what happens locally?

In my WCF service's business logic, I have a function that allows to filter users by one or more columns. The way I do the filtering is like this:

public List<UserDto> GetUsersByFilters    (
                                              String ssn, 
                                              List<Guid> orderIds, 
                                              List<MaritalStatusEnum> maritalStatuses, 
                                              String name, 
                                              int age
                                          )
{
    using (MyProjEntities entities = new MyProjEntities())
    {
        IQueryable<User> users = entities.Users;

        // Filter By SSN (check if the user's ssn matches)
        if (String.IsNullOrEmusy(ssn) == false)
            users = users.Where(us => us.SSN == ssn);

        // Filter By Orders (check fi the user has all the orders in the list)
        if (orderIds != null)
            users = users.Where(us => UserContainsAllOrders(us, orderIds));

        // Filter By Marital Status (check if the user has a marital status that is in the filter list)
        if (maritalStatuses != null)
            users = users.Where(pt => maritalStatuses.Contains((MaritalStatusEnum)us.MaritalStatus));

        // Filter By Name (check if the user's name matches)
        if (String.IsNullOrEmusy(name) == false)
            users = users.Where(us => us.name == name);

        // Filter By Age (check if the user's age matches)
        if (age > 0)
            users = users.Where(us => us.Age == age);


        return users.ToList();
    }
}

private   Boolean   UserContainsAllOrders(User user, List<Guid> orderIds)
{
    return orderIds.All(orderId => user.Orders.Any(order => order.Id == orderId));
}

My question is this: What queries happens on the database and what happens locally ?

Obviously, I don't want each time that all the users in the database will be fetched to my memory...

I assume that because 'Filter By Orders' uses a local function - that cannot be done on the database, so that is done locally. Am I right ?

And if so - does that mean that everything that happens after that happens locally also, as a result ?

And what if the 'Filter By Orders' wasn't there ? would everything be done on the database side ?

Does LINQ-TO-EF know how to 'translate' the 'Marital Status' query to a database-side query ? (I am checking if the user's marital status enum exists in the list of enums passed to the function).

Thanks !

[ Edit : Sorry. I don't know why the code is not auto-colored ]

Upvotes: 1

Views: 172

Answers (1)

Rikon
Rikon

Reputation: 2706

If you'll hover over your different "where" statements, you'll notice that they are IQueryable's (or at least they should be)... So long as you keep appending Iqueryable "where's" to each other, the statement should remain an Iqueryable (and not call the database each time).

When you finally do a ToList at the bottom, that's when you'll actually call the database.

It's actually very important to watch as you build a linq to ent statement and make sure that you don't inadvertently call an extension that returns an IEnumerable earlier than you want. If you accidentally called an extension that returns IEnumerable and then started calling "skip" and "take", attempting to do sql side paging, then you'll have missed... You will have skipped and take'ed on an in-memory object rather than creating an optimized sql query.

As to exactly what's going on, it's most interesting if you open up profiler and watch the sql query that gets submitted when you call ToList (or any other IEnumerable extension). Linq to Ent will USUALLY build a pretty optimized sql statement. Adding an Iqueryable Skip and Take for instance, Linq to Ent will generate the necessary sql for RowNumber and things like that so that it can do sql server side paging efficiently. (Note that as a query grows more complex, Linq to Ent has a harder time creating the most optimized query. If a complicated query starts taking too long, it's good to trap the sql being submitted because it's often possible to write your own more optimized query in those cases).

You can also trace the sql this way too

Upvotes: 1

Related Questions